Tag: Data Import

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…