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!
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!