"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine" Error in importing process of xlsx to a sql server

Question

I have a 64 bit windows 7 and SQLServer 2008 R2 (64 bit)

I follow the instructions that are here to import excel file to sql server but in figure3 section of that post when I try to access excel file and when I click next this error make me stop:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

I search the web i knew that I must install AccessDatabaseEngine_x64. but when I install it I have a same problem

Can you please help me what to do?

Slava · Accepted Answer · 2019-03-11T10:52:40.180

234

Install the following to resolve your error.

2007 Office System Driver: Data Connectivity Components

AccessDatabaseEngine.exe (25.3 MB)

This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2007 (*.mdb and .accdb) files and Microsoft Office Excel 2007 (.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server.

edited Mar 11 '19 at 10:52

answered May 18 '12 at 15:50

Slava

3,445
1
20
16

this problem shows up for me after changing my SSIS 64 bit execution turned on.also note that the instalation resolved the issue right after finishing and does not need any restarting – Iman Mar 23 '14 at 10:21
11

This occurred for me when I had a XLSX formatted file, downgraded the file to XLS and it worked for me without installing anything extra. – Mark Boltuc Nov 05 '14 at 13:44
8

The "2007" on this is terribly deceiving. I'm running SSMS 2014, Office 64 Bit, Windows 8.1 64 Bit and figured this was sorely out of date, but this "2007" Office component did the trick. Thanks! – russds Jun 02 '15 at 19:41
1

Fixed my 64 bit office on Windows 7. – TheTechGuy Jul 14 '16 at 16:02
2

The link is now giving a 404. Try this https://www.microsoft.com/en-us/download/details.aspx?id=54920 – Vivek Ayer Feb 11 '19 at 09:22
It worked (even in November 2019) with SQL Server 2017 on Windows Server 2016. Thanks! – Abdullah Ilgaz Nov 25 '19 at 12:53
Yes it worked with SQL Server 2017 on Windows 10. Thanks, appreciated. – ppel123 May 08 '20 at 08:44
It worked with SSMS 2012 64bit & windows 10. Thanks :) – Amar Gadekar May 10 '20 at 20:18

score 78 · Answer 2 · answered Oct 15 '13 at 22:36

78

Excel 2010 driver is 64 bit, while the default SSMS import export wizard is 32 therefore the error message.

You can import using the Import Export Data (64 bit) tool. ("C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe") notice the path is not Program Files x86.

answered Oct 15 '13 at 22:36

Ozer

781
5
3

3

+1. I was about the install extra component which is suggested in other answers. But then why to install when everything is already in my machine. – Pradeep Nov 19 '13 at 05:52
6

I love this answer but I'd like to know whether their is a way to change the default in SSMS. – Dave.Gugg Mar 16 '15 at 16:36
This is the correct answer to solve the OP's question. The others are work-arounds, but don't solve the given problem – Reversed Engineer Jan 22 '16 at 08:24
2

I had to open the 64-bit Importer by clicking Start and searching for "Import and Export" and selecting the 64-bit tool. – blizz Apr 27 '17 at 16:21
5

This solved my problem. To change the default to the working one, just replace the one in the x86 folder `C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe` with the working one `C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe`. If you want to be safe, rename the old one first instead of replace him, so you can go back if anything goes wrong. – Magnetron Jan 05 '18 at 17:58
3

I found the 64-bit version of the import wizard but unfortunately, unlike the 32-bit version on my machine, "Excel" is not listed as a possible data source in the drop-down. – Bob Sammers Nov 14 '18 at 16:15
Also note the 64-bit version of the Data Import and Export Wizard isn't installed https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard?view=sql-server-2017#prerequisite---is-the-wizard-installed-on-your-computer – Eric Labashosky Feb 08 '19 at 15:48
Only this one helped! Search "Import and Export Data (64-bit)" and run it. Here you go... no longer any issue with providers. I installed all version previously.. but yeah, the main thing was SSMS run x32 by default (I hate MS one more time for that lol). – hastrb Sep 09 '19 at 13:20
For me , combination of https://blog.sqlauthority.com/2015/06/24/sql-server-fix-export-error-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/ and https://stackoverflow.com/questions/9943065/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/19392279#comment83214099_19392279 worked flawlessly – user216652 May 07 '20 at 09:10
I suppose this should be elected as the correct answer! Anyway, I found my 64bit version in `C:\Program Files\Microsoft SQL Server\150\DTS\Binn`. Thanks Ozer. – Amirali Sam Mar 06 '23 at 09:00
Correct answer - no need to install or setup external things. if you try to open path directly you might face issue due to other versions of SQL server – Saurabh Solanki Jul 28 '23 at 07:16

score 31 · Answer 3 · answered May 17 '13 at 11:23

31

Save it as a CSV file and import it as a flat source file.

answered May 17 '13 at 11:23

Bob

335
3
2

No need to install anything extra. This should have way more upvotes. – Austin May 20 '20 at 22:05
Doesn't work for me because it when scanning it it says it can't find the header delimiter despite scanning a shit ton of bytes. My excel file has multiple sheets also. – Bob the Builder Aug 15 '22 at 17:28

score 18 · Answer 4 · edited Feb 11 '20 at 14:22

18

This is the only install that resolved the issue for me.

SQL 2008 r2 w/ office 2010 64bit: "2007 Office System Driver: Data Connectivity Components"

edited Feb 11 '20 at 14:22

BigM

678
1
17
28

answered Jul 16 '12 at 00:53

Adam H

181
1
2

1

Indeed, this 32bit installer also resolved it for me even though I'm using 64 bit versions of OS and SQL, Office is 32bit though. – PedroC88 Jul 02 '13 at 23:41
Broken link, please share the new one. – Venkat Oct 11 '19 at 05:16

score 1 · Answer 5 · answered Jul 17 '20 at 03:03

Currently, Microsoft don't provide download option for '2007 Office System Driver: Data Connectivity Components' and click on first answer for '2007 Office System Driver: Data Connectivity Components' redirect to Cnet where getting download link creates confusion.

That's why who use SQL Server 2014 and latest version of SQL Server in Windows 10 click on below link for download this component which resolve your problem : - Microsoft Access Database Engine 2010

Happy Coding!

score 1 · Answer 6 · answered Dec 03 '20 at 11:27

1

I had the same problem. SSMS launches the 32bit version of the import and export wizard which has this issue. Try launching the 64bit version application and it should work fine.

answered Dec 03 '20 at 11:27

dotcoder

2,828
10
34
50

score 0 · Answer 7 · answered Jul 06 '22 at 05:13

0

if you have xlsx file save it in workbook2003-2007.xls file and try. hope it will work

answered Jul 06 '22 at 05:13

Rohit Thakur

11
2

score -1 · Answer 8 · edited Jun 20 '20 at 09:12

-1

I had no luck until I installed the 2010 version link here: https://www.microsoft.com/en-us/download/details.aspx?id=13255

I tried installing the 32 bit version, it still errored, so I uninstalled it and installed the 64 bit version and it started working.

edited Jun 20 '20 at 09:12

Community

1
1

answered May 01 '19 at 20:16

user890332

1,315
15
15

"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine" Error in importing process of xlsx to a sql server

8 Answers8

Linked