Bulkcopy Excel 2007 data to SQL Server: Issue— Could not find installable ISAM


If you have installed Office 2007 SP2(or SP1) for the machine, the error is likely casused by the connection string.
You can find all kind of connection string from connectionstring.com. But you need to use them with a right syntax in your code.
Here is sample with the right syntax in C#:

string OLEDBConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\datatest\ImportUserFile.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES""";

 
 
I also include a working ASP.NET sample code for how to:

protected void myButton_Click(object sender, EventArgs e)

{

string OLEDBConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|ImportUserFile.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES""";

// string OLEDBConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\datatest\ImportUserFile.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES""";

using (System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(OLEDBConnectionString))

{

OleDbCommand command = new OleDbCommand("SELECT UserID, Password, FirstName, LastName, Email, Address, PostalCode, MobilePhone, HomeNumber, DD, MM, YYYY, Role FROM [ImportUserFile$]", myConnection);

myConnection.Open();

using (System.Data.OleDb.OleDbDataReader dr = command.ExecuteReader())

{

string sqlConnectionString = ConfigurationManager.ConnectionStrings["test_for_forumConnectionString"].ToString();

// Bulk Copy to SQL Server

using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(sqlConnectionString))

{

bulkCopy.DestinationTableName =

"dbo.Account";

//Define ColumnMappings: source(Excel) –destination(DB Table column)

bulkCopy.ColumnMappings.Add(

"UserID", "UserID");

bulkCopy.ColumnMappings.Add(

"Password", "Password");

bulkCopy.ColumnMappings.Add(

"FirstName", "FirstName");

bulkCopy.ColumnMappings.Add(

"LastName", "LastName");

bulkCopy.ColumnMappings.Add(

"Email", "Email");

bulkCopy.ColumnMappings.Add(

"Address", "Address");

bulkCopy.ColumnMappings.Add(

"PostalCode", "PostalCode");

bulkCopy.ColumnMappings.Add(

"MobilePhone", "MobilePhone");

bulkCopy.ColumnMappings.Add(

"HomeNumber", "HomeNumber");

bulkCopy.ColumnMappings.Add(

"DD", "DD");

bulkCopy.ColumnMappings.Add(

"MM", "MM");

bulkCopy.ColumnMappings.Add(

"YYYY", "YYYY");

bulkCopy.ColumnMappings.Add(

"Role", "Role");

bulkCopy.WriteToServer(dr);

}

}

}

}

Advertisements

One Comment on “Bulkcopy Excel 2007 data to SQL Server: Issue— Could not find installable ISAM”

  1. geekonweb says:

    Thanks a lot .. it worked . I was struggling for hours on it.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s