I had difficulty finding any documentation for formatting the sheet name when using the OLEDB ACE engine to access an Excel file. I had originally attempted the following query:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=C:\Report Output.xlsx', sheet1$) |
This raised the error:
Msg 7314, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not contain the table "Report Output". The table either does not exist or the current user does not have permissions on that table.
I assumed that a generic sheet name of sheet1$ would simply refer to the first sheet in the workbook, similarly to how you can call the sheet by the number or the name in VBA. However, this is not the case. Other MSDN documentation had formatted the sheet name like [Name$]. Some other variations that failed included:
'Report Output'['Report Output'][Report Output$]['Report Output'$]
In my case, the sheet name contained a space. The formatting that worked was ['Report Output$']:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=C:\Report Output.xlsx', ['Report Output$']) |
