• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 930
  • Last Modified:

Could not find installable ISAM - Excel 2007+

Good morning all,

I am trying to import an Excel file into a datatable. I have run into the Could not find installable ISAM error. I've read a ton of articles on it and they mostly point to the connection string or MDAC. I've verified both. What am I missing?

I am running this on Windows Server 2003 SBE, Service Pack 2. Latest MDAC. Jet version is most recent as well.

My code is listed below. My resulting connection string is

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test.xlsx;Extended Properties="Excel 12.0;HDR=YES;"

Dim strExcelConn As String = ""
        Dim strUploadFileName As String = "c:\test.xlsx"
        strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties=""Excel 12.0;HDR=YES;"""
        Dim dtExcel As New DataTable()

        Try
            Dim conn As New OleDbConnection(strConn)
            conn.Open()     '   <-- fails here!

            Dim da As New OleDbDataAdapter("select * from [Sheet1$]", conn)
            da.Fill(dtExcel)
            conn.Close()

        Catch ex As Exception
            lblMessages.Text = strConn & "<br/>" & ex.Message
            Return Nothing
            Exit Function
        End Try
        Return dtExcel

Open in new window

0
DEWebWorks
Asked:
DEWebWorks
  • 8
  • 5
1 Solution
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, probably your OS is 64bits and the application pool where your web application is running does not has enabled the support for 32 bit applications, if this is your case, you can enable it by using the next script:
1. Login as an Administrator
2. Open up a Command Prompt window, type the following command to enable 32-bit Application Pools:
cscript %SystemDrive%\inetpub\AdminScripts\adsutil.vbs set w3svc/AppPools/Enable32bitAppOnWin64 1

IIS7 has a graphical interface to do that (example):
Application Pool Advanced Settings - IIS7
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hey, also you can install the 64bit version of Microsoft Access Database Engine 2010, try this link, be sure to download the 64bit version:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Or maybe you are not installed the Microsoft Access Database Engine components at all... install the version that match your OS (32/64bit).
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
DEWebWorksAuthor Commented:
I am not running 64 bit and I have MDAC installed. It's the latest version.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Ok then install this:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255

it is required to read the 2007-2010 format
0
 
DEWebWorksAuthor Commented:
Sorry I missed that the first time. I just installed it. Followed all of the instructions. I still get the error. My new connection string looks like this:

Provider=Microsoft.ACE.OLEDB.12.0;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\text.xlsx;Extended Properties="Excel 14.0;HDR=YES;"

Does that look right to you?
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, no, please follow the instructions on this site: www.connectionstrings.com/excel-2007 

Also for each document type you will require a slight modification in your connection string.
0
 
DEWebWorksAuthor Commented:
Wohoo! I see progress! I'm now getting an error on my select statement:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
Dim da As New OleDbDataAdapter("select * from [Sheet1$]", conn)
            da.Fill(dtExcel)
            conn.Close()

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, try with this:
Dim myExcelData As New System.Data.DataTable
Dim myConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Using dta As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", myConnectionString)
    dta.Fill(myExcelData)
End Using

Open in new window

0
 
DEWebWorksAuthor Commented:
Thank you so much!
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
You're welcome
0
 
DEWebWorksAuthor Commented:
Wild thing... I found that most of this was caused by a bad copy/paste from MSDN. My select statement was fine, but when I copied it and pasted it in my query window, it looked like this:
 sql error
I went back and retyped the original connection string and it worked fine as well. Again, thanks for spending your weekend helping me with this, I appreciate it!
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
No problem, glad to have been of help :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now