THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

SQL Server 2016 Import and Export Wizard and Excel

SQL Server 2016 is a 64-bit application and it comes with two versions of the Import and Export Wizard. If you launch the Import/Export Wizard from the SSMS, the 32-bit version is invoked. The 64-bit version does not natively provide support for Excel files. There’s a lot of bad, outdated, or complex advice on how to fix the problem. I was able to easily to fix the problem without installing old software on my machine. The version of Office you have affects what providers are available to the Import and Export Wizard. My machine has the 64-bit version of Office installed. In other words, it is lacking in 32-bit drivers.

image

Figure 1. 32-bit Import and Export Wizard can export to Excel if you have the necessary providers.

image

Figure 2. 32-bit Excel export options.

It did not work as you can see because the necessary 32-bit providers were not present as further documented below.

image

Figure 3. Error message trying to export to Excel 2016.

image

Figure 4. Error message trying to export to Excel 2013.

image

Figure 5. Error message trying to export to Excel 2007-2010.

The error messages are listed below so that search engines can find them. Should I add SEO to my resume?

The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)

The 'Microsoft.ACE.OLEDB.15.0' provider is not registered on the local machine. (System.Data)

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

Selecting Microsoft Excel 97-2003 does work, by the way. But remember, my goal is to make the 64-bit provider work. The 64-bit Wizard can process larger files than the 32-bit Wizard, which is why I’m interested in getting the 64-bit version to work. I’m not interested in the 32-bit version. It still doesn’t work.

If you do a default installation of SQL Server 2016, you will find the 32-bit version of the Import and Export Wizard here:

C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\DTSWizard.exe

The 64-bit version is found here:

C:\Program Files\Microsoft SQL Server\130\DTS\Binn\DTSWizard.exe

You might want to make a shortcut to the 64-bit version so that you can easily Access it – pun intended as you will soon see.

The 64-bit version of the Import and Export Wizard does not offer export to any version of Excel.

image

Figure 6. 64-bit version of the Import and Export Wizard does not by default have Excel as an option.

To add Excel to the 64-bit Import and Export Wizard, install the 64-bit version of the Microsoft Access Database Engine 2016 Redistributable. Note: there is also a 32-bit redistributable. Installing it might resolve the problem with the 32-bit version of the Import and Export Wizard. I didn’t try this. Remember, this blog post is about getting the 64-bit version of the Import and Export Wizard to work with Excel files, either as sources or destinations.

image

Figure 7. 64-bit version of the Import and Export Wizard after installing the 2016 Access Database Engine redistributable shows Microsoft Excel as a supported provider.

image

Figure 8. Excel options made available by the 2016 Access Database Engine redistributable.

That was the Customers table from the Northwind sample database, just in case you were wondering. I’m creating a Northwind graph database you’ll see in a future post.

Published Sunday, June 11, 2017 2:35 PM by John Paul Cook
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

did not work said:

still getting the same error

June 14, 2017 12:48 PM
 

Anil said:

Its a excellent article, my work is unlocked. going to x64 import wizard has helped to load data.

July 25, 2017 4:13 PM
 

Todd said:

You are my hero.  I created a SSIS package ~10 years ago that exported to .xls and remembered jumping so many hoops to get there.  I was hoping to update the process to export to 64bit Excel 2016 .xlsx format

There's now finally "SSDT for Visual Studio 2017"

https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

and of course it's only 32 bit.  So I cannot run a SSIS import wizard in a new solution in VS2017 because it fires off the 32bit version of DTSWizard.exe and won't let me pick the 64bit Excel, and there's no 64bit version of DTSWizard included when you install SQL Server Management Studio.

I spend more time configuring Microsoft tools than coding! :D

August 24, 2017 8:44 AM
 

Zack said:

I don't have the full version of SQL server 2016 installed locally (because I have windows 7 and it requires windows 8 +). Is it possible to download the 64bit version of the Import/Export Wizard and use on widnows 7?

September 26, 2017 7:21 AM
 

tawerg said:

John my problem is the same as described by you. Unfortunatelly it solution don't work when we have MS Office/Access 2016. I can't install Microsoft Access Database Engine 2016 Redistributable and I still have a problem, I don't have driver and wizzard don't work.

December 7, 2017 4:31 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog

Syndication

Archives

Privacy Statement