RichardKline
asked on
Reading Tab Delimited Data From text file using ODBC
I found this code which extracts data from a text file -- Well and Good! But the file is tab delimited and the routine seems to assume comma delimited values.
two questions:
1. Possible to instruct the ODBC driver to delimit by tab?
2. Possible to instruct the ODBC driver to use the first row as column headings (field names)?
Thank you.
Dim path As String = "C:\ExtractTest\"
Dim conn As New OdbcConnection( _
"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & _
path & ";")
Dim cmd As New OdbcCommand("SELECT * FROM file22269.txt", conn)
conn.Open()
Dim dr As OdbcDataReader = _
cmd.ExecuteReader(CommandB
While dr.Read()
Dim test1 As String = dr.GetString(0)
End While
dr.Close()
cmd.Dispose()
conn.Dispose()
two questions:
1. Possible to instruct the ODBC driver to delimit by tab?
2. Possible to instruct the ODBC driver to use the first row as column headings (field names)?
Thank you.
Dim path As String = "C:\ExtractTest\"
Dim conn As New OdbcConnection( _
"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & _
path & ";")
Dim cmd As New OdbcCommand("SELECT * FROM file22269.txt", conn)
conn.Open()
Dim dr As OdbcDataReader = _
cmd.ExecuteReader(CommandB
While dr.Read()
Dim test1 As String = dr.GetString(0)
End While
dr.Close()
cmd.Dispose()
conn.Dispose()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I came up with this. It allows me to use the header information in a datareader. The method does require a schemi.ini file to be placed within the same folder as the tab delimited file. Pretty flexible and simple to maintain.
Scema.ini contents:
[file22269.txt]
ColNameHeader=TRUE
Format=TabDelimited
The Code:
Dim path As String = "C:\ExtractTest\"
Dim strConnect As String = "Provider=Microsoft.Jet.OL
path & ";Extended Properties=""text;HDR=YES;
Dim conn As New OleDBConnection(strConnect
Dim cmd As New OleDbCommand("SELECT * FROM file22269.txt", conn)
conn.Open()
Dim dr As OleDbDataReader = cmd.ExecuteReader()
While dr.Read()
' do something ...
End While
dr.Close()
cmd.Dispose()
conn.Dispose()
Scema.ini contents:
[file22269.txt]
ColNameHeader=TRUE
Format=TabDelimited
The Code:
Dim path As String = "C:\ExtractTest\"
Dim strConnect As String = "Provider=Microsoft.Jet.OL
path & ";Extended Properties=""text;HDR=YES;
Dim conn As New OleDBConnection(strConnect
Dim cmd As New OleDbCommand("SELECT * FROM file22269.txt", conn)
conn.Open()
Dim dr As OleDbDataReader = cmd.ExecuteReader()
While dr.Read()
' do something ...
End While
dr.Close()
cmd.Dispose()
conn.Dispose()
ASKER
navstar16, your links got me started into the right direction but there was a lot of digging....
Thanks!
Thanks!
Dim arrColumnValues() As String
Dim arrRows() As String
Dim i, j As Integer
Dim dt As New System.Data.DataTable
Dim row As DataRow
Dim path As String = "C:\ExtractTest\"
Dim conn As New OdbcConnection( _
"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & _
path & ";")
Dim cmd As New OdbcCommand("SELECT * FROM file22269.txt", conn)
conn.Open()
Dim dr As OdbcDataReader = _
cmd.ExecuteReader(CommandB
While dr.Read()
Dim test1 As String = dr.GetString(0)
End While
arrRows = Split(test1, vbCrLf)
arrColumnNames = Split(arrRows(0), delim)
''add columns to a datatable
For n = 0 To arrColumnNames.Length - 1
dt.Columns.Add(New DataColumn(arrColumnNames(
Next
For i = 1 To arrRows.Length - 1
arrColumnValues = Split(arrRows(i), delim)
row = dt.NewRow
For j = 0 To (arrColumnNames.Length - 1)
Try
If Not IsDBNull(arrColumnValues(j
row(arrColumnNames(j)) = arrColumnValues(j)
Else
row(arrColumnNames(j)) = ""
End If
Catch ex As Exception
Console.Write("ERROR: " & ex.Message)
End Try
Next
dt.Rows.Add(row)
Next
dr.Close()
cmd.Dispose()
conn.Dispose()