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

 

Dejan Sarka