2

Fixed-width text files in data management (F&O)

 1 year ago
source link: https://dev.goshoom.net/2022/09/fixed-width-text-files-in-data-management-fo/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Fixed-width text files in data management (F&O)

Data management in Dynamics 365 for Finance and Operations supports several types of files for import and export. I usually see XML, Excel and CSV used, but sometimes we also need to deal with a text format with fixed-width columns. It means that there is no column separator as in CSV – we know that first ten characters contains an ID, next eight characters contains a quantity or something like that.

Data management supports this scenario too, which not everyone is aware of.

To try it, first set up a source format. Go to Data management workspace, click Configure data source tile and create a new format. Set File format = Fixed width and possibly other parameters as needed. I’ll use First row header = No and Row delimiter = {LF}. It doesn’t seem to work very well with {CR}{LF}.

image-4.png

Then create an export project and add an entity to be exported with our new source format. I used Customer groups entity (because it’s relatively simple and it doesn’t contain too many records).

Still in the project, notice Entity attribute button. It’s very important when talking about fixed-width files.

image-1024x131.png

If you press it, you’ll see a list of columns that will be in the file. There is a sequence of columns, their length etc., and you can modify it as needed.

image-1.png

If you want to import the file, create an import project and add an entity with the fixed-width source format. It’ll complain about missing mapping, unless the columns have headers and data management is able to map them to fields.

image-5.png

In this case, we’ll configure it manually. As we already know, the Entity attribute button will take us into the setup of file columns. Here we can add the columns, their type, lenght and so on.

image-6.png

Then we can use these columns in source mapping in the usual way.

image-7.png

By the way, when I needed to deal with fixed-width text files in C# (namely Azure Functions), I used FixedWidthParserWriter, which did the job perfectly.

Filed under Dynamics Community Syndication | Tagged D365FO, data import, data management, Dynamics 365 | Comment | Permalink

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Comment *

Name

Email

Website

Save my name, email, and website in this browser for the next time I comment.

Post navigation


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK