Easiest way to Import csv file without dataport?

Dynamics 365 Business Central/NAV Developers Forum
1

Hi, Can anyone offer any suggestions on the easiest way to import a csv file without using a dataport. Oh and bye the way my record lengths are going to be greater that 1024! So I believe file.read(instring) is a problem. The reason for not using a dataport is that the process is going to be kicked off by NAS. Thanks. Jon

2

Use a report or a codeunit to import the file with Excel Automation (I don’t know if this works in NAS).

3

Thanks Heinz, I came to same conclusion. I think automation in code unit will work… i have made a start… Vars: MyADOConnection Automation ‘Microsoft ActiveX Data Objects 2.7 Library’.Connection MyADORs Automation ‘Microsoft ActiveX Data Objects 2.7 Library’.Recordset OnRun() CREATE(MyADOConnection); MyADOConnection.Open(‘Data Source=My Data’); CREATE(MyADORs); MyADORs.Open(‘SELECT * FROM CU0001.csv’, MyADOConnection); MyADORs.MoveFirst; IF NOT MyADORs.EOF THEN REPEAT BEGIN MESSAGE(’%1’, MyADORs.Fields.Item(‘F1’).Value); MESSAGE(’%1’, MyADORs.Fields.Item(‘F2’).Value); MESSAGE(’%1’, MyADORs.Fields.Item(‘F3’).Value); MESSAGE(’%1’, MyADORs.Fields.Item(‘F4’).Value); MESSAGE(’%1’, MyADORs.Fields.Item(‘F5’).Value); MESSAGE(’%1’, MyADORs.Fields.Item(‘F6’).Value); MESSAGE(’%1’, MyADORs.Fields.Item(‘F7’).Value); MESSAGE(’%1’, MyADORs.Fields.Item(‘F8’).Value); MESSAGE(’%1’, MyADORs.Fields.Item(‘F9’).Value); MESSAGE(’%1’, MyADORs.Fields.Item(‘F10’).Value); MESSAGE(’%1’, MyADORs.Fields.Item(‘F11’).Value); END; MyADORs.MoveNext; UNTIL MyADORs.EOF; MyADORs.Close; MyADOConnection.Close; CLEAR(MyADOConnection); CLEAR(MyADORs); The problem I now have is that if one of the columns is blank i.e. ,"","" I get error - “The datatype is not supported by C/SIDE…” I guess it does not like NULL value, do you know how to check for this? e.g. IF MyADORs.Fields.Item(‘F11’).Value=NULL THEN MEssage(‘Stop’);

4

Jon, can’t you just replace the empty strings in the CSV by something unique (i.e. §$§$§) and handle the re-conversion within Navision?

5

Thanks Joerg, Think I will have to persue that option! Regards, Jon

6

i had a similar problem in Navision 2.6: reading a textfile with lines with more than 250 Chars. The following Code reads a Textline Char by Char and filling this Chars in 2 Textvar`s If you add some code for checking for the field-delimiters and filling the chars in some variables for each field i think this could solve your problem. i := 0; iText1 := ‘’; iText2 := ‘’; REPEAT i += 1; ChrOk := LFile.READ(Chr); z := Chr; IF (i < 500) AND (z <> 10) AND (z <> 13) THEN BEGIN IF (i <= 250) THEN iText1 += FORMAT(Chr) ELSE iText2 += FORMAT(Chr); END; UNTIL (z = 10) OR (ChrOk = 0);

7

The problem with the empty Values can be solved … it isn’t nice but it works. I also tried the nice way using the Variant DataType for the result of Field.ActualSize. But the “Empty Value”-Fields give back an abnormal character which occurs an Error in Navision if you want to use the Variants-Content. So, this is my sample which also works with “empty” Fields in CSV-Files: Name DataType Subtype Length lau_ADOConnection Automation 'Microsoft ActiveX Data Objects 2.7 Library'.Connection lau_ADORecordset Automation 'Microsoft ActiveX Data Objects 2.7 Library'.Recordset lau_ADOFields Automation 'Microsoft ActiveX Data Objects 2.7 Library'.Fields lau_ADOField Automation 'Microsoft ActiveX Data Objects 2.7 Library'.Field CREATE(lau_ADOConnection); ltx_ConnectionStr := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + gcu_AddCtrlFunctions.GetTempPath() + ';Extended Properties="text;HDR=No;FMT=Delimited;"'; lau_ADOConnection.Open(ltx_ConnectionStr); CREATE(lau_ADORecordset); lau_ADORecordset.Open('SELECT * FROM ' + ltx_TempFileName, ltx_ConnectionStr); lau_ADORecordset.MoveFirst; IF NOT lau_ADORecordset.EOF THEN BEGIN lau_ADOFields := lau_ADORecordset.Fields; REPEAT FOR lin_Counter := 0 TO lau_ADOFields.Count-1 DO BEGIN lau_ADOField := lau_ADORecordset.Fields.Item(lin_Counter); ltx_Dummy := FORMAT(lau_ADOField.ActualSize); IF EVALUATE(lin_FieldSize, ltx_Dummy) THEN IF lin_FieldSize > 0 THEN MESSAGE('Feld: %1\Value: %2\Size: %3', lin_Counter, FORMAT(lau_ADORecordset.Fields.Item(lin_Counter).Value), lin_FieldSize); END; lau_ADORecordset.MoveNext UNTIL lau_ADORecordset.EOF; END; lau_ADORecordset.Close; lau_ADOConnection.Close; CLEAR(lau_ADOFields); CLEAR(lau_ADORecordset); CLEAR(lau_ADOConnection);