How to import data from an Excel File using T-SQL

Posted on by
If you have an Excel File named ExcelFile.xls, then you can perform SELECTs on that Excel file using the Excel Sheet name and the Jet Provider.

SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\ExcelFile.xls;Extended Properties="Excel 8.0"')...[Data_Load$];

In case, you use the same Excel file over and over again, then you can use OPENQUERY using a Linked Server defined to your Excel File.
Using OPENQUERY:

SELECT *
FROM OPENQUERY (DataLoad, 'select * from [Data_Load$]')

If you find that the data being imported for non-Text columns is NULL, then refer the following blog entry:
Advertisement