How Simple Is It to Move Data from Excel to SQL Server?

What could be simpler than to get data from an Excel table in SQL Server?

There are many ways to accomplish this task. You can utilize Integration Services (former DTS) or import and export wizard, which is the same under the hood. Another way is to create a simple ADO.NET app. You can use the Linked Server mechanism, which allows you to see any ODBC / OLE DB-reachable object in the form of a table (a collection of tables) or the result of an ad hoc query.

It had been until the 64-bit architecture stopped being something from the hi-end class and came to the developer and user laptops. A typical user is unlikely to install a database server, while it is normal for a developer to have a 64-bit SQL Server on a 64-bit machine with 32-bit MS Office. However, in this case, using Excel or Access with SQL Server linked servers may be a challenge because drivers are 32-bit. There are no providers available for MS Office, although I have one installed on my PC.

Thus, an attempt to use Excel or Access with SQL Server linked servers, as described in the documentation, will cause the following error:

Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB provider «Microsoft.ACE.OLEDB.12.0» for linked server…

So, what to do?

Install 64-bit provider, which is available for download at Microsoft Access Database Engine 2010 Redistributable. We get the following error during the installation process:

If 32-bit Office were not installed on the computer, then you would not get this warning.

Should I choose between the 64-bit or 32-bit version of MS Office and 64-bit or 32-bit version of SQL Server? Uninstalling one version and installing another is not a flexible solution. Moreover, it’s not always possible. Let’s have a look at the question a person has raised on MSDN:

The MS Access database is a production environment at the customers site, and it is not an option for me to de-install it and install a 64 bits version. It is also not an option to de-install the existing SQL Server 64 bit and install the 32 bits version, of course. An additional installation of a SQL Server 32 bits, only providing the link to MS Access would be the only option then. I find this quite horrible. 

I do not understand this part.

Since there is no 64-bit Jet, you need download and install the 64-bit version of «Microsoft Access Database Engine 2010 Redistributable». — Thanks for your answer, but I when I install the AccessDatabaseEngine_X64.exe I get the following error (picture 1) — Please refer to this KB article: support.microsoft.com/kb/2269468 — The article in the link in your last message tells to de-install all Office 32 bits products. That makes no sense to me, because the goal is to connect to the 32 bits MS Access database… — You need to install the 64-bit version of “Microsoft ACE OLEDB Provider” to connect to an Access file (32-bit or 64-bit) from SQL Server. — But I tried to install the 64 bits driver, but it is giving me an installation error… — The previous KB article explains the cause and provide the solution, you can refer to it.— You are guiding me in circles.

Well, the issue is to make the 64-bit version of Microsoft ACE OLEDB Provider work with the 32-bit Office. Why it is not possible to do this directly is a big question. There are a lot of information on this topic:

The fact that they cannot forces us to compile two different versions of our application; one specifically for x86 platform and one for x64 platform. Then we have to somehow determine which version of the ACE driver is installed so we know which version (32 or 64 bit) of our application to install. Life would be much simplier if we could just compile our app for the default AnyCPU and then both 32 and 64 dataproviders were allowed to be installed… Why can’t 32 and 64 bit ACE coexist? — It’s based upon the fact that Microsoft does not support the side by side install of 32 and 64-bit Microsoft Office 2010 or their dependent components… — I’m sorry, but that’s not a very helpful answer to the question. Q: Why can’t the 32 and 64 bit versions of the Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine.exe and AccessDatabaseEngine_X64.exe) both be installed on the same machine? A: Microsoft does not support the side by side install of 32 and 64-bit Microsoft Office 2010. That’s not answering, that’s just repeating the question in other words. Q: Why can’t I do this? A: Because we don’t support it. Q: Why can’t you support it? A: Because we don’t support it.

As a workaround, I used AccessDatabaseEngine_X64.exe with the /passive key that allows 64-bit version of the provider coexist with the 32-bit Office version. It should be noted that Microsoft does not support this method officially. In addition, there are warnings regarding possible issues with the compatibility.

When I installed the driver AccessDatabaseEngine_x64.exe… MS Office Pro Plus starts a MS Configuration Process that can take up to a minute or two before a document or Office program opens. If office 2010 32 bit is installed and if 64-bit ace is installed with /passive, then **every** time you run Access 2010, you get an automatic installation routine that resets to the 32-bit drivers for office.

In my case (Office 2013 Pro Plus, SQL Server 2012 Developer Ed., Windows 8.1), there were no visible issues (knock on wood). Still, I would like to add that it is as is and without any liabilities.

Download AccessDatabaseEngine_x64.exe, create System Restore Point (it seems to be created automatically when updating Office components) and run from the command line. Here are the possible keys:

Unlike the picture 2, the installation is run

Then, click Refresh on the Providers shortcut menu:

I created a simple xslx file very quickly:

In SQL Server, create a linked server on this Excel:

if exists (select 1 from sys.servers where name = 'XlsLnkSrv') exec sp_dropserver 
@server = 'XlsLnkSrv', @droplogins = 'droplogins'

exec sp_addlinkedserver @server = 'XlsLnkSrv', @srvproduct = 'ACE 12.0', 
@provider = 'Microsoft.ACE.OLEDB.12.0', @datasrc = 'C:\Temp\Sample.xlsx', 
@provstr = 'Excel 12.0; HDR=Yes'

Script 1

Read:

select * from openquery (XlsLnkSrv, 'Select * from [Sheet1$]')

Script 2

Is it successful? No!

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider «Microsoft.ACE.OLEDB.12.0» for linked server «XlsLnkSrv» reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider «Microsoft.ACE.OLEDB.12.0» for linked server «XlsLnkSrv».

Still, it is successful!

Likewise,

select * from openrowset('Microsoft.ACE.OLEDB.12.0',  
'Excel 12.0;Database=C:\Temp\Sample.xlsx;HDR=Yes', 'Select * from [Sheet1$]')

At first, we need to do the following:

exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure

Script 3

In conclusion, I would like to note that the 64-bit provider replaced the 32-bit one. If we create a connection for Sample.xslsx in Data Flow of DTS package, we will get an initialization failure of Microsoft Office 12.0 Access Database Engine OLE DB Provider, which is logically correct, as devenv.exe is 32-bit, while the provider is 64-bit. To restore the behavior, it is necessary to restore the 32-bit Office provider (here). However, in this case, we cannot use the linked server. To make the linked server work, go to the beginning of the article.