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

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

Advertisements

Windows 7 (64 bit): “The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered in the local machine”.

When I try to import Excel 2010.xlsx file into my local SQL Server R2 database from my Windows 7 machine (64 bit),  I ran into this error message: “The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered in the local machine”.

I found a  solution from a quick search:  to install Microsoft Access Database Engine 2010 Redistributable  from this link: http://www.microsoft.com/en-us/download/details.aspx?id=13255

The catch point is to install the 32 bit instead of 64 bit.

 


Import Excel Data Into SQL Server (Skip Rows)

I have included two snippets:
The first one is to use SqlBulkCopy:

protected void Button1_Click(object sender, EventArgs e) 
    {             // Connection String to Excel Workbook 
        string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\property.xls;Extended Properties=""Excel 8.0;HDR=YES;"""; 
 
        // Create Connection to Excel Workbook 
        using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(excelConnectionString)) 
        { 
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(" Select CountyCode,PropertyAppraiserName FROM [county$]", connection); 
 
            connection.Open(); 
 
            // Create DbDataReader to Data Worksheet 
            using (System.Data.OleDb.OleDbDataReader dr = command.ExecuteReader()) 
            { 
                // SQL Server Connection String 
                string sqlConnectionString = @"Data Source=myServer;Initial Catalog=mytest1;Integrated Security=True"; 
 
                // Bulk Copy to SQL Server 
                using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(sqlConnectionString)) 
                { 
                    
                    bulkCopy.DestinationTableName = "dbo.PropertyAppraiser"; 
 
 
                    //Define ColumnMappings: source –--destination  
 
                    bulkCopy.ColumnMappings.Add("CountyCode", "DesCountyCode"); 
                    bulkCopy.ColumnMappings.Add("PropertyAppraiserName", "DesPropertyAppraiserName"); 
                   
 
 
                    DataTable mydt = new DataTable(); 
 
                    mydt.Load(dr); 
 
                    IEnumerable<DataRow> RowsSkip5 = mydt.AsEnumerable().Skip(5); 
 
 
 
                    bulkCopy.WriteToServer(RowsSkip5.ToArray()); 
 
                    //bulkCopy.WriteToServer(dr); 
 
 
                } 
            } 
        } 
    }

The second one is to use OPENROWSET:


  protected void Button2_Click(object sender, EventArgs e) 
    { 
 
        // SQL Server Connection String  
        string sqlConnectionString = @"Data Source=myInstance;Initial Catalog=myDBName;Integrated Security=True"; 
        SqlConnection conn = new SqlConnection(sqlConnectionString); 
        string strSQL = @"INSERT INTO dbo.PropertyAppraiser (DesCountyCode, DesPropertyAppraiserName) SELECT F1, F2 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\property.xls;HDR=no', 'SELECT * FROM [county$a5:g]')"; 
        SqlCommand cmd = new SqlCommand(strSQL, conn); 
 
        conn.Open(); 
        cmd.ExecuteNonQuery(); 
        conn.Close(); 
 
    }


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);

}

}

}

}


About green triangle indicator in Excel cells’ upper-left corner

When I generate excel data report, there are a few result columns with mixed number and text. I define the column as text and the cells include numbers in this column show green triangles at each cell.
If this green triangel bothers you, you can always turn it off from Tools>> Options>>Error checking tab:  Store number stored as text. and click OK. The green triangles will disppear for the column with mixed number and text values.

Import from Excel to SQL Server through BulkCopy with ColumnMappings

 

Here is a code snippet I copied from a question I answered at ASP.NET forum:

If the source columns are not matching with destination in position and/or name, you need to define ColumnMappings during the bulkcopy.

protected void Page_Load(object sender, EventArgs e)
   {
       // Connection String to Excel Workbook
       string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\property.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

       // Create Connection to Excel Workbook
       using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(excelConnectionString))
       {
           System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand
           ("Select CountyCode,PropertyAppraiserName FROM [county$]", connection);

           connection.Open();

           // Create DbDataReader to Data Worksheet
           using (System.Data.OleDb.OleDbDataReader dr = command.ExecuteReader())
           {
               // SQL Server Connection String
               string sqlConnectionString = "Data Source=.; Database=test_for_forum;Integrated Security=True";

               // Bulk Copy to SQL Server
               using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(sqlConnectionString))
               {
                   bulkCopy.DestinationTableName = "tblPropertyAppraiser";

 

                   //Define ColumnMappings: source –destination

                   bulkCopy.ColumnMappings.Add("CountyCode", "DesCountyCode");
                   bulkCopy.ColumnMappings.Add("DesPropertyAppraiserName", "PropertyAppraiserName");
                  

 

bulkCopy.WriteToServer(dr);
               }
           }
       }
   }

http://forums.asp.net/t/1405240.aspx


IMEX=1 revisit and TypeGuessRows setting change to 0 (watch for performance)

When we import mixed type data in a column from excel to SQL server, we may have issues with losing some data. We can set IMEX=1 to allow the incoming column allow mixed data types based on the Registry setting ImportMixedTypes to Text. However, there is a default setting for Excel to Guess Data Type of a column, the default value is 8.(The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype.) We can change this value to 0 to force Excel to check all values in the column to choose the data type for the column.

Here is the registry setting we need to check:

Type Regedit from Run… command:

Under entries

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

Change:
TypeGuessRows: 0    —-Check all column values before choosing the appropriate data type.ImportMixedTypes: Text         —-import mixed-type columns as text.

Connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\Book1.xls;Extended Properties="EXCEL 8.0;IMEX=1;HDR=YES";

–Or:

SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;IMEX=1;HDR=YES;Database=C:\data\Book1.xls;’, ‘select * from [Sheet1$]’)

We are using IMEX=1, the Import Mode, in the connection string  to apply the registry setting.

PS:

The possible settings of IMEX are: 0,1,2.

(0 is Export mode 1 is Import mode 2 is Linked mode (full update capabilities))

 

 

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

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