T-SQL: Excel 2007 & OPENROWSET

I do a lot of work with Excel spreadsheets and often need to import data from them on an adhoc basis. I typically use the Import/Export wizard to do this, although depending on how i'm feeling and the requirement I may go with one of the functions which gets data from a remote source such as OPENROWSET.

On this occasion, I was working with an Excel 2007 (.xlsx) workbook so I went straight to my favourite search engine to get a site which would remind myself of the syntax i'd need (how the internet makes one lazy on remembering syntax!) and it didn't disappoint. The first thing I noticed was that this wasn't using the old trusty Jet driver that I would use when working with Excel 2003. Using the Jet provider gives a fairly unhelpful:

OLE DB provider "Microsoft.Jet.OLEDB.4.0"FOR linked server "(null)" returned message "Unspecified error"
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"


Instead, you need to use the Access Database Engine driver:

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\ExcelImport\MyData.xlsx', 'SELECT * FROM [Sheet1$]')



So we're all set to get that data. Or so I thought as on executing the query, I got an error:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

The errors here are a bit of a red herring as the directory in question is open to Everyone. Also, it says that it is unable to get the column information but strangely, the column set was returned (albeit empty) in the results tab. Odd.

This problem can be averted by simply running the following queries to set some OLEDB properties:

USE [master]
GO

EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO



NB: a simple gotcha is remember to have your spreadsheet closed when trying to access it using this method otherwise you'll get another access denied error.

A couple of things I noticed but was unable to reproduce on a consistent basis, is that after running these queries, the excel spreadsheets appear to become corrupt for a length of time. Also, the changes did not always appear to be instant and on my machine it sometimes took 10 minutes for the behaviour to change.