How to query excel from sql server

This post takes us through on how to query a excel file from SQL Server, doing this using the distributed queries.
Firstly, make sure that the “Run_Value” is set to “1” for “Ad Hoc Distributed Queries”
 

sp_configure 'Ad Hoc Distributed Queries','1'

GO

RECONFIGURE

GO

The below is the result that we are going to get, and we can confirm the change of the configured value by again running sp_configure ‘Ad Hoc Distributed Queries’

 Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.

If we do not do this, we will end up getting an error like the below.
“Ad hoc access to OLE DB provider ‘Microsoft.Jet.OLEDB.40′ has been denied. You must access this provider through a linked server.””
We can query data by using either of the OPENDATASOURCE or OPENROWSET to query the excel directly.

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\test\Excel2Sql.xls;Extended Properties=Excel 8.0')...[Sheet1$]

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database C:\test\Excel2Sql.xls', [Sheet2$])

We need to make sure that the source excel file is accessible, means we need to give the correct location, and also we need to make sure that the excel file is not open during this select process, if it is open we would generally get the below error.
“OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Unspecified error“.”
“Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.”
Note: – we cannot use this method on a 64 bit OS System, running 64 bit SQL SERVER