Read Excel data from SQL Server 2005

Published:
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$]')

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

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

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

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.
1
4,113 Views

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community