SQL Server: How to Ad-Hoc Query an Excel Spreadsheet using OpenRowset

Running an ad-hoc query from SQL Server SSMS to an Excel spreadsheet is amazingly straightforward using the OpenRowset command.

Here’s how it’s done…

For starters, copy the Excel spreadsheet to a location where the installed version of SQL Server can access it. This is necessary since your query in SSMS (SQL Server Management Studio) will not execute from your local machine. The query will be passed to SQL Server, which will then attempt to locate the spreadsheet as an OLE DB data source in order to run your query on it.

So make sure that the Excel spreadsheet is located either on the computer SQL Server is installed on, or on a network share which the SQL Server account has access to.

Step two is to open SSMS, connect to SQL Server, and open a query window in SSMS where you can run your query from.

Step three is to actually write and run the OpenRowset query.

In the example below I am running a simple query against an Excel 2003 spreadsheet called test.xls that is located directly on SQL Server’s c:\ drive. The worksheet that I am running the query against is the default named worksheet called Sheet1. Please note that you must place a $ sign after the name of the worksheet in your query.

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\test.xls;HDR=No',
                'SELECT * FROM [Sheet1$]') b

You can tell SQL Server if you want it to use the first row in your worksheet as data, or if it should be considered the column headings. In my example above the command HDR=No tells SQL Server to use the first row as data, and it will set the column header to the default F1, F2,…FN. Simply change the command to HDR=Yes if you want SQL Server to get it’s column heading names from your first row of data.

Once you have run a successful query to an Excel spreadsheet using the OpenRowset command, you can also join the data to other data sources such as SQL Server tables or views. Here is an example of a join to an imaginary table called tblUserNames:

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\test.xls',
                'SELECT * FROM [Sheet1$]') b
INNER JOIN  tblUserNames a ON b.UserId = a.UserId
                WHERE NOT b.UserId IS NULL

Hopefully this has been a helpful overview of running OpenRowset commands in SSMS to query Excel spreadsheets. Please take a look at the MSDN documentation for the full list of parameters that the OpenRowset function supports.