Query Excel using ACE OLEDB 12.0 driver- ERROR: Could not find installable ISAM.

  • I have the followimg cpde in a VB.Net Windows Forms App:

    I have the followimg cpde in a VB.Net Windows Forms App:
    Private Sub ImportButton_Click(sender As Object, e As EventArgs) Handles ImportButton.Click
    REM Dim ds As New DataSet("PARTS")
    Dim da As OleDbDataAdapter
    Dim conn As OleDbConnection = Nothing
    Dim sheet As String, path As String
    Dim xl As New DataTable("PARTS")
    Dim column As New DataColumn
    column.DataType = Type.GetType("System.Int32")
    column.ColumnName = "EXCEL_ROW"
    xl.Columns.Add(column)
    column = New DataColumn
    column.DataType = Type.GetType("System.String")
    column.ColumnName = "ID"
    xl.Columns.Add(column)
    column = New DataColumn
    column.DataType = Type.GetType("System.DateTime")
    column.ColumnName = "DATE"
    xl.Columns.Add(column)

    Dim I As Integer
    path = FileNameTextBox.Text.ToString
    Try
    conn = New OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0; " &
    "Data Source=""" & path & """; " &
    "Extended Properties=Excel 12.0 Xml;HDR=YES;")

    ''get sheet name
    sheet = GetSheetName(path)
    da = New OleDbDataAdapter("SELECT EXCEL_ROW, ID, [DATE] FROM [" & sheet & "]", conn)
    conn.Open()
    da.Fill(xl)
    Catch ex As Exception
    Debug.Print(ex.Message)
    MessagesTextBox.AppendText(ex.Message & vbCrLf)
    Finally
    If conn.State = ConnectionState.Open Then
    conn.Close()
    End If
    End Try

    I = 2
    For Each row In xl.Rows
    row = New String() {row("EXCEL ROW").ToString, row("ID").ToString, row("DATE").ToString}
    DGV.Rows.Add(row)
    I = I + 1
    Next
    xl = Nothing
    conn = Nothing
    End Sub

    Function GetSheetName(ByVal path As String) As String
    Dim dt As New DataTable
    Dim conn As OleDbConnection
    Dim sSheetName As String = ""
    Dim strConn As String
    Try
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" &
    "Data Source=" & path & ";" &
    "Extended Properties=""Excel 12.0 Xml;"""

    conn = New OleDbConnection(strConn)
    conn.Open()

    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
    sSheetName = dt.Rows(0).Item("Table_Name")

    Catch ex As Exception
    Debug.Print(ex.Message)
    MessagesTextBox.AppendText(ex.Message & vbCrLf)
    End Try
    GetSheetName = sSheetName
    End Function

    When I've taken care of using a file dialog to populate the text box with the Excel file name, I then click on my IMPORT button, and this code runs, and then fails with the "Could not find installable ISAM." error when it runs the conn.Open statement. Any ideas? I've tried forcing the connection string to have double quotes around the filename, as it does have spaces in the path portion. I've look online a lot and this error only seems to appear relating to MS Access. Hoping someeone out there has done this before and is familiar.

    • This topic was modified 4 years, 11 months ago by  sgmunson. Reason: code tags not the same as before, and error occurred during submit

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Not sure about VB. From SQL I use syntax like below and it works fine. Provided, of course, that ACE driver is actually installed on the SQL Server, no one keeps Excel file open and Sheet1 exists.

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\Excel File.xls',
    'select * from [Sheet1$]')

    --Vadim R.

  • Thanks for that.  Unfortunately, I can't use T-SQL for this, as the file needs to go on the user's machine as opposed to on the server.  We need this as uncomplicated as I can make it, given that I already have to secure the database at a fairly high level.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Found the OLEDB answer on StackOverflow.   Turns out the Extended Properties portion of the connection string needed to be enclosed in quotes.  Of course, that only got me past the problem with getting the data.  Now I've got a weird problem with filling the DataGridView control.   I have another app in which I create a New String() = {"value1", "value2", "value3"} and that new string variable is named newrow, and that is how I then use DataGridView1.Add(newrow).

    But now, for some reason, using the same technique in this new app, my first column gets the value "System.String[]" and the other 2 columns get nothing.   No idea why.  Something I'm doing must be messing this up.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • So here's the latest version of the code:

    Private Sub ImportButton_Click(sender As Object, e As EventArgs) Handles ImportButton.Click

    Dim da As OleDbDataAdapter
    Dim conn As OleDbConnection = Nothing
    Dim sheet As String, path As String
    Dim xl As New DataTable("PARTS")
    Dim column As New DataColumn
    column.DataType = Type.GetType("System.Int32")
    column.ColumnName = "EXCEL_ROW"
    xl.Columns.Add(column)
    column = New DataColumn
    column.DataType = Type.GetType("System.String")
    column.ColumnName = "ID"
    xl.Columns.Add(column)
    column = New DataColumn
    column.DataType = Type.GetType("System.DateTime")
    column.ColumnName = "COUNT_DATE"
    xl.Columns.Add(column)

    path = FileNameTextBox.Text.ToString
    Try
    conn = New OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0; " &
    "Data Source=""" & path & """; " &
    "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=2;""")

    ''get sheet name
    sheet = GetSheetName(path)
    da = New OleDbDataAdapter("SELECT EXCEL_ROW, ID, COUNT_DATE FROM [" & sheet & "]", conn)
    conn.Open()
    da.Fill(xl)
    Catch ex As Exception
    Debug.Print(ex.Message)
    MessagesTextBox.AppendText(ex.Message & vbCrLf)
    Finally
    If conn.State = ConnectionState.Open Then
    conn.Close()
    End If
    End Try

    Dim newrow
    Dim ExcelRow As String, ID As String, TheDate As String
    For Each row In xl.Rows
    ExcelRow = row("EXCEL_ROW").ToString
    ID = row("ID").ToString
    TheDate = row("COUNT_DATE").ToString
    TheDate = VisualBasic.Left(TheDate, 10)
    newrow = New String() {ExcelRow, ID, TheDate}
    DGV.Rows.Add(newrow)
    Next
    xl = Nothing
    conn = Nothing
    End Sub

    Function GetSheetName(ByVal path As String) As String
    Dim dt As New DataTable
    Dim conn As OleDbConnection
    Dim sSheetName As String = ""
    Dim strConn As String
    Try
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" &
    "Data Source=" & path & ";" &
    "Extended Properties=""Excel 12.0 Xml;"""

    conn = New OleDbConnection(strConn)
    conn.Open()

    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
    sSheetName = dt.Rows(0).Item("Table_Name")

    Catch ex As Exception
    Debug.Print(ex.Message)
    MessagesTextBox.AppendText(ex.Message & vbCrLf)
    End Try
    GetSheetName = sSheetName
    End Function

    And it populates only the first column of the DataGridView named DGV with "System.String[]".   I've tried changing the data types in the data table to all be strings, and still couldn't make things work.   At one point I even had a failure because of the way I named a column in the SQL query.   Just hoping someone will see the code and recognize why a DataGridView control would react that way.  The DGV control has exactly 3 columns.   Wondering if some property needs to be changed or something like that?   Am I constructing the newrow variable incorrectly, perhaps?   Any ideas at all?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • You probably will have better luck on some VB.Net forum.

    --Vadim R.

  • Finally fixed the problem.   Had to Dim newrow As String() first.   That solved the problem.   All I've got left to do now is to fix the column alignment in the second column and then figure out how to reference the values in the datagridview cells.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply