SQL Server - Querying an Excel Workbook From an SQL Query

Using a SQL Server SELECT Statement to Query an Excel Workbook

In this article we look at how you can query an Excel workbook as if it were a table in a SQL Server Database.

The SQL Server OPENROWSET function can be used to connect to a variety of data sources by means of a data provider: 

OPENROWSET   
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'   
   | 'provider_string' }   
   , {   [ catalog. ] [ schema. ] object   
       | 'query'   
     }   
   | BULK 'data_file' ,   
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]  
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }  
} )   

<bulk_options> ::=  
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]   
   [ , DATASOURCE = 'data_source_name' ]
   [ , ERRORFILE = 'file_name' ]  
   [ , ERRORFILE_DATASOURCE = 'data_source_name' ]   
   [ , FIRSTROW = first_row ]   
   [ , LASTROW = last_row ]   
   [ , MAXERRORS = maximum_errors ]   
   [ , ROWS_PER_BATCH = rows_per_batch ]  
   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ] ]

   -- bulk_options related to input file format
   [ , FORMAT = 'CSV' ]
   [ , FIELDQUOTE = 'quote_characters']
   [ , FORMATFILE = 'format_file_path' ]   

Supported Data Providers include:

• SQLNCLI for accessing remote SQL Server Databases
• Microsoft.Jet.OLEDB.4.0 for accessing Microsoft Access Databases and Excel Workbooks
• Microsoft.ACE.OLEDB.12.0 for accessing Microsoft Access Databases and Excel Workbooks

The older JET data provider is only available for 32 bit platforms, but ACE is available for 64 bit platforms as well.

ACE is an abbreviation for Access Connectivity Engine, but is now generally known as the Access Database Engine. ACE is backewardly compatible with JET so can be used to query older versions of Access databases and Excel workbooks.

ACE is going to be used in this article as it is the more modern and flexible implementation.

We will us a sample Workbook that contains some Order Records:

Obviously we could import this data into a SQL Server database, but if the data is generated by a Legacy system that is limited in export capabilities, or a person is manually constructing the data set, and it frequently changes and is replaced it may be more practical to simply query the data in place.

As already mentioned the ACE library can be used to query the data in an Excel Workbook, using the OPENROWSET function.

The following example retrieves all columns for all records in an Excel workbook called Northwind Orders.xls, on a worksheet called Orders.

USE Northwind
GO
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=e:\TestZone\Northwind Orders.xls;HDR=YES',Orders$)

The arguments to the OPENROWSET function to connect to the Excel Worksheet data are:

• Data Provider: ACE (Microsoft.ACE.OLEDB.12.0)
• Workbook version: Excel 2007 format (Excel 12.0)
• Workbook: e:\TestZone\Northwind Orders.xls
• Woksheet Range Name: Orders$

The results from this query are as follows:

Joining OPENROWSET results sets to Database Tables with JOINs

The OPENROWSET can be used in a JOIN clause.

--Join to other tables
SELECT wb.[Order ID], wb.Customer, wb.Employee, wb.[Order Date], od.ProductID, od.Quantity 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=e:\TestZone\Northwind Orders.xls;HDR=YES',Orders$) AS wb
--Orders$ is a named range for a worksheet called Orders. This could be any named range in the workbook.
	JOIN [Order Details] As od ON wb.[Order ID] = od.OrderID

This query pulls four columns from the workbook and one from the table called [Order Details] in a database called Northwind. Mote the alias of wb created for the “table” created by the OPENROWSET query. This is not required, but makes it easier to reference and identify columns when multiple tables are in the query.

I hope you have found this helpful. Do share this article on Social Media if you like it.