Using a SQL Server SELECT Statement to Query an Excel Workbook

Occasionally you may find that some of the data you need to reference in a SQL Server query is located outside of the database in 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: 

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.

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.

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.

If you would like to learn more about working with SQL Server why not take a look at our SQL Server Database Querying Courses or email us at info@ptr.co.uk