As promised it is time to pick more things that I have improved for NAV 2016. Still on the Data Exchange, and looking at file import. We can import a text file; fixed and delimited, with different encoding.
By using Codeunit 1240 for the External Data Handling action we are able to get any text file into the Data Exchange framework. It will simply prompt the user for a local file name.
Microsoft shipped Codeunit 1241 to read a flat file. I decided to change that a bit with two enhancements. First, I wanted to be able to read a delimited file and second, I wanted to be able to use different encoding.
The Codeunit name is “Fixed File Import” and just the name change show the difference. My version is named “Fixed/Delimited File Import”.
There are settings in the Data Exchange that I wanted to be able to support.
The standard Codeunit only reads the MSDOS File Encoding with this code
OnRun(VAR Rec : Record "Data Exch.") "File Content".CREATEINSTREAM(ReadStream); DataExchDef.GET("Data Exch. Def Code"); LineNo := 1; REPEAT ReadLen := ReadStream.READTEXT(ReadText); IF ReadLen > 0 THEN ParseLine(ReadText,Rec,LineNo,SkippedLineNo); UNTIL ReadLen = 0;
Changed
WITH DataExchDef DO BEGIN GET("Data Exch. Def Code"); CASE "File Encoding" OF "File Encoding"::"MS-DOS" : "File Content".CREATEINSTREAM(ReadStream,TEXTENCODING::MSDos); "File Encoding"::WINDOWS : "File Content".CREATEINSTREAM(ReadStream,TEXTENCODING::Windows); "File Encoding"::"UTF-8" : "File Content".CREATEINSTREAM(ReadStream,TEXTENCODING::UTF8); "File Encoding"::"UTF-16" : "File Content".CREATEINSTREAM(ReadStream,TEXTENCODING::UTF16); END; LineNo := 1; REPEAT ReadLen := ReadStream.READTEXT(ReadText); IF ReadLen > 0 THEN ParseLine(ReadText,Rec,LineNo,SkippedLineNo); UNTIL ReadLen = 0; END;
to read the four different text encoding that are supported by the Data Exchange Setup.
The PharseLine section in the standard Codeunit has this simple loop to read the fixed file.
StartPosition := 1; REPEAT DataExchField.InsertRecXMLField(DataExch."Entry No.",LineNo,DataExchColumnDef."Column No.",'', COPYSTR(Line,StartPosition,DataExchColumnDef.Length),DataExchLineDef.Code); StartPosition += DataExchColumnDef.Length; UNTIL DataExchColumnDef.NEXT = 0; LineNo += 1;
Extending this and adding two functions gives me the ability to import both fixed and variable text file.
StartPosition := 1; REPEAT CASE DataExchDef."File Type" OF DataExchDef."File Type"::"Fixed Text" : BEGIN DataExchField.InsertRecXMLField(DataExch."Entry No.",LineNo,DataExchColumnDef."Column No.",'', COPYSTR(Line,StartPosition,DataExchColumnDef.Length),DataExchLineDef.Code); StartPosition += DataExchColumnDef.Length; END; DataExchDef."File Type"::"Variable Text" : BEGIN DataExchField.InsertRecXMLField(DataExch."Entry No.",LineNo,DataExchColumnDef."Column No.",'', ExtractFirstDelimitedPart(Line,GetSeparator),DataExchLineDef.Code); END; ELSE ERROR(FileTypeNotSupported,DataExchDef."File Type"); END; UNTIL DataExchColumnDef.NEXT = 0; LineNo += 1; LOCAL GetSeparator() Separator : Text[1] WITH DataExchDef DO CASE "Column Separator" OF "Column Separator"::Tab : Separator[1] := 9; "Column Separator"::Semicolon : Separator := ';'; "Column Separator"::Comma : Separator := ','; "Column Separator"::Space : Separator := ' '; END; LOCAL ExtractFirstDelimitedPart(VAR Line : Text;Separator : Text[1]) FirstPart : Text SeparatorPosition := STRPOS(Line,Separator); IF SeparatorPosition > 0 THEN BEGIN FirstPart := COPYSTR(Line,1,SeparatorPosition - 1); IF SeparatorPosition + 1 <= STRLEN(Line) THEN Line := COPYSTR(Line,SeparatorPosition + 1) ELSE Line := ''; END ELSE BEGIN FirstPart := Line; Line := ''; END;
More to come. Stay tuned…
Have a look at the XMLport 1220 (Data Exch. Import – CSV) – it supports variable text length and all the different DataExchange Encodings
Hi Gunnar
I suppose this framework is not made with EDIFACT in mind, if my partners require that, the best bet is to still buy Anveo?
Miklos, I have mapped EDIFACT as an XML file via the Data Exchange.
Hi Gunnar, Can we get a sample of EDIFACT mapping with DataExchange Framework please
I will be blogging about this in this month. You can also contact me via http://navision.guru
I am wanting to use data exchange definitions for certain integrations of Sales Orders. There is a Generic Import type (used by PEPPOL invoice for example) but no Generic Export type. Should data exchange definition be able to export other things apart from Bank Statement and if so why is there no type available.
Any way to handle dimensions in the import.
I need to import into the General Journal, and i have several shortcut dimensions that i have to import.
Hi Henrik
I would try to map to a buffer table and use the intermediate feature. Then in the mapping I would create the dimension set and use that.
Thank you very much!This is only blog I could found about Data Exchange Definitions…Any suggestions how to handle .txt import with more than one line?one header and two detail?
You need to have a way to identify a header and an detail line. Often the first part of the string is the identifier. You then create two line definition and specify the identifier string. Please go to http://navision.guru for more help.
Hi Gunnar, Is there any function to ”skip line” when importing if the value of a colum = certain value? I want to Skip all transaction with status = Declined for example.
Thank you
You should be able to map the different table and skip the mapping codeunit-> do nothing
Hi Gunnar,
is it possible to convert data via the Data Exchange Framework?
For example an export from another financial system, which uses different G/L accounts into NAV. A conversion table could map the G/L accounts from the first system with the NAV G/L account.
Hi Marc.
That would be done using a pre- or post-mapping codeunit. Pre-mapping to change the data in data exchange before creating the result data. Post-mapping to update the result data after import.
Ok Thank you!
This improvement should really be in the base application by now…
Any chance of suggesting this to NAV Team to pickup?
Some of these improvements are, not all.