Using SQL to read an Excel file into SQL Server

There are two obvious ways to get data from Excel into SQL Server:

• Use the Import/Export Wizard in SQL Server Management Studio (SSMS)
• Build up the individual VALUE strings in Excel (the TEXTJOIN function in Excel is very handy for this), and then copy/paste into SSMS (you have to write the INSERT part of the statement yourself)

I use both methods all the time; the second is quicker, but only works for up to a few thousand rows. Currently, I’m working on a spreadsheet that I’m updating frequently, and I want to be able hit ‘execute’ (‘go’) on some SQL code that will do the import, followed by some fairly involved post-processing — all with minimal intervention on my part.

My use case

As I mentioned in my previous post, I’m learning Welsh. So, in an effort to combine two of my interests, in my spare time I’m (very slowly) building a Windows app to help with my learning. It is, of course, powered by a SQL Server back end. Hence I’ve created a bunch of Excel spreadsheets with lists of nouns, verbs, etc., and I want to import them into my SQL Server database, as painlessly as possible. I prefer to develop my databases by dropping absolutely everything, then re-creating all objects and data from scratch every time I make any sort of significant change. I use SQL scripts to load code in from files, and I can add steps at the end to load data in from spreadsheets.

What follows is how I got the Excel file import working — really, I’m just showing you what error messages I encountered along the way, and how I got past each one, with the help of Google, Stack Overflow, etc.

Important caveat: This is all running on my local computer, so I have full control over security and permissions. I wouldn’t recommend doing any of this on a production server without research and testing!

Getting started

My translation data is in the spreadsheet Welsh words.xlsx, on my I: drive. The first sheet is named ‘nouns’. Here is the SQL statement I want to run:

SELECT *
FROM OPENROWSET
(
	'Microsoft.ACE.OLEDB.12.0',
	'Excel 12.0; Database=I:\Welsh words.xlsx; HDR=YES; IMEX=1',
	'SELECT * FROM [nouns]'
)
GO

OPENROWSET is used “to access remote data from an OLE DB data source”. You can find out more about it here (at microsoft.com).

Microsoft.ACE.OLEDB.12.0 is an ‘OLE DB Provider’ … and honestly, no, I don’t really know what that means, other than it’s some clever code that lets one thing talk to another!

When I execute this code in SQL Server Management Studio (SSMS), I get my first error message:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component
'Ad Hoc Distributed Queries' because this component is turned off as part of the
security configuration for this server. A system administrator can enable the use
of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about
enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries'
in SQL Server Books Online.

The important words are ‘security configuration’ — I’ve come across this before, e.g. when I wanted to use the MSXML2.ServerXMLHTTP component, see the post “‘You Can’t Always Get What You Want’ – old tech to the rescue!”. We need to enable ‘Ad Hoc Distributed Queries’ in the configuration, like this:

EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
-- Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1.
   Run the RECONFIGURE statement to install.

RECONFIGURE
GO
-- Commands completed successfully.

I run my SELECT statement again, and get a different error:

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0"
for linked server "(null)".

According to this article at MSSQLTips, I need to set the following configuration option in SQL Server:

EXEC master.[sys].[sp_MSset_oledb_prop] N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
-- Commands completed successfully.

I run my SELECT statement again, and get:

The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded
in-process on a 64-bit SQL Server.

So the provider I’m trying to use to access the Excel file is 32-bit, but I need the 64-bit version.

Getting the right provider

In a reply to this Stack Overflow post, “Microsoft.ACE.OLEDB.12.0” cannot be loaded in-process on a 64-bit SQL Server, there’s a link to Microsoft to download the file I need: Microsoft Access Database Engine 2010 Redistributable.

The file I want is AccessDatabaseEngine_X64.exe, and it’s 27.3 MB in size:

The reply says to install the code in passive mode to “prevent the installation from failing if you already have the 32-bit version installed”. Therefore when it finishes downloading, I open up a Command Prompt as administrator, and run:

"C:\Users\Pete\Downloads\AccessDatabaseEngine_X64.exe" /passive /quiet

(Nothing much happens, the mouse pointer maybe shimmies for a few seconds)

Ok, I’ve got the correct provider installed; my SELECT query should work, right?

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" 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 "(null)".

The solution to this is in another Stack Overflow post: I need to be running SSMS as administrator, so I shut SSMS down (not the server, just the management studio) and re-start SSMS as administrator.

Nearly there

I run my SELECT statement again…

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message
"The Microsoft Access database engine could not find the object 'nouns'. Make sure the object
exists and that you spell its name and the path name correctly. If 'nouns' is not a local
object, check your network connection or contact the server administrator.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0"
for linked server "(null)".

A-ha, we’re getting somewhere! The complaint is that it “could not find the object ‘nouns'”, the implication being that it could find other objects? In fact, it’s my fault: my query is wrong, I need a dollar sign ($) after the sheet name:

SELECT *
FROM OPENROWSET
(
	'Microsoft.ACE.OLEDB.12.0',
	'Excel 12.0; Database=I:\Welsh words.xlsx; HDR=YES; IMEX=1',
	'SELECT * FROM [nouns$]'
)
GO

I run this, et voilà, my data:

(Actually, the above shows the data from the first 12 columns, which you can select by changing [nouns$] to [nouns$A:L].)

And that’s it, I can now finally import data from my Excel spreadsheets, and I can add these imports as a step in my database rebuild code.

---

Some final notes:

• None of the above works if the Excel file is open; it has to be closed, or you’ll get the error “Cannot initialize the data source object”.
• I thought there was a way of using OPENROWSET to return the list of sheet names, but I can’t find it now. That would be very useful!
• You can also connect Excel to SQL Server using Linked Server functionality, but I’ve not tried it — please do let me know if you have.