Using Excel file as a linked server usually causes some headache. Main reasons for the headache are:
– Excel does not use the concepts “Catalog” and “Schema”, so we must refer to a table with server…table
– If not defined differently, one sheet represents one table with the “$” sign appended, i.e. Sheet1$
– Excel does not have login security, so we must change the default security:
EXEC sp_addlinkedsrvlogin ‘ExcelSource’, ‘false’
In Excel we can also define named ranges of cells. We refer to a named range without the dollar sign appended. Here are some examples how to use Excel as a linked server.
/* Excel as a linked server */
/* Assuming we have an Excel file ‘C:tempMyExcel.xls’
with following data in the first sheet:
Id Name Q
1 AAA 15,3
2 BBB 14,2
3 CCC 8,7
4 DDD 3,9
5 EEE 10,1
*/
EXEC sp_addlinkedserver ‘ExcelSource’,
‘Jet 4.0’,
‘Microsoft.Jet.OLEDB.4.0’,
‘C:tempMyExcel.xls’,
NULL,
‘Excel 5.0’
EXEC sp_addlinkedsrvlogin ‘ExcelSource’, ‘false’
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource…Sheet1$
USE Tempdb
GO
CREATE TABLE test_excel
(id int,
name varchar(255),
q decimal(7,2))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource…Sheet1$
SELECT *
FROM test_excel
GO
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource…Table1
SELECT *
FROM ExcelSource…Table2
