Working with Excel files can always be of great interest. I have not seen a reporting solution in the world that doesn’t give an option to export to excel. If you are talking about two separate solutions and you want to export and import, then such options can be of great use. In this blog, I was trying to import data from Excel sheet to SQL Instance by using Import Wizard available in SQL Server Management Studio. I am sure all of us have had this requirement once in a while and would have surely tried to get this working.
After selecting the option, I selected Data Source as “Microsoft Excel” and selected file name which I wanted to import. Excel file was created with latest version of excel so I selected “Microsoft Excel 2007”.
As soon as I hit next, I got below error
TITLE: SQL Server Import and Export Wizard
The operation could not be completed.
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
I checked management studio and as the message says, I didn’t see the provider as we can see below. We can also use below query to find the details of providers installed on the machine
Few blogs have mentioned to download (2007 Office System Driver: Data Connectivity Components) but that didn’t work. So I tried installing http://www.microsoft.com/en-us/download/details.aspx?id=13255 (Microsoft Access Database Engine 2010 Redistributable) but it also failed because I am using 32 bit version of office on my 64 bit PC.
Note: If you are running 32-bit version of SQL then your issue will be solved by installing above. In my PC, I have to take below approach.
- Uninstall 32-bit version of Microsoft Office.
- Install the 64-bit version of MS Office (Make sure setup is run from “x64” folder and run setup.exe in order to install the 64 bit of MS Office).
- Install the 64-bit version “Microsoft.ACE.OLEDB.12.0” (download AccessDatabaseEngine_x64.exe from second link provided earlier).
Here is the location of DLL “ACEOLEDB.DLL”
- On a 32-bit operating system
- C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
- On a 64-bit operating system
- If 32-bit office is installed then
C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
- If 64-bit office is installed then
C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
Production Server would not have complete office installed so it would not be a big deal to install correct version of driver.
Have you ever encountered this error in your environments? What did you do to solve them? Let me know.
Reference : Pinal Dave (https://blog.sqlauthority.com)
Hi, I have a SQL 2012 64bit version that is causing this error. Will this exe help ?
Hi, I am facing the same issue and then Install the 64 bit ACEOLEDB.DLL as well. But still not able to import the data from excel. Post installation of ACEOLEDB.DLL, xp_enum_oledb_providers does not list the new provider entry
it also worked for me thank you so much.
Hi I installed Access Database Engine 2010 64bit. Microsoft.ACE.OLEDB.12.0 shown up in provider, but this error is still around. Any idea why this may base the case? Thank you.
I installed the 64 bit one because my office version is 64bit.
However, then the right click menu from SSMS ([DataBase]->Tasks->Import Data) was still giving the error. If I manually ran the SQL program to import data that was 64 bit (WindowsMenu -> Microsoft SQL Server 2019 -> SQL Sever 2019 Import and Export Data(64bit) ), then no error. It seems my version of SQL Server has both a 32bit version and 64bit version of the program to Import and Export Data installed.
To get the right click menu to work I uninstalled the 64bit Microsoft.ACE.OLEDB.12.0 and installed the 32bit version (since SSMS is 32bit I assumed it was launching that version). Problem solved on the right click menu (which is the only place I ever use Import and Export data from).
The 32-bit version did it for me as well, using SSMS 2017. Thanks, Alva.
thanks Alva that really helped. just going to start –all programs–sql–import and export was the deal
Hi I have Access Database2019 64bit. and am using visual studio 2019 .Microsoft.ACE.OLEDB.12.0 shown up in provider,
Thanks so far for the analysis, but you’re not solving the problem?
install the 32bit version of access it works
can we install both the 32bit and 64bit version of Access Database Engine 2010 on sql server.
What is User is connecting from SSMS via Citrix then where the package is to be installed in citrix or in sql server machine.
It took forever, but i finally figured out how to import excel files. Save files in excel as CSV, right click your portfolio database, click task, import data. The the import wizard pops up, on the top where is says data source choose at Flat File Source, then browse for you file (make sure you select all files on the bottom right, so your CSV files can be seen), then click next, click next again, under destination choose Microsoft OLE DB Provider for SQL Server, confirm the server name is correct, click next, click your file name if it’s not already, then next, run immediately should already be checked, click next then finish. When the execution is successul, hit close.