Text File

  • Thread starter Thread starter Andy Williams
  • Start date Start date
A

Andy Williams

I'm trying to open a comma delimited text file using the following code, but
no luck...

Dim cn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\monkey.txt;Extended Properties=""text;HDR=no;FMT=Delimited""")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM c:\monkey.txt",
cn)
cn.Open()
Dim rdr As OleDbDataReader
rdr = cmd.ExecuteReader
Do While rdr.Read()
Debug.Write(rdr.Item(0))
Loop
cn.Close()

What am I missing?

TIA -Andy

btw, I'm a SQL Server guy, so I'm a bit out of my league here...
 
Hi Andy,

First you need to connect to the folder, not to the file. Your connection
string should look like

Dim cn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended
Properties=""text;HDR=no;FMT=Delimited""")

You also need to use file name only in a SQL statement and put it into
square brackets

SELECT * FROM [monkey.txt]

If you do not have a schema.ini file defined for your text file, then
provider will try to guess type of the columns based on a content of the
file. If you need specific type for each column, then you need to create
Schema.ini
 
Thanks Val, that works perfectly.

-Andy

Val Mazur (MVP) said:
Hi Andy,

First you need to connect to the folder, not to the file. Your connection
string should look like

Dim cn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended
Properties=""text;HDR=no;FMT=Delimited""")

You also need to use file name only in a SQL statement and put it into
square brackets

SELECT * FROM [monkey.txt]

If you do not have a schema.ini file defined for your text file, then
provider will try to guess type of the columns based on a content of the
file. If you need specific type for each column, then you need to create
Schema.ini
--
Val Mazur
Microsoft MVP

http://xport.mvps.org



Andy Williams said:
I'm trying to open a comma delimited text file using the following code,
but no luck...

Dim cn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\monkey.txt;Extended Properties=""text;HDR=no;FMT=Delimited""")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM c:\monkey.txt",
cn)
cn.Open()
Dim rdr As OleDbDataReader
rdr = cmd.ExecuteReader
Do While rdr.Read()
Debug.Write(rdr.Item(0))
Loop
cn.Close()

What am I missing?

TIA -Andy

btw, I'm a SQL Server guy, so I'm a bit out of my league here...
Click to expand...
Click to expand...
 
Back
Top