How To read tab delimited file using ADO, Connect to text driver?

[Jessie Niu]

Hi, I can connect to text file driver with coma delimited file, how not Tab
delimited?

if I use the following :
conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & sFilePath & ";FMT=TabDelimited;", "", ""

it does not work.

Thanks


Jessie

 

[Paul Clement]

¤ Hi, I can connect to text file driver with coma delimited file, how not Tab
¤ delimited?
¤
¤ if I use the following :
¤ conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
¤ "DBQ=" & sFilePath & ";FMT=TabDelimited;", "", ""
¤
¤ it does not work.

Tab delimited files require a schema.ini file which would look something like the following:

[TabDelimitedFile.txt]
ColNameHeader=True
Format=TabDelimited
CharacterSet=ANSI

....and is placed in the same location as the text file.

I would use the Jet OLEDB provider with the Text driver to open the file:

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "E:\My Documents\TextFiles" & ";" & _
"Extended Properties=""Text;HDR=NO;"""

Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
TextConnection.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM TabDelimitedFile.txt",
TextConnection)

Dim ds As New DataSet("TextFiles")
da.Fill(ds, "TabDelimited")


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Jessie Niu]

Thanks

Jessie

¤ Hi, I can connect to text file driver with coma delimited file, how not Tab
¤ delimited?
¤
¤ if I use the following :
¤ conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
¤ "DBQ=" & sFilePath & ";FMT=TabDelimited;", "", ""
¤
¤ it does not work.

Tab delimited files require a schema.ini file which would look something like the following:

[TabDelimitedFile.txt]
ColNameHeader=True
Format=TabDelimited
CharacterSet=ANSI

...and is placed in the same location as the text file.

I would use the Jet OLEDB provider with the Text driver to open the file:

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "E:\My Documents\TextFiles" & ";" & _
"Extended Properties=""Text;HDR=NO;"""

Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
TextConnection.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM TabDelimitedFile.txt",
TextConnection)

Dim ds As New DataSet("TextFiles")
da.Fill(ds, "TabDelimited")


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)