SQL Server - Read Excel, CSV 2003, 2007 files using OpenRowSet

In SQL Server, we can read the data directly from an Excel file or a .csv file using OpenRowSet function as given below:

For OPENROWSET function to work properly in SQL Server 2005, you need to check "Enable OPENROWSET and OPENDATASOURCE Support" using SQL Server Surface Area Configuration for Features.

 Once you are done with that you can make use of the below queries to read the data:

--Read .CSV file from a 32 bit SQL Server 
SELECT * FROM OPENROWSET
('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=D:\DWP Deliverables\CSPRojectIntegration\;Extensions=CSV;','SELECT * FROM CSProjects.csv')

--Read Excel file from a 32 bit SQL Server 
SELECT *  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\DWP Deliverables\CSProjects.xls',
'SELECT * FROM [Sheet1$]')

--Read Excel 2007 file from a 32 bit SQL Server 
Another workaround is trying to install Microsoft.Jet.OLEDB.12.0, and use your connection string with OLEDB 12.0, it can be downloaded at :
2007 Office System Driver:  http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891

On the other hand, you can save the 2007 file to 2003 format and try reding it from OLEDB4.0 driver

--Read files from 64 bit SQL Server
JET OLEDB Provider works only with 32 bit. For 64-bit OLEDB Provider for ODBC you have to install MSDASQL using the below link http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20065#overview


Hope this helps!!