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

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a database and Azure specialist who works 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 who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at a clinic that treats low income and uninsured patients. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog

Syndication

Privacy Statement