Read Excel data from SQL Server 2005

I am showing a way to read/import the excel data in table using SQL server 2005...

Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name".

Now to import this Excel data into the table, we will use the OpenRowSet method

OpenRowset contains all the information that is required to connect to remote data source.
It can also be used (alternatively) to access table from linked server

select * 
                      from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\temp\book1.xls', 'SELECT * FROM [Sheet1$]')

Select all Open in new window

This will read all the data from the excel sheet 1. If it had a named worksheet, then we would need to use the actual name.

But this is an Ad hoc query statement, and so, might not be permitted to be executed by the SQL server 2005 (but enabled by default in SQL Server 2000).

So, we will need to go into SQL Server configuration and enable the Ad Hoc queries. Firstly, it is an advanced options, so we will need to enable that first, and then we can deal with enabling the adhoc query.

To configure the SQL Server for these advanced statements, execute the following T-SQL in a query window:

sp_configure 'show advanced options', 1
                      GO
                      
                      reconfigure
                      GO

Select all Open in new window

This will result in a confirmation of the configuration changes :
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

And now we have enabled the advanced options, we can now focus on the ad hoc query option. To do so, execute the following T-SQL in a query window:

sp_configure 'Ad Hoc Distributed Queries', 1
                      GO
                      
                      reconfigure
                      GO

Select all Open in new window

This will result in a confirmation of the configuration changes :
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

So, now we are finally ready for the original / the first SQL statement to import excel data so it will execute successfully.

In this similar way, we can insert any valid data stored in the disk. like images..

INSERT INTO Employees (Id, Name, Photo) 
                      SELECT 10, 'John', BulkColumn from Openrowset( Bulk 'C:\photo.bmp', Single_Blob) as EmployeePicture

Select all Open in new window

We can also use the BULK rowset provider for OPENROWSET to read data from a file.
SINGLE_BLOB Returns the contents of data_file as a single-row, single-column rowset of type varbinary(max).

But it is recommended that import XML data only using the SINGLE_BLOB option, rather than SINGLE_CLOB and SINGLE_NCLOB, because only SINGLE_BLOB supports all Windows encoding conversions.

Hope you find this article useful, and point out that it is one of quite a few different methods available to import data into SQL Server.