Using ADO to Read and Parse a Text File - April 2002

ADO [Active Data Objects] can be used to read a variety of file formats. I have used it to read and parse a CSV and fixed width formatted text file. ADO reads and parses the file into a recordset. When I first needed to parse a text file I tried various routines - some provided via other users - and none seemed satisfactory. So I did some research and discovered that ADO will perform the task. I've revisited the code, wrote a demo program and made this the April 2002 tip-of-the-month.

The Demo Program

I created a VB 6 demo program to show how to use ADO to read and parse a CSV [comma separated value] and fixed width formatted text file. The demo program references ADO 2.6. You could probably change the reference to an earlier version of ADO. The results are shown in a 6.0 ListView. The demo program:

Select either the 'Parse a CSV file' or 'Parse a Fixed Field Size file' radio button then click 'Use ADO to Read and Parse the File'. The following result screens are displayed:


File 'TestFile.csv' is parsed and the data is shown in a ListView control.
Notice the Schema.Ini contains information used by ADO to parse the file.


File 'TestFile.txt' is parsed and the data is shown in a ListView control.
The Schema.Ini is used by ADO to determine fixed field widths.

 

Operative ADO Code

 ' build the connection string
sCon = "Driver={Microsoft Text Driver (*.txt; *.csv)}; "
sCon = sCon & "DEFAULTDIR=" & sPath & "; "
sCon = sCon & "Extensions=" & sExt & "; "
sCon = sCon & "Persist Security Info=False"

cnn.Open sCon 'open the connection

' read and parse into a recordset
rs.Open "Select * from [" & sFile & "]", cnn, adOpenStatic, adLockReadOnly, adCmdText

Registry Entry for Jet 4.0 Engine

I discovered that a 'not disabled' entry is required in registry Jet\4.0\Engines\Text\DisabledExtensions. So if there is no entry for the desired file extension I add the entry. In this case I had to add 'csv' to entry 'DisabledExtensions'. Function 'AddExtToRegistryIfNeeded' is used. See the source code for details. A sample registry entry:


DisabledExtension must contain the file extension entry.
The "!" character in front of the value indicates these extensions are not disabled.

From Fred Block

Monday 08/19/2002 7:47:14am
Name: Fred Block
Comments: Re: Using ADO to Read and Parse a Text File - April 2002

Great demo - thanks!

Please mention that the SCHEMA.INI file is used by ADO (that it must exist in the same folder as the source text file) and is "not" parsed internally by the Demo application. It took me a while to figure this out.

Thanks -- Fred

From Michael Cole

On April 4th, 2002, Michael Cole [michael.cole@hansen.com] reported that Robert Smith [smith@smithvoice.com] has some code on his site that shows various data conversion facilities. Click here to view Robert Smith's site.

Most of what Robert did was in DAO, which was good because I still use DAO rather than ADO.  Some of his stuff, he has converted.

From his menu...

Database Tips/Export & Convert Anything (Exporting to anything via DAO)
Database Tips/Bigiron to ISAM...        (ADO Export to anything)
Database Tips/All ISAMs are...          (DAO Import from Text)

I have created a DLL which will import or export anything via DAO and ISAMs.  If you are interested, you can have a look.  One of these days, I may even create an ADO version.  Either that or I'll just steal your code. :-)

Michael Cole

The Demo Program Uses These Modules

Module - Click for More Info Description Used in the Demo To
modConnectToWebSite.bas Connect to a web site Show this info when Help/Info is
selected from the demo.
modLocationLocationLocation.bas Form Housekeeping Center the forms.
TBRegistry.bas Read and Write to the Registry Used by function 'AddExtToRegistryIfNeeded'
modAddBackSlash.bas Add a backslash if needed. Add a backslash to App.Path
clsLVMaxCol.cls Set Listview column widths. Set the column widths of the results screen.

Download VB 6 Source Code

Click here to download the VB 6 source code [23k]

Previous Tip   Tip Index    Next Tip    First Tip

Home

Updated 2011/10/06