I learned quite a lot during my 15 years working with Navision. I am going to share some of my precious knowledge here.
I’ll show how can we import a csv file into any NAV table, without any libraries or custom code units.
Importing CSV
Obvious part, which most of my code objects starts with:
Integer - OnPreDataItem()
Integer.SETRANGE(Number,1,1);
readCSV()
mFilePath := getMainPath('downloaded')+'RA_Hertz-DE.csv';
//invalid->ready
//downloaded->ready
lreFileRec.SETRANGE(Path, getMainPath(''));
lreFileRec.SETRANGE("Is a file", TRUE);
lreFileRec.SETFILTER(Name, '*.csv');
IF lreFileRec.FIND('-') THEN REPEAT
lreNewCSVfile.TEXTMODE := FALSE;
lreNewCSVfile.WRITEMODE(TRUE);
IF NOT lreNewCSVfile.OPEN(getMainPath('ready') + '\' + lreFileRec.Name) THEN
IF NOT lreNewCSVfile.CREATE(getMainPath('ready') + '\' + lreFileRec.Name) THEN
ERROR('nejde vytvorit soubor');
lreCSVfile.TEXTMODE := FALSE;
lreCSVfile.OPEN(lreFileRec.Path + '\' + lreFileRec.Name);
lreCSVfile.SEEK(0);
REPEAT
lreCSVfile.READ(mChar);
IF mChar='"' THEN mChar := 0;
IF mChar <> 0 THEN lreNewCSVfile.WRITE(mChar);
UNTIL lreCSVfile.POS >= (lreCSVfile.LEN);
lreCSVfile.CLOSE();
lreNewCSVfile.CLOSE();
UNTIL (lreFileRec.NEXT() =0);
cacheCols();
cacheFields();
//import ready
lreFileRec.SETRANGE(Path, getMainPath('ready'));
lreFileRec.SETRANGE("Is a file", TRUE);
lreFileRec.SETFILTER(Name, '*.csv');
IF lreFileRec.FIND('-') THEN REPEAT
x:=1;
mImportFile.OPEN(lreFileRec.Path + '\' + lreFileRec.Name);
mImportFile.CREATEINSTREAM(mImportStream);
WHILE NOT mImportStream.EOS DO BEGIN
mImportStream.READ(mChar);
//nova radka
IF mChar=13 THEN BEGIN
processCSV(x);
linCnt += 1;
x:=1;
CLEAR(gteVals);
END;
IF mChar=',' THEN x+=1;
IF (mChar<>10) AND (mChar<>13) AND (mChar<>',') THEN BEGIN
gteVals[x]:=gteVals[x] + FORMAT(mChar);
END;
END;
mImportFile.CLOSE;
UNTIL lreFileRec.NEXT()=0;
//ready->imported
//ready->backup
{
processing a CSV record. gteNAVfield/getNAVtab are arrays telling 1) which CSV fields we want to import and 2) to which fields/tables in NAV
}
processCSV(cnt : Integer)
greNS.INSERT(); // create new record
FOR n:=1 TO cnt DO BEGIN
//find column name
IF (gteNAVfield[n] <> '') AND (gteNAVfield[n] <> '?') THEN BEGIN
lteColName:=gteNAVfield[n];
lteTabName:=gteNAVtab[n];
linFieldID:=getFieldID(lteTabName,lteColName);
setField(lteTabName,linFieldID,gteVals[n]);
END;
END;
greRecRef.SETTABLE(greNS); //save rec ref into the table
greNS.MODIFY();
{
gets field id of a table by a field name
gteNSfieldName is cached array of field names
ginNSfieldID is cached array of field ids
}
getFieldID(tabName : Text[300];colName : Text[300]) fieldId : Integer
IF tabName = 'Some Table' THEN BEGIN
FOR n:=1 TO ginRecFieldsCnt DO BEGIN
IF gteNSfieldName[n] = colName THEN BEGIN
fieldId:=ginNSfieldID[n]; //field id of NAV table
EXIT;
END;
END;
END;
{
greRecRef is variable of type RecordRef we point to greNS which is variable of type Record of chosen record
greFieldRef is variable of type FieldRef which we use to reference to this record fields
}
//sets a field of a table with a value
setField(tabName : Text[30];fieldId : Integer;mVal : Text[1024])
IF fieldId < 1 THEN EXIT;
IF tabName = 'Some Table' THEN BEGIN
greRecRef.GETTABLE(greNS); // get table reference
greFieldRef:=greRecRef.FIELD(fieldId);
greFieldRef.VALUE:=mVal;
END;
{
after all fields are set, we call settable and modify to save loaded fields into Navision record
greRecRef.SETTABLE(greNS); //save rec ref into the table
greNS.MODIFY();
}
for more details about FieldRef and extensions for other FieldTypes, refer to https://cyltr.com/dynamics-nav-fieldref/
