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..
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:
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:
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.
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.
Ranga Narasimhan has been in IT for over 10 years working on Siebel, Visual Basic, Crystal Reports and SQL Server.
