Importing Files with Navision Application Server

CtrlShiftF11

I am working on a project developing automated text file importing using the Navision Application Server. Because you cannot use dataports with the NAS, I have written codeunits to handle the task of importing, parsing and creating records with my delimited text files.

Right now I'm trying to use a global variable with a data type of Variant to read the file data into...

REPEAT
  CLEAR(ImportBuffer);
  CatalogImportFile.READ(ImportBuffer);
  
  //Call file handling functions to work with the ImportBuffer global here
  //Insert Records here, etc.

UNTIL CatalogImportFile.POS = ImportFileLen;

Is there a better way to do this? I'm sure that someone else has tried to use the NAS to automate regular imports.

Thanks in advance!
CtrlShiftF11 ](*,)

Marije_Brummel

I always use this, pretty much the same code

File1.TEXTMODE := TRUE;

File1.OPEN('C:\Temp\Textfile);

Counter := 1;
WHILE File1.READ(Value) <> 0 DO
BEGIN
  Counter += 1;

  Table.field1 := Copystr(value,1,10);
  Table.field2 := Copystr(value,20,20);
  Table.insert;
 
END;

File1.CLOSE;

This works fine for me and is a lot faster than a dataport when importing large amounts of data

CtrlShiftF11

Well that's fine and all but I don't need help with the parsing and handling of the data (if you recall my data is delimited so COPYSTR with hard coded integer values wouldn't cut it for my data). You have to loop through the string and search for declared constants that serve as Text Qualifiers and Column Delimiters - which I've coded functions for.

I should have been more specific, my real question is...
In TEXTMODE should I be reading each line of the file data into a data type of VARIANT?

[-X

Thanks,
CtrlShiftF11

Marije_Brummel

Well, you should have been more specific then.

No, you can just use a text variable, just make it a big one.

What is the delimiter you are using?

CtrlShiftF11

I fixed it and was able to use the VARIANT data type, which gives me more than 250 characters. I originally tried to use the new BIGTEXT data type but I ran into issues. BLOBs usually don't play nice so no big shock there.

Anyway, I've about got this perfected so I'll post the source code when I'm done testing and refining in case some other poor fool runs into this very issue.
\:D/

Marije_Brummel

Congratulations.

CtrlShiftF11

The following is the code that I've used to import data into Navision via a Codeunit...

OnRun()
ImportFilename := 'C:\Temp\NAVItemImport_Catalog.txt';
ColDelimiter := '|';
TextQualifier := '^';

CatalogImportFile.TEXTMODE(TRUE);
CatalogImportFile.OPEN(ImportFilename);
ImportFileLen := CatalogImportFile.LEN;

REPEAT
  CLEAR(ImportBuffer);
  CatalogImportFile.READ(ImportBuffer);
  
  FirstChar := COPYSTR(ImportBuffer,1,1);
  IF FirstChar = TextQualifier THEN
    FirstParsingPoint := 2
  ELSE
    FirstParsingPoint := 1;

  ItemNo := ParseFileRecForColVal(FirstParsingPoint);
  IF NOT Item.GET(ItemNo) THEN BEGIN
    ItemDesc := ParseFileRecForColVal(FindNextParsePoint());
    UPC := ParseFileRecForColVal(FindNextParsePoint());
    TypeCode := ParseFileRecForColVal(FindNextParsePoint());

    WITH Item DO BEGIN
      INIT;
      "No." := ItemNo;
      VALIDATE(Description,COPYSTR(ItemDesc,1,30));
      "Long Description" := ItemDesc;
      VALIDATE("Item Category Code",'FG');
      "Last Date Modified" := TODAY;
      AppFrmCommonLib.CreateDefaultItemUOM(ItemNo,'PIECES');
      VALIDATE("Base Unit of Measure",'PIECES');
      INSERT(TRUE);
    END;
  END;
  ClearImportVars();
UNTIL CatalogImportFile.POS = ImportFileLen;

CatalogImportFile.CLOSE;

ParseFileRecForColVal(StartingPoint : Integer) ColumnVal : Text[250]
CharCounterL := StartingPoint;
WHILE (CurrentCharL <> TextQualifier) AND (CurrentCharL <> ColDelimiter) DO BEGIN
  CurrentCharL := COPYSTR(ImportBuffer,CharCounterL,1);
  CharCounterL += 1;
END;
IF CurrentCharL = TextQualifier THEN
  CopyLenL := CharCounterL - (StartingPoint + 1)
ELSE
  CopyLenL := CharCounterL - 1;
ColumnVal := COPYSTR(ImportBuffer,StartingPoint,CopyLenL);
LastParsingPoint := CharCounterL;
EXIT(ColumnVal);

FindNextParsePoint() NextParsePoint : Integer
CharCounterL := LastParsingPoint;
CurrentCharL := COPYSTR(ImportBuffer,CharCounterL,1);
WHILE (CurrentCharL = TextQualifier) OR (CurrentCharL = ColDelimiter) DO BEGIN
  CurrentCharL := COPYSTR(ImportBuffer,CharCounterL,1);
  CharCounterL += 1;
END;
NextParsePoint := CharCounterL - 1;
EXIT(NextParsePoint);

ClearImportVars()
CLEAR(ImportBuffer);
CLEAR(ItemNo);
CLEAR(ItemDesc);
CLEAR(UPC);
CLEAR(TypeCode);
CLEAR(LastParsingPoint);
CLEAR(FirstParsingPoint);

This is kind of rough but you get the idea. I'll post any enhancements once I get finished completely testing and refining this solution. :-k

timgrant

Hi,

I'm having the same problem regarding dataports and NAS. I am looking at this and I don't understand what is going on with:
ColDelimiter := '|';
TextQualifier := '^';

Is this not supposed to be a tab delimited file (which I'm using too), why do you use the above characters? Do you not use e.g. Char := 9; for the Tab or something? Could anyone shed some more light on this?

Many Thanks

dynamicslancer

May this blog will help : dynamicslancer.blogspot.in/p/blog-page.html