da.Fill(dt) throwing an error when loading a .csv using odbc adapter

Question

The error is:

The Microsoft Jet database engine could not find the object `FileName.csv` Make sure the object exists and that you spell its name and the path name correctly.

I am using vb.net to determine the path and name so that isn't the issue.

My code:

 Public Sub ConvertCSV()
    Dim DirectoryPath, FileName, FileNameAndPath As String
    Dim openFileDialog1 As New OpenFileDialog()

    openFileDialog1.InitialDirectory = "C:\Documents and Settings\ADMIN\Desktop"
    openFileDialog1.Filter = ".csv files (*.csv)|*.csv"
    openFileDialog1.FilterIndex = 2
    openFileDialog1.RestoreDirectory = True

    If openFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
        FileNameAndPath = openFileDialog1.FileName
        Dim fi As New IO.FileInfo(FileNameAndPath)
        DirectoryPath = fi.DirectoryName
        FileName = System.IO.Path.GetFileName(FileNameAndPath)

        'Note that the folder is specified in the connection string,
        'not the file. That's specified in the SELECT query, later.
        Dim connString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
            & DirectoryPath & ";Extended Properties=""Text;HDR=No;FMT=Delimited"""
        Dim conn As New Odbc.OdbcConnection(connString)

        'Open a data adapter, specifying the file name to load
        Dim da As New Odbc.OdbcDataAdapter("SELECT * FROM [" & FileName & "]", conn)
        'Then fill a data table, which can be bound to a grid
        Dim dt As New DataTable
        da.Fill(dt)

        'grdCSVData.DataSource = dt
    End If
End Sub

I have read Reading CSV into using OLEDB and I am not using a 64bit system, cpu, or visual studio.

How to read a CSV file into a .NET Datatable doesn't work. I don't want to introduce anything custom. That's why I am updating this project.

http://social.msdn.microsoft.com/Forums/en-US/4f860035-e081-44b9-a08c-b3911f682975/problem-using-odbcdatareader-to-read-from-a-csv-file says I should only use the filename, not the entire path in the SQL statement. That's what I am doing.

I've seen this code in some shape or form all over the place Visual Basic How do I read a CSV file and display the values in a datagrid?

Edit: Can I learn anything at the line Dim da As New Odbc.OdbcDataAdapter from the mouse over properties like if the connection failed or what information should be in the various fields?

Check this one: http://kbcsv.codeplex.com/, don't use ODBC to parse CSV, it's very slow, 10 times slower than KBCSV or so. [KBCsv has built-in support for reading into a DataSet](http://stackoverflow.com/a/3306491/897326). Also see [Creating a DataTable from CSV File](http://stackoverflow.com/questions/3306330/creating-a-datatable-from-csv-file). — Victor Zakharov, Jan 16 '14 at 21:23
I appreciate speed, but this isn't a speed sensitive project. This must be a formatting thing. The error occurs at da.Fill(dt). Is there a way to check the connection? — ZL1Corvette, Jan 16 '14 at 21:52
Speed means reliability. If something was created to work fast, it already assumes that it works stable. So by getting "fast" you are getting "no problems" (a high chance of, at least). — Victor Zakharov, Jan 16 '14 at 21:56
Please elaborate in your question. For me it worked from the first try. — Victor Zakharov, Jan 17 '14 at 19:11

score 0 · Accepted Answer · answered Jan 24 '14 at 22:25

My problem was my oledb adapter. For readability my button calls a sub that brings up the openfiledialog and then depending on filter index calls up the csv option and the xls option. Both are pretty similar, but the xls needs sheet name detection. I'll include for informational purposes.

Public Sub ConvertCSV(ByVal FileNameAndPath As String)
    Dim DirectoryPath, FileName As String
    Dim fi As New IO.FileInfo(FileNameAndPath)
    DirectoryPath = fi.DirectoryName
    FileName = System.IO.Path.GetFileName(FileNameAndPath)

    Dim inputString As String = ""

    Dim sr As New IO.StreamReader(FileNameAndPath)
    inputString = sr.ReadToEnd()
    sr.Close()

    Dim dtnew As New DataTable()
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DirectoryPath & ";Extended Properties=Text;"
    Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" & FileName & "]", conn)
    da.Fill(dtnew)
End Sub

My Excel solution:

Public Sub ConvertExcel(ByVal FileNameAndPath As String)

    Dim DirectoryPath, FileName As String
    Dim fi As New IO.FileInfo(FileNameAndPath)
    DirectoryPath = fi.DirectoryName
    FileName = System.IO.Path.GetFileName(FileNameAndPath)

    Dim inputString As String = ""

    Dim sr As New IO.StreamReader(FileNameAndPath)
    inputString = sr.ReadToEnd()
    sr.Close()

    Dim dtnew As New DataTable()
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileNameAndPath & ";Extended Properties=Excel 8.0;"

    conn.Open()

    Dim dtSheets As DataTable = conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)
    Dim listSheet As New List(Of String)
    Dim drSheet As DataRow

    For Each drSheet In dtSheets.Rows
        listSheet.Add(drSheet("TABLE_NAME").ToString())
    Next

    ''Make a sheet selection Form
    'If dtSheets.Rows.Count > 1 Then

    'End If

    Dim StringCheck As String = "SELECT * FROM " & dtSheets.Rows(0).Item(2).ToString
    Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" & dtSheets.Rows(0).Item(2).ToString & "]", conn)
    da.Fill(dtnew)

End Sub

Only other thing you have to do is declare some tables and adapters elsewhere.

Dim da, ds, dt, dtnew As Object

da.Fill(dt) throwing an error when loading a .csv using odbc adapter

1 Answers1