asked on

A Question About Read Text Files Into a Recordset

I use ADO and the ODBC text driver to open a text file and read it into a recordset.

This is my code:

Dim cnCSV As New ADODB.Connection
Dim rs As New ADODB.Recordset

cnCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\;" & _
"Extended Properties='text;HDR=NO;FMT=Delimited'"
rs.Open "Select * From " & "Test.TXT", cnCSV, adOpenStatic, adLockReadOnly, adCmdText

But this code doesn't work with fields in the text file are delimited by space.

For example,

This is the conten of the text file

Jerry M 28
Jonh M 26
...

This is the debug info

debug.print rs.fields.count

the output is: 1

How to specify a space-delimited format?

Thanks a lot!

ASKER CERTIFIED SOLUTION

TimCottee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

n_narayanan

Rather you can go for open command for text files.
This will work for tab and space delimited

OPEN "c:\myfile.dat" FOR INPUT AS #1
'Assume that you have 3 fields
DIM fld1, fld2, fld3
WHILE NOT EOF(1)
INPUT #1, fld1, fld2,fld3
debug.print fld1
debug.print fld2
debug.print fld3
WEND
CLOSE #1

Cheers
Narayanan

Arana (G.P.)

ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\TxtFiles;Extended Properties="Text;"';
ADODataSet.TableName := 'TxtFile#csv';

SCHEMA.INI file must be created and placed into same directory as TxtFile.csv (in this example C:\TxtFiles). Content of SCHEMA.INI:

[TxtFile.csv]
Format=Delimited(;)

if you use delimited() inside the parentesis should be your custom character, i have not tried it with a SPACE character, give it a shot.

Richie_Simonetti

learning...

Jerryleo

ASKER

Hello TimCottee,

Thank you very much!