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.
Figure 1. 32-bit Import and Export Wizard can export to Excel if you have the necessary providers.
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.
Figure 3. Error message trying to export to Excel 2016.
Figure 4. Error message trying to export to Excel 2013.
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.
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.
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.
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.