Use linkserver to Excel Spreadsheet

Use the Microsoft OLE DB Provider for Jet on an Excel Spreadsheet

To create a linked server definition using the Microsoft OLE DB Provider for Jet to access an Excel spreadsheet, first create a named range in Excel specifying the columns and rows of the Excel worksheet to select. The name of the range can then be referenced as a table name in a distributed query.

EXEC sp_addlinkedserver 'ExcelSource',
  'Jet 4.0',
  'Microsoft.Jet.OLEDB.4.0',
  'd:\book1.xls',
  NULL,
  'Excel 5.0'
GO


In order to access data from an Excel spreadsheet, associate a range of cells with a name. A given named range can be accessed by using the name of the range as the table name. The following query can be used to access a named range called Sheet1 using the linked server set up as above.

SELECT * FROM ExcelSource...Sheet1$
GO


Drop That link Server:

sp_dropserver 'ExcelSource'