SQL - OPENDATASOURCE - Excel Sheet with hyphen in name

2
votes

I have data source that comes regularly from a third party in an Excel workbook. The sheets are are named Page1-2 and Page2-2 and I am having trouble accessing them with SQL2008R2 because of the hyphens. Renaming one of sheets to Sheet1 works like this:

SELECT * 
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source="D:\MyFiles\MyFile.xlsx"; Extended properties=Excel 12.0' )...Sheet1$

I've tried all sorts of combos with the other sheet with no luck eg. [Page2-2], 'Page2-2', [Page2-2$] etc. I get the error 'The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not contain the table "Page1-2$". The table either does not exist or the current user does not have permissions on that table.'

Essentially, I want this process to be as automated as possible so if I can get away with not renaming sheets, I want to know how I can access the sheets with hyphens in the names????

Googling gives me results that uses a Jet provider, but that's no good for me.

Thanks!

1
sql-serveroledb
I think you need OPENDATASOURCE( )...[Page1-2$] (i.e. dollar sign inside the square brackets), but I can't test this at the moment. - aucuparia
Thanks, but I tried that one with no luck. - Warren
what error do you get? - aucuparia
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not contain the table "Page1-2$". The table either does not exist or the current user does not have permissions on that table. - Warren
this suggests using single quotes inside square brackets - e.g. ['Page1-2$']. - aucuparia

1 Answers

2
votes

The OLEDB provider exposes Excel worksheet names as tables named as per the worksheet, with a dollar sign appended. Certain characters (among them space and hyphen) will confuse either the OLEDB driver or the SQL parser or both. To refer to your worksheets you need:

OPENDATASOURCE(   )...['Page1-2$']

I suspect the OLEDB driver needs the single quotes because of the hyphen, and SQL will then want square brackets because of the quotes!