In a previous answer a reference was made to “ADO CSV Components”. Is this a component I should be able to find under the Navision “Automation” Data type? If so, can anyone tell me which item I should select in the “Automation Server List”?
Is it a tool for creating CSV files? If so, it sounds like it could have some uses. Has anyone used it?
I’m hoping it is such a tool, hence the request for more information. Currently we use Dataports, which are an excellent tool for simple CSV exports and Codeunits for the more complex exports. If there is a CSV automation object I was going to review our existing code to see if it could be improved.
John, can you link to the “previous reply”
ADO means ActiveX Data Object, and I think you can connect to any data source, including a CSV file, as long as you get the connection string and such right. I’d be interested to see an example though, because dataports are also very well suited for this purpose.
hi, please take a look here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp. it is a document talking about how to use ADO to read data from a text file with scripts. best regards feri
The link to the previous topic is: - http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=16685 ADO CSV is mentioned by Alexander Broz in his reponse to the above topic.
In Germany their exists a proverb: If you talk about the Devil there he is [}:)] I practiced with the Automation Components for reading a CSV File. They can be found as “Microsoft ActiveX Data Objects 2.7 Library” in the Automation Server List. Here is also a piece of Code. It is very special to my problem but I think it will help. Very interesting was the creation of a “schema.ini” file before reading. There isn’t any other way to change the “;” as Delimiter using the Connection String or something else. (Forget the Delimiter property in the C-String … I always hoped that it will work [:D] ) Main function Global ADO Variables: Name DataType Subtype Length gau_CurrField Automation ‘Microsoft ActiveX Data Objects 2.7 Library’.Field Local ADO Variables: 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 grc_EDISetup.GET(); IF EXISTS(ltx_DirName + '\schema.ini') THEN ERASE(ltx_DirName + '\schema.ini'); // Search inside the directory lrc_File.SETRANGE(Path, ltx_DirName); lrc_File.SETRANGE("Is a file", TRUE); IF lrc_File.FIND('-') THEN REPEAT IF CREATEDATETIME(lrc_File.Date, lrc_File.Time) + grc_EDISetup."Min File age" < CURRENTDATETIME THEN BEGIN ltx_FileName := lrc_File.Name; ltx_QlfFileName := ltx_DirName + '\' + lrc_File.Name; lin_RunningDocNo := 0; // Create Schema File lfi_SchemaFile.TEXTMODE(TRUE); lfi_SchemaFile.CREATE(ltx_DirName + '\schema.ini'); lfi_SchemaFile.WRITE('[' + ltx_FileName + ']'); lfi_SchemaFile.WRITE('ColNameHeader=FALSE'); lfi_SchemaFile.WRITE('Format=Delimited(|)'); lfi_SchemaFile.WRITE('CharacterSet=OEM'); lfi_SchemaFile.CLOSE; CREATE(lau_ADOConnection); ltx_ConnectionStr := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + ltx_DirName + ';Extended Properties="text;HDR=No;FMT=Delimited|"'; lau_ADOConnection.Open(ltx_ConnectionStr); CREATE(lau_ADORecordset); lau_ADORecordset.Open('SELECT * FROM ' + ltx_FileName, ltx_ConnectionStr ); lau_ADORecordset.MoveFirst; IF NOT lau_ADORecordset.EOF THEN BEGIN lau_ADOFields := lau_ADORecordset.Fields; REPEAT // Customer No. lau_ADOField := lau_ADORecordset.Fields.Item(0); IF IsValidField(lau_ADOField) THEN lco_CurrCustomerNo := CurrFieldValue(); // Ext. Order No. lau_ADOField := lau_ADORecordset.Fields.Item(1); IF IsValidField(lau_ADOField) THEN [... Deleted for Demo Reasons ...] lau_ADORecordset.MoveNext UNTIL lau_ADORecordset.EOF; END; lau_ADORecordset.Close; lau_ADOConnection.Close; CLEAR(lau_ADOFields); CLEAR(lau_ADORecordset); CLEAR(lau_ADOConnection); ERASE(ltx_DirName + '\schema.ini'); lin_ImportCounter += 1; END; UNTIL lrc_File.NEXT=0; IF lrc_File.FIND('-') THEN REPEAT COPY(ltx_DirName + '\' + lrc_File.Name, ltx_TargetDirName+ '\' + lrc_File.Name); ERASE(ltx_DirName + '\' + lrc_File.Name); UNTIL lrc_File.NEXT=0; EXIT(lin_ImportCounter); Also used for problems with NIL Field Values IsValidField(vau_ADOField : Automation "'Microsoft ActiveX Data Objects 2.7 Library'.Field") : Boolean // Needed because of some trouble if the Fields Value is NIL ltx_Dummy := FORMAT(vau_ADOField.ActualSize); IF EVALUATE(lin_FieldSize, ltx_Dummy) THEN BEGIN gau_CurrField := vau_ADOField; EXIT(lin_FieldSize > 0); END; Get Values as Text CurrFieldValue() : Text[255] EXIT(FORMAT(gau_CurrField.Value));
Alexander, thanks for the detailed reply. I have moved this tot he Tips a trick form, since I am sure it will benefit many developers. thanks.