HOWTO: Use ADO and ODBC Text Driver to Open Delimited Text Files

ID: Q233318

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1
Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0

SUMMARY

This article discusses how to open a delimited text file using ADO and the ODBC Text driver. It is possible to use RDO and DAO to open such text files. However, the procedure is a little different. Refer to the MORE INFORMATION section below for major changes that make it easier to open such files in ADO. This article assumes that the reader has a previous knowledge of ADO.

MORE INFORMATION

ADO is different than RDO or DAO when dealing with Text files in the following ways:

ADO does not use Schema.ini file and hence it is not required as it was with RDO and DAO. Schema.ini file usually contains information about the text file such as File name, File format, field names, etc.

If using a DSN-less connection, the syntax of the connection string is different:
- ADO : "Driver={Microsoft Text Driver (*.txt; *.csv)};dbq=C:\...\; Extensions=asc,csv,tab,txt;"
- RDO : "DRIVER={Microsoft Text Driver (*.txt; *.csv)};dbq=C:\...\;"
The first line in the delimited text file will be used by ADO for column names. If you don't want to have column names in the file, then you must leave the first row blank; otherwise the first row will be used by ADO as a source for column names and your RecordCount will be short by one.

You don't need to specify the file format in advance, ADO can handle different delimited files such as "asc, "csv," "tab" or "txt" based on the file extension as specified in the SQL statement.

The following sample codes demonstrate how to open a recordset based on a csv "Comma Separated" file format:

Sample 1:


  ' DSN-Less connection
   cn1.Open "Driver={Microsoft Text Driver (*.txt;" _
          &  " *.csv)};dbq=C:\...\;Extensions=asc,csv,tab,txt;"

   rs1.Open "select * from TextFile.csv",cn1,adOpenStatic,adLockReadOnly

Sample 2:


  ' DSN connection
   cn1.Open "DSN=Text;"

   rs1.Open "TextFile.csv",cn1,adOpenStatic,adLockReadOnly,adCmdUnknown

Please note that it is possible to use the ADO Data Control with DSN-less connections by copying the connection string as it appears in Sample 1, and then pasting it in the ConnectionString property of the ADO Data Control.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

Q187670 HOWTO: Use RDO and ODBC Text Driver to Open a Delimited

"The Microsoft Jet Database Engine Programmer's guide (2nd Edition)", Microsoft Press By Dan Haught and Jim Ferguson (ISBN 1-57231-342-0)

Additional query words:

Keywords : kbADO kbDatabase kbVBp kbVBp500 kbVBp600 kbGrpVBDB kbDSupport
Version : WINDOWS:1.5,2.0,2.1,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto