<

Go Premium for a chance to win a PS4. Enter to Win

x

Read Excel data from SQL Server 2005

Published on
9,642 Points
3,542 Views
1 Endorsement
Last Modified:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Join & Write a Comment

In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month