July 15, 2013 at 8:21 am
Hello, can some one point me to proper direction here or it just stupid question (not sure what to think)
I have an automated file processing setup on SQL2010 server.
what it does is read a number of Excel 2010 (xlsx) files, convert(extract) specific worksheets form those files into CSV delimited files for uploading to the website.
my issue is that I have 6 Excel files, and at least 2 of them error out when I do the select on them.
as an example the first select works just fine but the second errors with
"Msg 7314, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not contain the table
"'Centers$'". The table either does not exist or the current user does not have permissions on that table."
all files are in the same folder and created and updated by the same user.
folder is a public share for all internal users use thus all users have all permissions to the files.
the path is a local path for the server as in I could easily provide path like 'D:\USERSHARE\WebsiteLiveFiles\BAndSConfig.xlsx' and it still works when it works. (in fact all paths are local in actual job script.)
yes I know it is not a good idea to have SQL server and file server on one machine but we do not have another chassis at this moment.
I can not provide sample of the files as the data is proprietary and confidential.
and since I am not sure what cause the issue I can not replicate it either.
I have tried copying the work sheet from files that work into one that errors out.
the copied WS works just fine selecting from the same file. so it is not file permission.
#1 (WORKS)
SELECT * FROM
OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Data Source="\\MYSERVER\USERSHARE\WebsiteLiveFiles\BAndSConfig.xlsx";
Extended Properties="Excel 12.0;HDR=NO;IMEX=1"')...['SPC Data$']
#2 (ERRORS OUT)
SELECT * FROM
OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Data Source="\\MYSERVER\USERSHARE\WebsiteLiveFiles\DPC.xlsx";
Extended Properties="Excel 12.0;HDR=NO;IMEX=1"')...['Centers$']
July 15, 2013 at 8:40 am
OK, I think I got it. so I will post my finding here for some one to use in the future, as it was not obvious to me and I might not be the only one... :w00t:
it seams that:
IF your worksheet name has space or special characters in it, you need to quote the name of worksheet like
"SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Data Source="D:\USERSHARE\WebsiteLiveFiles\Configurator.xlsx";Extended Properties="Excel 12.0;HDR=NO;IMEX=1"')...['Center Sizes$']"
However if the name is more or less normal style (IE: no special anything in it and no spaces) you should not use quotes. like
"SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0','Data Source="D:\USERSHARE\WebsiteLiveFiles\Configurator.xlsx";Extended Properties="Excel 12.0;HDR=NO;IMEX=1"')...[Centers$]"
that was the difference in the excel files all files that were working as expected had worksheets names with spaces thus all the script were quoted.
the files that did not work were newly created and had worksheets name as single words. since I copied the selects from the main files and just substituted the file names and WS names it did not work.
I guess the OLEDB driver is very finicky about this kind of SQL format and quotes.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply