How do I configure an Excel file as a Linked Serve

I have opened the Linked Server dialog to create a Linked Server so that I can import my excel file data to a SQL Server 2005 database. Which provider must I use and what other settings do I need to fill in?

回答1:

You would set it up using either OLEDB provider or the provider for ODBC drivers and create a connection using the ODBC Administrator tool on the server to the Excel file. Are you planning to read from this Excel file on a regular basis? If not, then setting it up as a Linked Server could be overkill.

For more details, see "How to import data from Excel to SQL Server" and "How to use Excel with SQL Server linked servers and distributed queries"

回答2:

For those who are using SQL SERVER 2012+ you can use the Microsoft OLEDB 12.0 Provider that comes with SQL Server 2012+ and which allows you to use Excel 2007-2013 xlsx files for adhoc distributed queries or as a linked server. Examples below.

The Excel workbook 'Application.xlsx' has 3 worksheets Application,Device,User First Activate Ad Hoc Queries on the Server.

USE MSDB
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

For Ad Hoc Queries use the OPENROWSET Function.

SELECT * FROM 
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel   8.0;Database=C:\Users\Administrator\Desktop\Application.xlsx;HDR=YES', 'SELECT * FROM [Application$]');

SELECT * FROM 
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel   8.0;Database=C:\Users\Administrator\Desktop\Application.xlsx;HDR=YES', 'SELECT * FROM [Device$]');

SELECT * FROM 
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel  8.0;Database=C:\Users\Administrator\Desktop\Application.xlsx;HDR=YES', 'SELECT * FROM [User$]');

For Creating a Linked Server for Excel 2007-2013 format:

USE MSDB
GO
EXEC sp_addLinkedServer
@server= 'XLSX_MATRIX',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'C:\Users\Administrator\Desktop\Application.xlsx',
@provstr = 'Excel 12.0; HDR=Yes'

Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLSX_MATRIX, 'Select * from [Application$]')
SELECT * FROM OPENQUERY (XLSX_MATRIX, 'Select * from [Device$]')
SELECT * FROM OPENQUERY (XLSX_MATRIX, 'Select * from [User$]')

SELECT * FROM XLSX_MATRIX...[Application$]
SELECT * FROM XLSX_MATRIX...[Device$]
SELECT * FROM XLSX_MATRIX...[User$]