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


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:
Advertisements

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s