Click here to Skip to main content
16,019,768 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a ASP.Net project. I like to get excel data to DataTable in C#. I try the below code but the following error is showing "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine"
I installed the Access Database Engine and Runtime. but the error sows again. please help me on this...

What I have tried:

private DataTable Parse(string FilePath)
    {
        string Extension = System.IO.Path.GetExtension(FilePath);
        string conStr = "";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                break;
            case ".xlsx": //Excel 07
                conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                break;
        }
        conStr = String.Format(conStr, FilePath, "YES");
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();

        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();


        return dt;
    }
Posted
Comments
PIEBALDconsult 21-Aug-24 16:37pm    
You can search for the other million times that's been asked.
PIEBALDconsult 21-Aug-24 16:44pm    
Wait, IMEX=2 ? Is that correct?

1 solution

This normally indicates that you are targetting an architecture with your application that the installed driver doesn't match. So, if you were building for x64 (for instance), and you only have the 32 bit OLEDB driver installed, this isn't going to work. The fix you need to perform is to install the appropriate driver (either 64 bit or 32 bit - we can't tell this for you, you are going to have to figure this out for yourself), or build your application to match the architecture of the driver.
 
Share this answer
 
Comments
Aadhi Gobi 21-Aug-24 8:06am    
Hi, Thank you so much. I have a x64 instance, and I installed the x64 bit driver. but its not working. again same error appear.
Pete O'Hanlon 21-Aug-24 8:35am    
So, to be clear, you have a 64 bit machine, with the application compiled for 64 bit, and you have installed the 64 bit driver. One way to test this is to run this command in PowerShell:
(New-Object system.data.oledb.oledbenumerator).GetElements() | select SOURCES_NAME, SOURCES_DESCRIPTION
On my current machine, this gives me the following:
SOURCES_NAME SOURCES_DESCRIPTION
------------ -------------------
SQLOLEDB Microsoft OLE DB Provider for SQL Server
MSDataShape MSDataShape
Microsoft.ACE.OLEDB.12.0 Microsoft Office 12.0 Access Database Engine OLE DB Provider
Microsoft.ACE.OLEDB.16.0 Microsoft Office 16.0 Access Database Engine OLE DB Provider
ADsDSOObject OLE DB Provider for Microsoft Directory Services
Windows Search Data Source Microsoft OLE DB Provider for Search
MSDASQL Microsoft OLE DB Provider for ODBC Drivers
MSDASQL Enumerator Microsoft OLE DB Enumerator for ODBC Drivers
SQLOLEDB Enumerator Microsoft OLE DB Enumerator for SQL Server
MSDAOSP Microsoft OLE DB Simple Provider

From that, I can see the 64 bit driver is installed. What do you get when you run this check?
OriginalGriff 21-Aug-24 9:14am    
Pete, I get the same here (with a couple of additions), but which part of that says 64 bit?
Pete O'Hanlon 21-Aug-24 9:47am    
It's the version of the OS you are running on. So, if you run powershell on a 64 bit machine, and run that command, it's telling you what's installed for that architecture.
OriginalGriff 21-Aug-24 11:32am    
AH! That makes sense now. :thumbsup:

I don't do much with powershell (or BAT files)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900