Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using a SQL Server Linked Server to Query Excel Files


By:   |   Last Updated: 2010-05-27   |   Comments (9)   |   Related Tips: More > Microsoft Excel Integration

Problem

In my work place, we have Data Analyst/Data Integration people who constantly import Excel files into SQL for queries.  Instead of importing the data to SQL Server, is there another way to query an Excel file using SQL Server tools?

Solution

There are several methods of doing this and one of them is to use a linked server which we will cover in this tip..

First create an Excel file named MyTable.xls. It is just an empty Excel file. (Note: you can download the Excel files and code here)

Next, create a new Linked Server pointing to Mytable.xls that was just created.

From Management Studio--> Object Explorer-->ServerObjects-->Right click Linked Servers to open a new Linked Server window.

a way to query an Excel file using SQL Server tools

Name the Linked Server "QUERYEXCEL" as shown below. For server type, choose "Other Data Sources". In the data source field, enter the full path of the MyTable.xls file you just created. Enter other fields as seen in the figure below.

Name the Linked Server "QUERYEXCEL"

To do a quick test drive, I have attached MyTable.xls file and also Employee.xls and sales.xls which you can download here.

To make it little interesting, Sales.xls has two sheets, with sales header data in Sheet1 and sales detail in sheet2. Also, remember to save these excel files to the folder on the SQL Server where the new linked server was just created, not on your desktop :)

--Test the Linked Server, Query the existing excel file Mytable.xls, which is empty:
select * from QueryExcel...Sheet1$

You would see this

save these excel files to the folder on the SQL Server

Lets query the Employee.xls

--------------------Query Employee.xls-------------------------
--Rename Employee.xls to Mytable.xls, If you have enabled cmd shell, this should work:
exec master..xp_cmdshell 'ren C:\DBAwork\MyTable.xls MyTable_Original.xls'
exec master..xp_cmdshell 'ren C:\DBAwork\Employee.xls MyTable.xls'

select * from QueryExcel...Sheet1$

You would see this:

query the Employee.xls

Lets query the Sales.xls, Sales Header is on Sheet1

------------------Query the Sales.xls--------------------------
--Rename MyTable.xls to its original name Employee.xls.
--Rename Sales.xls to MyTable.xls
exec master..xp_cmdshell 'ren C:\DBAwork\MyTable.xls Employee.xls'
exec master..xp_cmdshell 'ren C:\DBAwork\Sales.xls MyTable.xls'

--Query the SalesHeader
select * from QueryExcel...Sheet1$

query the Sales.xls, Sales Header is on Sheet1

Lets query the Sales.xls, Sales Detail is on Sheet2

--Query the SalesDetail
select * from QueryExcel...Sheet2$

query the Sales.xls, Sales Detail is on Sheet2

Join Header and Detail from Sheet1 and Sheet2

--Lets check if join works

select Header.SalesOrderID, Detail.SalesOrderDetailID, Header.AccountNumber, Detail.ProductID, Detail.UnitPrice
From
(select * from QueryExcel...Sheet1$) Header
inner join
(select * from QueryExcel...Sheet2$) Detail
On Header.SalesOrderID = Detail.SalesOrderID
order by Header.SalesOrderID, Detail.SalesOrderDetailID

quickly query and join Excel files with other tables thus avoid creating unwanted tables in the database.

This is very simple, but at the same time pretty helpful to quickly query and join Excel files with other tables thus avoid creating unwanted tables in the database.

Next Steps


Last Updated: 2010-05-27


next webcast button


next tip button



About the author
MSSQLTips author Ranga Narasimhan Ranga Narasimhan has been in IT for over 10 years working on Siebel, Visual Basic, Crystal Reports and SQL Server.

View all my tips
Related Resources


Become a paid author


Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, January 01, 2013 - 6:36:18 AM - Minku Bhatia Back To Top

Can we connect Oracle SQL Server with Excel in same way mentioned above, I was unable to follow steps mentioned  above, please guide...

 

Thanks 

Gurshish 

Thursday, October 04, 2012 - 9:46:03 AM - Ranga Back To Top

Sometimes even if excel has just 10 rows of data, when you query, you may see a whole lots of rows with null values. To avoid this, from the first empty cell, hit ctrl+shift+end to select the entire excel sheet after the data rows and hit delete. this will ensure that sql is not picking up the empty cells. Let me know if this works. Also, if you have data on the excel and sql returns null, make sure the excel column has the right data type, select the column and format and choose the right datatype.

Thursday, October 04, 2012 - 12:23:06 AM - jj Back To Top

excel sheet has values but we got the null values from sql query.how to get the original value using linked server.

Friday, July 13, 2012 - 12:36:33 PM - JIT Back To Top

 

Sir, thats what i m looking for.

Finally I got solution for issue.

THANX !!!!!!!!!

Sir how to insert integer values into excel using above concept

Thursday, June 14, 2012 - 2:13:04 AM - bnar Back To Top

Thanks for the article,can we create relation between linked tables?how about if the Excel file is on another server in the same network?

Thursday, March 29, 2012 - 8:25:02 AM - JIT Back To Top

I Want to Export Data To Formatted Excel Sheet From Sql Server Using OpenRowSet .

Formatted Excel Sheet Contains a Table In the middle of sheet in which i want to export data

Tuesday, March 27, 2012 - 3:26:49 PM - Ray Back To Top

I am using Windows 7.0 Pro (32-bit) with SQL 2008R2 Developer Edition SP1 (build 2769).  SQL Service is running as "local system" and the spreadsheet is on a local folder.

MS Office 2010.

I have not found a combination of settings that will create a usable Linked Server. I tried the settings specified in the article, I also tried the Access 12.0 Provider with "Excel 12.0" as the Provider string.  I tried several variations.

The errors are variations on Can't access the data SQL Server Error 7303.

 

 

Monday, June 07, 2010 - 10:48:11 AM - admin Back To Top

The download has been fixed.

Monday, June 07, 2010 - 10:39:36 AM - gregsoc Back To Top

The link to the download file for this tip do not work.  Can they be fixed please?

Learn more about SQL Server tools