How to Use SQL Query to Access Microsoft Excel Spreadsheet

This topic shows how to use SQL query to access data stored in a Microsoft Excel spreadsheet.

Example 1

If your data are organized as one table per worksheet, then use the first row to declare the field names. The table name is defined by the name of the worksheet with a dollar sign ( $ ) as a suffix. Note that the dollar sign is a reserved character in SQL, hence the table name in a query should be enclosed in square brackets.

SQL query which reads the maximum daily temperature in London from the above table would have the following syntax:

SELECT MaxTemp FROM [Sheet1$] WHERE City='London'

Example 2

Excel allows you to associate a name to a given group of cells in a spreadsheet. The associated name can then be used in SQL queries as the table name.

Use the Insert / Name / Define option from the main menu in Microsoft Excel to define PriceList as the name of the group of cells displayed above. You can also select the cells and type the new name in the Name Box field located to the left of the Formula Bar.

SQL query which reads the marker price from the above table would have the following syntax:

SELECT ItemPrice FROM PriceList WHERE ItemName='Marker'