read .csv file w/ data adapter

[christywarner]

Hi,

I'm trying to read a .csv file using a data adapter.
I've tried several different connection strings, but
no luck yet. Here is my latest string....


If ext.Text = "csv" Then
'Connection string for a Text File
Dim connectionstring As String
Dim conn2 As New Microsoft.Data.Odbc.OdbcConnection
Dim da2 As New Microsoft.Data.Odbc.OdbcDataAdapter
Dim dt2 As System.Data.DataTable
Dim ds2 As System.Data.DataSet
connectionstring = "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\automated auditors\testdata"
'Query the Employees.txt file as a table
conn2 = New Microsoft.Data.Odbc.OdbcConnection(connectionstring)
Dim SQL As String = "SELECT * FROM invoicescsv.csv"
' Create connection object
Dim cmd As New Microsoft.Data.Odbc.OdbcCommand(SQL)
cmd.Connection = conn2
' Open connection
conn2.Open()
da2 = New Microsoft.Data.Odbc.OdbcDataAdapter("SELECT * FROM invoicescsv.csv", conn2)
ds2 = New System.Data.DataSet
dt2 = ds2.Tables.Add("invoices&quot
da2.Fill(dt2)
conn2.Close()
End If

I created a schema.ini file also with the column names,
put it in the same directory that the .csv file is in, and it didn't work. I keep getting a "System Error " First Exception ...

thanks for your help!

 

[christywarner]

the exact error message I'm receiving is:

A first chance exception of type 'Microsoft.Data.Odbc.OdbcException' occurred in microsoft.data.odbc.dll

Additional information: System error.

 

[bboffin]

Did you create an ODBC DSN in order to create the schema.ini file?

If so try using just:

    "DSN=MyTextDSN;"

as your connection string as the DSN contains all the necessary information including the path to the files and hence the location of the Schema.ini file as well as lots of other details.

Otherwise set up a DSN assigning the path and specifically generating the Schema.ini file via the ODBC manager and try the connection string above.