Import the data from Excel to Sql Server

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).


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