Text File

[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...

 

[Val Mazur \(MVP\)]

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

 

[Andy Williams]

Thanks Val, that works perfectly.

-Andy

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

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...