Manipulating EXCEL 97-2003 Worksheets with the OPENROWSET Command

Manipulating EXCEL 97-2003 Worksheets with the OPENROWSET Command

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
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 (for SQL Server 2008!) – Yep, it is a cool feature 🙂
———————————————————————————————
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’

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!


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.