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.
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.
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
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:
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$
Lets query the Sales.xls, Sales Detail is on Sheet2
--Query the SalesDetail
select * from QueryExcel...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
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
- Renaming the files each time may not be the easiest approach, but hopefully you got the idea of how this could be done.
- Take a look at these other tips that are related to this topic:
Ranga Narasimhan has been in IT for about 10 years. He started as a Visual Basic / Crystal Reports developer and then started coding in SQL Server. He has been a SQL Server developer and for the last few years doing SQL server production support operations. In his last job, Ranga was the primary DBA for the Siebel call center database and has learned a lot from the challenges that a very large database can bring and an application where queries are built dynamically. He enjoys spending time with his family. His interests are playing tennis and golf.