In many cases I had to query data from excel sheet with large number of records … of course I can do this by using filtering feature from MS excel, many developer prefer writing sql statements instead of using filtering feature built into MS Excel. Using Sql statements to query data from Excel sheet will make developer life easier. You can load this sheet into DB server then query it using the ordinary tables, actually this is not the optimum solution for this case.. You can query excel sheet without load it into DB server.
By using ODBC you can query excel sheet’s data without loading it into DB from inside Query analyzer or MS Sql Management studio…. It is very simple query and u can even filter by writing your own WHERE condition.
To query data form MS Excel 2003 you can use the following Select Statement:
|
SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;DATABASE=FileNameWithPath.xls’, ‘Select * from [Sheet1$]’) |
And form MS Excel 2007 you can use the following select statement:
|
SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0;Database=FileNameWithPath.xlsx;HDR=No;IMEX=1’, ‘select * from [Sheet1$]’) |
For example:
Suppose you have an excel sheet with 3 columns; firstName,lastName and Email.And this sheet is stored at C:\Filename.xsl on your hard drive… to query this file:
|
SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;DATABASE=c:\filename.xls’, ‘Select * from [Sheet1$]’) |