HOW TO: FIX ERROR – “the ‘microsoft.ace.oledb.12.0’ provider is not registered on the local machine”

Posted: November 26, 2012 | Author: | Filed under: Excel 2007, Excel 2010, SQL Server | Leave a comment

http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/1d5c04c7-157f-4955-a14b-41d912d50a64

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

Posted: January 31, 2010 | Author: | Filed under: Excel 2007 | 1 Comment
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);

}

}

}

}

Get data from SQL Server with data format and column headers

Posted: March 4, 2008 | Author: | Filed under: Excel 2007 | Leave a comment
From Excel 2007 Data Tab>>From Other Sources>>From SQL Server(first one)>>type server name, I tried (local) and a named remote instance and both worked>>Choose the database of interest>>choose table or view of interest>>click next and add some optional information here>>Click finish and click OK from the new Import Data window>>Done.