Tips and Tricks for data import into SQL Server – Part II

In this post we will import data from .xlsx file into SQL Server.

As with Tips and Tricks for data import into SQL Server – Part I  , you might think this is a trivial task. You are correct !

However, in this post we will pick a case where we do have to take configuration steps w.r.t. the task at hand before we can actually import data.

Our challenge is that we have and 32-bit Microsoft Office 2016 installed on our 64-bit SQL Server and we want to import data from .xlsx file.

Let's start by solving one problem at a time as we start to import data from .xlsx file.

Below is our .xlsx file placed at a local folder.



Step 1: We will try to import using OPENROWSET query

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;   Database=C:\Users\.....\SourceFile.xlsx','select * from [Sheet1$]')

As expected, we will encounter an error.

Step 2: We take a clue from the error itself. To resolve we need to enable 'Ad Hoc Distributed Queries'. Execute the below queries.

Note: Admin rights are needed.

sp_configure 'show advanced options', 1; 

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1; 

RECONFIGURE; 

GO 

Step 3: Let's try to import query again. 

We will face another error. 

We need to install the OLE DB provider. 

Step 4: We can download and install the provider from Microsoft downloads

Note: Admin rights are needed. 

We have 64-bit SQL server, we need to install 64-bit AccessDatabaseEngine. 

Let's try.  Once, it is installed. Run the import query again. Again, we face an error message. 

Although, we have Microsoft.ACE.OLEDB.12.0 provider present, but still we are not able read the data. 

Reason, is we have 32-Bit Microsoft Office installed on our server. 

Step 5: We need to install the 64-bit AccessDatabaseEngine with passive switch. 

We will first uninstall the existing one.

And once, uninstall is successful, we will install the .exe but do not double click yet!

Open cmdline as admin and install the AccessDatabaseEngine with /passive switch. 

Step 6: Once, installation is completed. We are able to read the data. 

That's it ! 

We have imported data from .xlsx file when we had 64-bit SQL server and 32-Bit Microsoft office installed on the server. 

P.S. You should have appropriate rights on the server.