Manipulating EXCEL Worksheets with the OPENROWSET Command

In this article, we will be discussing about manipulating EXCEL 97-2003 worksheets with the OPENROWSET command.

About 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_samplesexcel_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_samplesexcel_file_2003.xls;','SELECT * FROM [Sheet1$]')

UPDATE Statement

update OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samplesexcel_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_samplesexcel_file_2003.xls;','SELECT * FROM [Sheet1$]')
select '40','Product 4'
UNION ALL
select '50','Product 5'

INSERT Statement + Row Constructors (SQL Server 2008 or later)

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samplesexcel_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_samplesexcel_file_2003.xls;','SELECT * FROM [Sheet1$]')
set code=NULL, description=NULL
where code='70';
GO

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

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!