Home > Excel > Query Excel file source through Linked Server

Query Excel file source through Linked Server


In previous post we saw how to setup a Linked Server for MySQL Database. Now lets go with other data sources. Excel files are the most important source of data and report management in a particular department.

When you need to do some query on Excel data, one way is to use Import/Export wizard, push the excel contents to SQL Server and then query on SQL Server DB. Another and easy way is to create a Linked Server to Excel file and query directly the Excel file itself.

You just need to create the Excel file and execute the following SQL Statements below:

For Excel 2003 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLS_NewSheet',
	@srvproduct = 'Jet 4.0',
	@provider = 'Microsoft.Jet.OLEDB.4.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xls',
	@provstr = 'Excel 5.0; HDR=Yes'

Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLS_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLS_NewSheet...[Sheet1$]

For Excel 2007 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLSX_NewSheet',
	@srvproduct = 'ACE 12.0',
	@provider = 'Microsoft.ACE.OLEDB.12.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xlsx',
	@provstr = 'Excel 12.0; HDR=Yes'

Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLSX_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLSX_NewSheet...[Sheet1$]

Note: If your excel file don’t have headers, then set HDR=No

You may need to execute the following SQL Statements to configure the Linked Server initially:

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

Useful Link: http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx

About these ads
  1. December 1, 2010 at 4:29 am

    EXEC sp_addlinkedserver MyCSV, ‘Jet 4.0′, ‘Microsoft.Jet.OLEDB.4.0′,’c:\’, NULL,’Text’
    GO
    EXEC sp_addlinkedsrvlogin MyCSV, FALSE, NULL,NULL,NULL
    GO
    EXEC sp_tables_ex MyCSV — note how it picks up any / all txt and csv files in that folder
    GO
    select * from MyCSV…Order_Worksheet#csv — four part identifier using table name from above
    GO
    select * from openquery(MyCSV,’select * from order_worksheet.csv’)
    GO
    sp_dropserver ‘MyCSV’, ‘droplogins';
    GO

  2. Saurabh Sharma
    January 21, 2011 at 12:10 pm

    Hi manub22

    Thanks for the great article … but when I tried your steps to add in a xlsx file I got the following error – “The OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked serverreported an error. Access denied.”

    Fir this I went to services.msc and went to the properties of SQLEXPRESS .. I made sure that my system user name and password were entered to the this account section in the Log on tab

    And then I restarted the service and my error was gone !

    –Saurabh

  3. bnar
    June 13, 2012 at 5:43 pm

    Thanks for the article,i want to link sql server to an Excel file in another Pc in the same network?could you help please

    • June 13, 2012 at 6:13 pm

      @bnar, you can provide the network path at the @datasrc parameter. You will need to share the folder where Excel file is stored on another PC.

  1. July 10, 2012 at 5:35 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 405 other followers

%d bloggers like this: