Excel as a linked server

  • Article

Been a little light on technical content and since I had to figure this out for my "day job"...thought I'd post it.

I have a need to load a bunch of Excel 2007 spreadsheets into a database as the first step in moving from a distributed Excel centric process to a centralized SQL Server process. In other words...the Excel spreadsheets are out of control ... > 600 of them.

So I went digging and found this KB article on How to use Excel with SQL Server linked servers and distributed queries. Nice...but I'm trying to import Excel 2007 which doesn't use the JET database provider, but instead uses the provider you can get separately here..."Microsoft.ACE.OLEDB.12.0".

Adding a workbook as a linked server looks like this:

 exec sp_addLinkedServer @server='XLLink', 
                    @srvproduct='ACE 12.0', 
                    @provider='Microsoft.ACE.OLEDB.12.0', 
                    @datasrc='C:\spreadsheet.xlsm',
                    @provstr='Excel 12.0;HDR=No';

Now if you want to read an entire worksheet you simply do this:

 select * from XLLink...[Sheet1$]

If you want to read a single cell or range of cells, you can do a pass through query:

 select * from openquery(XLLink, 'SELECT * FROM [Sheet1$A1:A1]')

Better yet, as is our case, we are reading Excel named ranges (where ProductName is a named range):

 select * from XLLink...ProductName

One important note on the provider string is that I included the "HDR=No" section - which means the first row of our returned cells does NOT contain column names. If you decide not to go the linked server route, then you can simply do it like this:

 SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:spreadsheet.xlsx;HDR=No','Select * from [Sheet1$]')

Just thought I'd share...