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.