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
Learn how to efficiently import and export data in SQL Server.
Enroll to our online course today!
Check our online course titled “How to Import and Export Data in SQL Server Databases”
(special limited-time discount included in link).
Learn how to import and export data in SQL Server, via all the available built-in tools such as SSIS, BCP and more!
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 * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samplesexcel_file_2003.xls;','SELECT * FROM [Sheet1$]')
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 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')
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!
Featured Online Courses
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check out Artemakis’s eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks 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).