SQL Server - Query Excel Using Linked Server
In this post we will see a very simple example of how to query an Excel file using SQL Server and Linked server or how to import Excel data to SQL Server using Linked Servers.
I am using SQL Server 2008 R2 and MS Excel 2007.
Below Excel file named "Book2.xlsx" located at (d:\Book2.xlsx) is used for this example:
Run the below stored procedure. Make sure to replace the @datasrc value with your file's path.
EXEC sp_addLinkedServer
@server= 'HPPC1',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'D:\Book2.xlsx',
@provstr = 'Excel 12.0; HDR=Yes'
If you do not want to include header row or your excel file does not have headers you need to set HDR=No above.
sp_addlinkedserver STORED PROCEDURE creates a linked server. After a linked server is created by using sp_addlinkedserver, distributed queries can be run against this server. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.
@server is the name of the linked server to create. The @server parameter will be the name by which the linked server will be known.
@srvproduct is the product name of the OLE DB data source to add as a linked server.
For more on sp_addlinkedserver see here.
We are using OPENQUERY to query the Excel linked server. The first argument that OPENQUERY expects is the linked server name. Delimiters are required for worksheet names, as shown below.
SELECT * FROM OPENQUERY (HPPC1, 'Select * from [Sheet1$]')
Result of running the above query:
| id | name | add |
|---|---|---|
| 1 | A | aa |
| 2 | B | bb |
| 3 | C | cc |
If you get following or a similar error:
Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. Access denied. Msg 7350, Level 16, State 2, Line 2 Cannot get the column information from OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)".
then try solution from here.
You may need to configure linked server initially using the following statements:
sp_configure 'show advanced options', 1 RECONFIGURE WITH OverRide sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE WITH OverRide
For more information on Excel Import to SQL Server using Linked Servers see here.
Also remember that there is no way to import Excel data to SQL Server x64 using Linked Servers technique on a machine with Microsoft Office x86 (32 bit)!