OPENROWSET ( 'provider_name', 
              { 'datasource'; 'user_id'; 'password' | 'provider_string' },
              { [ catalog.][schema.]object | 'query' }
           )

In this article, we will only be using the following syntax of the OPENROWSET:

OPENROWSET ( 'provider_name', 'provider_string', 'query' )

The provider_name is a character string that represents the friendly name of the OLE DB provider as specified in the registry.  In the case of Excel, the provider_name that we will use is "Microsoft.Jet.OLEDB.4.0".

The provider_string is a provider-specific connection string that is passed to initialize the OLE DB provider.  provider_string typically encapsulates all the connection information needed to initialize the provider.  For Excel, the provider_string that we will use is "Excel 8.0;Database=Excel File Name".

The query is a string constant sent to and executed by the provider.  SQL Server does not process the query but processes query results returned by the provider (a pass-through query).

Querying an Excel Worksheet

Let's say you have an Excel file named Addresses.xls containing addresses of people with the following sample data:

  |     A     |     B    |               C             |         D        |   E   |     F      |
------------------------------------------------------------------------------------------------
1 | FirstName | LastName | Address                     | City             | State | ZIP        |
2 | Mickey    | Mouse    | Walt Disney World           | Lake Buena Vista | FL    | 32830      |
3 | Donald    | Duck     | Walt Disney World           | Lake Buena Vista | FL    | 32830      |
4 | George    | Bush     | 1600 Pennsylvania Avenue NW | Washington       | DC    | 20500-0003 |
5 | George    | Clooney  | 151 El Camino Drive         | Beverly Hills    | CA    | 90212-2704 |
6 | Tom       | Cruise   | 9830 Wilshire Boulevard     | Beverly Hills    | CA    | 90212-1804 |

To query this data from the Excel file without using DTS to import it to a table in SQL Server, you can do the following SELECT statement with OPENROWSET:

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

Sheet1 is the name of the worksheet in the Excel file.  Make sure that the name of the worksheet is followed by the dollar sign ($) when specifying in the query.  If the dollar sign is not included, the following error will be encountered:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
[OLE/DB provider returned message:
The Microsoft Jet database engine could not find the object 'Sheet1'.
Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace
[OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005:   ].

The result of this query is as follows:

FirstName  LastName   Address                        City                 State  ZIP        
---------- ---------- ------------------------------ -------------------- ------ ---------- 
Mickey     Mouse      Walt Disney World              Lake Buena Vista     FL     NULL
Donald     Duck       Walt Disney World              Lake Buena Vista     FL     NULL
George     Bush       1600 Pennsylvania Avenue NW    Washington           DC     20500-0003
George     Clooney    151 El Camino Drive            Beverly Hills        CA     90212-2704
Tom        Cruise     9830 Wilshire Boulevard        Beverly Hills        CA     90212-1804

By default, the first row in the Excel file is assumed to be the column header, as can be seen from the output.

It should be noted that the Excel file must be located in the SQL Server where you are connected and not on your local machine.  The C: drive specified in the OPENROWSET function is the C: drive of the SQL Server you are connected to and not your local C: drive.  If the Excel file specified in the provider_string parameter of the OPENROWSET function does not exist or is incorrect, the following error will be encountered:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. 
The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005:
The provider did not give any information about the error.].

Just like any SELECT statement, conditions can be included in the query to filter out records from the Excel worksheet by simply specifying it in the WHERE clause.  As an example, to display only the addresses of people from Florida you can do the following:

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls',
                'SELECT * FROM [Sheet1$]')
WHERE [State] = 'FL'

This query gives the following result:

FirstName  LastName   Address                        City                 State  ZIP        
---------- ---------- ------------------------------ -------------------- ------ ---------- 
Mickey     Mouse      Walt Disney World              Lake Buena Vista     FL     32830
Donald     Duck       Walt Disney World              Lake Buena Vista     FL     32830

Part 2: Querying an Excel Worksheet Without Header and With Mixed Column Data Type
Part 3: Importing an Excel Worksheet

Related Topics:

• Frequently Asked Questions - SELECT Statement
• Frequently Asked Questions - INSERT Statement