<

Improve company productivity with a Business Account.Sign Up

x

Read Excel data from SQL Server 2005

Published on
9,759 Points
3,659 Views
1 Endorsement
Last Modified:
Approved
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
Comment
Author:pankaj_3425
0 Comments

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Join & Write a Comment

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month