Querying/Reading on Text/Excel files using Opendatasource

Microsoft Sql Server has provided numerous ways to importing textfile into database, like Bulkinsert, Import/export wizard,
DTS (Sql 2000), SSIS (sql 2005/2008). With above all methods if you want Query on the data, you need to first load the into database.
But using open datasource method, you can query on the file without loading into database.
This way we have full control on  the file using t-sql.

Ex:1)
We have excel file with 2 column id and name
Here is the original data in excel file::

---If we want to see the data in Querywindow using t-sql( Make sure don't open excel file while executing)

select * from OPENDATASOURCE('Microsoft.jet.OLEDB.4.0','datasource=C:\test.xls;Extended properties=EXCEl 5.0')...[one$]

---You can even query on the file using where caluse

select * from OPENDATASOURCE('Microsoft.jet.OLEDB.4.0','data source=C:\test.xls;Extended properties=EXCEl 5.0')...[one$]

where id=3

select * from OPENDATASOURCE('Microsoft.jet.OLEDB.4.0','data source=C:\test.xls;Extended properties=EXCEl 5.0')...[one$]

where name='abc'

Ex:2)
Same way we have comma separated text file with id and name columns.

Access text file using MSDASQL provider or Jet procider

SELECT * FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:\', 'SELECT * from test1.txt')

(OR)

select * from OPENDATASOURCE('Microsoft.jet.OLEDB.4.0','data source=C:\;Extended properties=Text;')...[test1#txt]

Note::
If the text file is not a comma separated, then above is not sufficient.You have to create schema.ini file in the same folder where your file resides


The one more advantage of this Opendatasource is, in upfront we don't need to create table with required columns.

Ex: This below Query create table with required columns and load the data into test1 table

select *

into test1

from OPENDATASOURCE('Microsoft.jet.OLEDB.4.0','data source=C:\test.xls;Extended properties=EXCEl 5.0')...[one$]