How to read a dbase DBF file?

Business Central/NAV Developers Forum
1

Hello, I’m trying to write a report that imports data froma dBase *.dbf file. The table has 250K+ records and because Excel truncates at 64K, I can not convert the file to a complete text file for dataporting. (I’ve looked at dbf2txt, but it creates a fixed length file and there are errors in the field sizes). I’ve read all the postings on this site on ADO, but not ever using that automation before, I was wondering if anyone can post a really simple sample of code to read from a dbase dbf file using ADO? Something like; CREATE(AdoConn); AdoConn.OPEN(somefile.dbf); AdoRecSet.OPEN(AdoConn); FirstRow = //Get First Row// MESSAGE(FirstRow); AdoConn.CLOSE(); Any other ways that you can suggest to read a *.dbf file? Thanks in advance, john

2

MSDN article KB326548 contains the following:

quote:

NOTE: The code in this article assumes that you have created an ADO Connection object: cnn, and an ADO RecordSet object: rst. Open dBASE The following code opens a dBASE ISAM database. If a dBASE file (for example, dBaseFile.dbf) is located at c:\somepath, where C is the drive, and where somepath is the folder that contains dBaseFile.dbf, as follows: cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\somepath;" & _ "Extended Properties=DBASE III;" Specify the file name in the SQL statement as follows: rst.Open "Select * From dBaseFile", cnn, , ,adCmdText

So, to translate this into C/AL: Declare cnn as ‘Microsoft ActiveX Data Objects 2.x Library.Connection’ Declare rst as ‘Microsoft ActiveX Data Objects 2.x Library.Recordset’ Declare flds as ‘Microsoft ActiveX Data Objects 2.x Library.Fields’ Declare fld as ‘Microsoft ActiveX Data Objects 2.x Library.Field’ CREATE(cnn); CREATE(rst); CREATE(flds); CREATE(fld); cnn.Open('Provider=Microsoft.Jet.OLEDB.4.0;' + 'Data Source=c:\somepath;' + 'Extended Properties=DBASE III;'); rst.Open('Select * from dBaseFile', cnn, 0, 1, 1); flds := rst.Fields(); WHILE NOT rst.EOF() DO BEGIN fld := flds.Item('Field1'); MESSAGE('Field1 value = %1.',FORMAT(fld.Value())); //... rst.MoveNext(); END; rst.Close(); CLEAR(fld); CLEAR(flds); CLEAR(rst); CLEAR(cnn);

3

Fritz! Thank-you!! Works like a charm! For those also using this example, note that I had to comment out the CREATE of flds since rst.Fields() returns the required instance of flds. Same with fld. And of course, I replaced the literal reference to ‘Field1’ with the real field/column name. //CREATE(flds); //CREATE(fld); Awesome, now I can relax[:p] this weekend -john