Wednesday, February 25, 2009

Manipulating EXCEL 97-2003 Worksheets with the OPENROWSET Command

SQL Server provides among other the neat feature of the OPENROWSET command.
OPENROWSET allows establishing ad hoc connections using OLE DB. It actually allows one-time access to remote data from an OLE DB data source. If you just need to access this data in an ad hoc manner, you can use the OPENROWSET command instead of setting up a linked server.

An example of the OPENROWSET command, is accessing EXCEL files and treating them like SQL Server tables thus being able to perform SELECT, UPDATE, INSERT and DELETE operations on them.

There is one consideration though; because establishing ad hoc remote connections may increase security risk, in newer versions of SQL Server (SQL Server 2005, SQL Server 2008) this feature is turned off by default.

Though, you can easily enable it either by configuring the Surface Area of SQL Server from within Management Studio or by using sp_configure.

As I am a little bit of a code junkie I prefer using sp_configure :)

Here's how you can enable ad hoc queries in SQL Server (run each statement separately):

sp_configure 'show advanced options',1
reconfigure
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

And here's how you can disable it (again, run each statement separately):

sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
sp_configure 'show advanced options',0
reconfigure


Let's see some code examples on how we can perform these operations on an EXCEL 97-2003 worksheet.

Assumption 1 : I have an EXCEL 97-2003 file with two columns. The sheet is called "Sheet1" and the two columns,"code" and "description" (I used the first excel row for defining the column names).

Assumption 2 : My EXCEL file's full path is "C:\blog_samples\excel_file_2003.xls"

Tip: It is preferable not to use spaces in file paths for such cases.

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


UPDATE Statement
-----------------------
update OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samples\excel_file_2003.xls;','SELECT * FROM [Sheet1$]')
set description='New Description - Product 3'
where code='30'


INSERT Statement
----------------------
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samples\excel_file_2003.xls;','SELECT * FROM [Sheet1$]')
select '40','Product 4'
UNION ALL
select '50','Product 5'


INSERT Statement + Row Constructors (for SQL Server 2008!) - Yep, it is a cool feature :)
---------------------------------------------------------------------------------------------
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samples\excel_file_2003.xls;','SELECT * FROM [Sheet1$]')
values ('60','Product 6'),('70','Product 7')

DELETE Statement
--------------------
Well, the DELETE statement is a little bit tricky with EXCEL data and OPENROWSET. As the Microsoft KB Article on the following link says, in an EXCEL worksheet you can only delete values in fields (cells) while you cannot delete an entire record if you receive the following error message:

Deleting data in a linked table is not supported by this ISAM.

Alternatively, you can "delete" EXCEL records with OPENROWSET by blanking out the contents of each individual field.

Also, the above KB Article says that you cannot delete the value in a cell containing an Excel formula or if you get the following error message:

Operation is not allowed in this context.

So, back to our example, let's run the following:

update OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samples\excel_file_2003.xls;','SELECT * FROM [Sheet1$]')
set code=NULL, description=NULL
where code='70'

In this way the last record of the EXCEL worksheet has been "deleted".

Remarks
-----------
With the above examples we saw how we can manipulate EXCEL 97-2003 files using SQL Server and the OPENROWSET commnand.

A future post will introduce the topic of manipulating EXCEL 2007 files using the OPENROWSET command.

By the way, on this link, you can obtain the latest service pack for the Microsoft Jet 4.0 Database Engine.

Until next time!
[Ad]
Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: