Import from excel - Could not find installable ISAM

I'm trying to quickly create a way of importing data from an excel sheet on my C drive to a table on a sql 2005 db hosted by a provider.

I set this up according to the following:

http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

The article is in C# but I write in VB. I think I got it except I get this error:

Could not find installable ISAM.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.OleDb.OleDbException: Could not find installable ISAM.

Source Error:

Line 23: Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select ID,Data FROM [Data$]", connection)Line 24:Line 25: connection.Open()Line 26: Line 27: ' Create DbDataReader to Data Worksheet

Microsoft is not much help. A couple of folks say to contact my host. Before I do I just want to be sure there code I have is correct.

Could someone take a quick look at this.

Thanks,

Here is the code behind the button.

ProtectedSub BTNImport_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles BTNImport.Click

' Connection String to Excel Workbook

Dim excelConnectionStringAsString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;ExtendedProperties=""Excel 8.0;HDR=YES;"""

' Connection to Excel Workbook

Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select ID,Data FROM [Data$]", connection)

connection.Open()

' Create DbDataReader to Data Worksheet

Using drAs Data.Common.DbDataReader = command.ExecuteReader()

' SQL Server Connection String

Dim connectionStringAsString = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString

'Origina from David Hyden's site - Dim sqlConnectionString As String = "Data Source=.;Initial Catalog=Test;Integrated Security=True"

' Bulk Copy to SQL Server

Using bulkCopyAs Data.SqlClient.SqlBulkCopy =New Data.SqlClient.SqlBulkCopy(connectionString)

bulkCopy.DestinationTableName ="ExcelData"

bulkCopy.WriteToServer(dr)

EndUsing

EndUsing

EndUsing

EndSub

Are you running this code on your local machine?

|||

Yes

|||

Actually, I'm using Visual Studio 20005 and am using Sql server express edition to test it.

|||

Maybe this will help

http://support.microsoft.com/kb/209805

|||

I read this before. This relates to Access.

I did push the page up to production in the even that it was my local machine. But, when I hit it online I still get the error.

|||

Got it. I think it was the comination of two problems.

First - I may have needed the following namespace:

Imports System.Data.OleDb

Previously I only had System.Data

Second- I revised the connection string as follows:

Dim excelConnectionStringAsString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0"

Works like a charm now.

import excel data fail in sql 2005

Hello,
When I use the sql server 2005 import and export wizard to import an excel
file, I always get "Could not find installable ISAM.(microsoft JET database
engine)" error.
my operating system is xp with sp2, sql server 2005, office 2003 and office
2007.
all of them have latest update (through microsoft update).
the excel file type is 97-2003 work sheet.
Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
MSEXCL40.dll (4.0.8618.0)
but I can not find the
HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
reinstall the xp sp2. there is
HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
Currently, both sql 2005 and access 2003 can not import the data from excel
(97-2003 worksheet), but the access 2007 works well.
So I have to import the excel file into access 2007 first and then import it
into sql 2005.
Please tell me what can I do to fix this issue, your help are really
appreciated.Hi
I assume that you have seen
http://support.microsoft.com/kb/283881/
If you convert the file to excel 2007 can you import it? For a connection
string you would need Extended Properties=Excel 8.0 and I assume this is
expecting Excel 12.0 but I guess that is not going to help your current issue!
John
"med" wrote:
> Hello,
> When I use the sql server 2005 import and export wizard to import an excel
> file, I always get "Could not find installable ISAM.(microsoft JET database
> engine)" error.
> my operating system is xp with sp2, sql server 2005, office 2003 and office
> 2007.
> all of them have latest update (through microsoft update).
> the excel file type is 97-2003 work sheet.
> Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
> MSEXCL40.dll (4.0.8618.0)
> but I can not find the
> HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
> reinstall the xp sp2. there is
> HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
> Currently, both sql 2005 and access 2003 can not import the data from excel
> (97-2003 worksheet), but the access 2007 works well.
> So I have to import the excel file into access 2007 first and then import it
> into sql 2005.
> Please tell me what can I do to fix this issue, your help are really
> appreciated.
>
>
>
>
>
>|||Thanks for your reply,
It does not work after I convert the excel file to 2007 such as 1.xlsx
The sql server 2005's import wizard can only support up to Excel 97-2005 so
that I get "File path contains invalid Excel file,Please provide file with
.xls extension" error.
I did read the article, but I can not even find the
MICROSOFT\Jet\4.0\engines\excel key in the registor.
My access 2007 does work well when import the excel file, but access 2003
and sql server 2005 does not work?
any thoughts?
"John Bell" wrote:
> Hi
> I assume that you have seen
> http://support.microsoft.com/kb/283881/
> If you convert the file to excel 2007 can you import it? For a connection
> string you would need Extended Properties=Excel 8.0 and I assume this is
> expecting Excel 12.0 but I guess that is not going to help your current issue!
> John
>
> "med" wrote:
> > Hello,
> >
> > When I use the sql server 2005 import and export wizard to import an excel
> > file, I always get "Could not find installable ISAM.(microsoft JET database
> > engine)" error.
> >
> > my operating system is xp with sp2, sql server 2005, office 2003 and office
> > 2007.
> > all of them have latest update (through microsoft update).
> >
> > the excel file type is 97-2003 work sheet.
> >
> > Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
> > MSEXCL40.dll (4.0.8618.0)
> > but I can not find the
> > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
> > reinstall the xp sp2. there is
> > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
> >
> > Currently, both sql 2005 and access 2003 can not import the data from excel
> > (97-2003 worksheet), but the access 2007 works well.
> >
> > So I have to import the excel file into access 2007 first and then import it
> > into sql 2005.
> >
> > Please tell me what can I do to fix this issue, your help are really
> > appreciated.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||Hi
Have you tried creating an ODBC DSN and see if that works?
Searching for "Could not find installable ISAM" on microsoft.com
http://search.microsoft.com/results.aspx?q=Could+not+find+installable+ISAM&qsc0=0&FORM=QBME1&l=1&mkt=en-GB
turns up a large number of hits such as http://support.microsoft.com/kb/318161
John
"med" wrote:
> Thanks for your reply,
> It does not work after I convert the excel file to 2007 such as 1.xlsx
> The sql server 2005's import wizard can only support up to Excel 97-2005 so
> that I get "File path contains invalid Excel file,Please provide file with
> .xls extension" error.
> I did read the article, but I can not even find the
> MICROSOFT\Jet\4.0\engines\excel key in the registor.
> My access 2007 does work well when import the excel file, but access 2003
> and sql server 2005 does not work?
> any thoughts?
> "John Bell" wrote:
> > Hi
> >
> > I assume that you have seen
> > http://support.microsoft.com/kb/283881/
> >
> > If you convert the file to excel 2007 can you import it? For a connection
> > string you would need Extended Properties=Excel 8.0 and I assume this is
> > expecting Excel 12.0 but I guess that is not going to help your current issue!
> >
> > John
> >
> >
> > "med" wrote:
> >
> > > Hello,
> > >
> > > When I use the sql server 2005 import and export wizard to import an excel
> > > file, I always get "Could not find installable ISAM.(microsoft JET database
> > > engine)" error.
> > >
> > > my operating system is xp with sp2, sql server 2005, office 2003 and office
> > > 2007.
> > > all of them have latest update (through microsoft update).
> > >
> > > the excel file type is 97-2003 work sheet.
> > >
> > > Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
> > > MSEXCL40.dll (4.0.8618.0)
> > > but I can not find the
> > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
> > > reinstall the xp sp2. there is
> > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
> > >
> > > Currently, both sql 2005 and access 2003 can not import the data from excel
> > > (97-2003 worksheet), but the access 2007 works well.
> > >
> > > So I have to import the excel file into access 2007 first and then import it
> > > into sql 2005.
> > >
> > > Please tell me what can I do to fix this issue, your help are really
> > > appreciated.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >|||Thanks John,
I had done a lot this kind of search, but can not find anything helpful.
Can you reproduce my error, just install the office2003, office2007,and sql
server 2005 on XP with sp2 to see if you can get this error?
I can create link servers to access databases by using either "Jet 4.0" or
"office 12.0 access database engine ole db provider", but no one work for
Excel.
Please test it and no more assumptions.
"John Bell" wrote:
> Hi
> Have you tried creating an ODBC DSN and see if that works?
> Searching for "Could not find installable ISAM" on microsoft.com
> http://search.microsoft.com/results.aspx?q=Could+not+find+installable+ISAM&qsc0=0&FORM=QBME1&l=1&mkt=en-GB
> turns up a large number of hits such as http://support.microsoft.com/kb/318161
> John
> "med" wrote:
> > Thanks for your reply,
> > It does not work after I convert the excel file to 2007 such as 1.xlsx
> >
> > The sql server 2005's import wizard can only support up to Excel 97-2005 so
> > that I get "File path contains invalid Excel file,Please provide file with
> > .xls extension" error.
> >
> > I did read the article, but I can not even find the
> > MICROSOFT\Jet\4.0\engines\excel key in the registor.
> >
> > My access 2007 does work well when import the excel file, but access 2003
> > and sql server 2005 does not work?
> >
> > any thoughts?
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I assume that you have seen
> > > http://support.microsoft.com/kb/283881/
> > >
> > > If you convert the file to excel 2007 can you import it? For a connection
> > > string you would need Extended Properties=Excel 8.0 and I assume this is
> > > expecting Excel 12.0 but I guess that is not going to help your current issue!
> > >
> > > John
> > >
> > >
> > > "med" wrote:
> > >
> > > > Hello,
> > > >
> > > > When I use the sql server 2005 import and export wizard to import an excel
> > > > file, I always get "Could not find installable ISAM.(microsoft JET database
> > > > engine)" error.
> > > >
> > > > my operating system is xp with sp2, sql server 2005, office 2003 and office
> > > > 2007.
> > > > all of them have latest update (through microsoft update).
> > > >
> > > > the excel file type is 97-2003 work sheet.
> > > >
> > > > Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
> > > > MSEXCL40.dll (4.0.8618.0)
> > > > but I can not find the
> > > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
> > > > reinstall the xp sp2. there is
> > > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
> > > >
> > > > Currently, both sql 2005 and access 2003 can not import the data from excel
> > > > (97-2003 worksheet), but the access 2007 works well.
> > > >
> > > > So I have to import the excel file into access 2007 first and then import it
> > > > into sql 2005.
> > > >
> > > > Please tell me what can I do to fix this issue, your help are really
> > > > appreciated.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >|||Hi
Unfortunately I don't have the facilities to do that. In general a
production environment would not have office installed. If you are not able
to create your own test environment then you may want to raise a PSS incident.
John
"med" wrote:
> Thanks John,
> I had done a lot this kind of search, but can not find anything helpful.
> Can you reproduce my error, just install the office2003, office2007,and sql
> server 2005 on XP with sp2 to see if you can get this error?
> I can create link servers to access databases by using either "Jet 4.0" or
> "office 12.0 access database engine ole db provider", but no one work for
> Excel.
> Please test it and no more assumptions.
>
> "John Bell" wrote:
> > Hi
> >
> > Have you tried creating an ODBC DSN and see if that works?
> >
> > Searching for "Could not find installable ISAM" on microsoft.com
> > http://search.microsoft.com/results.aspx?q=Could+not+find+installable+ISAM&qsc0=0&FORM=QBME1&l=1&mkt=en-GB
> >
> > turns up a large number of hits such as http://support.microsoft.com/kb/318161
> >
> > John
> >
> > "med" wrote:
> >
> > > Thanks for your reply,
> > > It does not work after I convert the excel file to 2007 such as 1.xlsx
> > >
> > > The sql server 2005's import wizard can only support up to Excel 97-2005 so
> > > that I get "File path contains invalid Excel file,Please provide file with
> > > .xls extension" error.
> > >
> > > I did read the article, but I can not even find the
> > > MICROSOFT\Jet\4.0\engines\excel key in the registor.
> > >
> > > My access 2007 does work well when import the excel file, but access 2003
> > > and sql server 2005 does not work?
> > >
> > > any thoughts?
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I assume that you have seen
> > > > http://support.microsoft.com/kb/283881/
> > > >
> > > > If you convert the file to excel 2007 can you import it? For a connection
> > > > string you would need Extended Properties=Excel 8.0 and I assume this is
> > > > expecting Excel 12.0 but I guess that is not going to help your current issue!
> > > >
> > > > John
> > > >
> > > >
> > > > "med" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > When I use the sql server 2005 import and export wizard to import an excel
> > > > > file, I always get "Could not find installable ISAM.(microsoft JET database
> > > > > engine)" error.
> > > > >
> > > > > my operating system is xp with sp2, sql server 2005, office 2003 and office
> > > > > 2007.
> > > > > all of them have latest update (through microsoft update).
> > > > >
> > > > > the excel file type is 97-2003 work sheet.
> > > > >
> > > > > Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
> > > > > MSEXCL40.dll (4.0.8618.0)
> > > > > but I can not find the
> > > > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
> > > > > reinstall the xp sp2. there is
> > > > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
> > > > >
> > > > > Currently, both sql 2005 and access 2003 can not import the data from excel
> > > > > (97-2003 worksheet), but the access 2007 works well.
> > > > >
> > > > > So I have to import the excel file into access 2007 first and then import it
> > > > > into sql 2005.
> > > > >
> > > > > Please tell me what can I do to fix this issue, your help are really
> > > > > appreciated.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >