reading from txt file

  • Thread starter Thread starter Jeremy Chapman
  • Start date Start date
J

Jeremy Chapman

I'm trying to read from a txt file. The first line is the headers, the rest
are tab delimited fields. I'm trying to read the file using ODBC, but I'm
getting the error 'ERROR [42000][Microsoft][ODBC Text Driver] Syntax error
in FROM clause.

I must be missing some stuff in my code, which I've included below. Does
anyone have ideas or code snippets?

Sample file:

Client Number Invoice Date Billing Name Additional Line of Billing Name
Purchase Order Number Invoice Number Product Type User Sub Level Name Sub
Level Name User Name Additional User Name Reference 1 Reference 2 Service
Plan Name Service Plan Price Additional Local Airtime Long Distance Charges
Roaming Charges Wireless Web Phone Premium Services Pager Services Value
Added Services Other Charges and Credits Network and Licensing Charges PST,
QST and HST Subtotal before GST GST Total
12345678 Jan 09, 2004 Example Name Field 020785677036 C
250-212-2345 Test User Corporate 25/200 21.25 0.00 0.33
0.00 0.00 0.00 0.00 8.50 0.00 7.20 2.80 40.08
2.61 42.69

Code:

System.Data.Odbc.OdbcConnection pConnection = new OdbcConnection();

pConnection.ConnectionString = "Driver={Microsoft Text Driver (*.txt;
*.csv)};DBQ="+ System.IO.Path.GetDirectoryName(dlgFile.FileName);

pConnection.Open();

System.Data.Odbc.OdbcCommand pCommand = new OdbcCommand("SELECT * FROM " +
System.IO.Path.GetFileName(dlgFile.FileName));

pCommand.Connection = pConnection;

OdbcDataReader pReader = pCommand.ExecuteReader();

dataGrid1.DataSource = pReader;
 
¤
¤ I'm trying to read from a txt file. The first line is the headers, the rest
¤ are tab delimited fields. I'm trying to read the file using ODBC, but I'm
¤ getting the error 'ERROR [42000][Microsoft][ODBC Text Driver] Syntax error
¤ in FROM clause.
¤
¤ I must be missing some stuff in my code, which I've included below. Does
¤ anyone have ideas or code snippets?
¤
¤ Sample file:
¤
¤ Client Number Invoice Date Billing Name Additional Line of Billing Name
¤ Purchase Order Number Invoice Number Product Type User Sub Level Name Sub
¤ Level Name User Name Additional User Name Reference 1 Reference 2 Service
¤ Plan Name Service Plan Price Additional Local Airtime Long Distance Charges
¤ Roaming Charges Wireless Web Phone Premium Services Pager Services Value
¤ Added Services Other Charges and Credits Network and Licensing Charges PST,
¤ QST and HST Subtotal before GST GST Total
¤ 12345678 Jan 09, 2004 Example Name Field 020785677036 C
¤ 250-212-2345 Test User Corporate 25/200 21.25 0.00 0.33
¤ 0.00 0.00 0.00 0.00 8.50 0.00 7.20 2.80 40.08
¤ 2.61 42.69
¤
¤ Code:
¤
¤ System.Data.Odbc.OdbcConnection pConnection = new OdbcConnection();
¤
¤ pConnection.ConnectionString = "Driver={Microsoft Text Driver (*.txt;
¤ *.csv)};DBQ="+ System.IO.Path.GetDirectoryName(dlgFile.FileName);
¤
¤ pConnection.Open();
¤
¤ System.Data.Odbc.OdbcCommand pCommand = new OdbcCommand("SELECT * FROM " +
¤ System.IO.Path.GetFileName(dlgFile.FileName));
¤
¤ pCommand.Connection = pConnection;
¤
¤ OdbcDataReader pReader = pCommand.ExecuteReader();
¤
¤ dataGrid1.DataSource = pReader;
¤

I would recommend using the Jet OLEDB provider instead:

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

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

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

'...
'...

TextConnection.Close()

Also, for a tab delimited file you will need a schema.ini file which would look something like this:

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


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