How-To Import variable CSV/TXT Files with the Data Exchange Framework into any Table

Do you use the Data Exchange Framework inside Dynamics 365 Business Central?

No? Get the documentation here:

You can use the Data Exchange Framework to manage the exchange of business documents, bank files, currency exchange rates, and any other data files with your business partners.

👉 Microsoft Docs – Data Exchange Framework

In our case we would like to import German post codes directly into the Post Code table (225) in Business Central.

So that framework would fit our needs, right?

Our code example on GitHub will extend the Data Exch Def Card (1210) by an action "Import File". So you can jump into any Data Exchange Definition and select your file to import it directly into BC.

👉 See the CSV Importer code

📦 Import Showcase: Customers

Import Preview

Import in Action

It can be also imported by using "Configuration Packages" but I am frustrated of using excel files.

So in our examlple we are importing nearly 17.000 post codes and this took 2 mins and 45 seconds on my local sandbox. The big advantages is that you can leave the "Columns Definitions" as it is and I don't need to transform the original csv file to excel (and maybe some extra work on throwing columns out).

This is how it looks like:

We are using one regular expression to split each line into columns:

(?:^|%1)(?=\[^"\]|(")?)"?((?(1)\[^"\]\*|\[^%1"\]\*))"?(?=%1|$)

where %1 can hold any column separator you want. Like a comma, pipe or semicolon. Also field delimiters are handled here.

We've prepared one simple snippet on regex101.com to play around with. Give it a try ;-)

The other way around would be to use Configuration Packages:

Config Package

Get the code from here:

https://github.com/byndit/BeyondAL/tree/main/BeyondAL/src/CSVimporter