Do you use the Data Exchange Framework inside Dynmaics 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.
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, or?
Our code example on Github will extend the Data Exch Def Card (1210, Card) by an action "Import File". So you can jump into any Data Exchange Definition and select your file to import it directly into BC.
Take a look at this showcase by importing customers:
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:
Get the code from here: