November 4, 2018 at 11:01 am
Using SQL Server 2016.
I am trying to read an Excel file using OPENROWSET. This works perfectly:SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;Database=D:\temp\my_filename_123.xlsx', 'SELECT * FROM [Sheet1$]')
Note that there are no spaces in the file name.
However, the following will not work:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;Database=D:\temp\my filename 123.xlsx', 'SELECT * FROM [Sheet1$]')
Or using double quotes to enclose the whole path:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;Database="D:\temp\my filename 123.xlsx"', 'SELECT * FROM [Sheet1$]')
The error is ".... cannot find 'Sheet1$'... etc.."
Please help. Regards.
November 4, 2018 at 11:45 am
I've not been able to make it fail with the following code, which I've just tested on my box.
SELECT *
FROM OPENROWSET
(
'Microsoft.ACE.OLEDB.12.0'
,'Excel 12.0;
HDR=Yes;
Database=C:\Presentations\Automating Excel Hell with T-SQL\#440 Pittsburgh\Sample Spreadsheet for Import 20140301.xlsx;'
,'SELECT * FROM [Sheet1$];'
)
;
You are missing a couple of semicolons but those don't appear to matter much. Notice that I've not had to make any special accommodations for file paths that contain many spaces. The only way that I can see what you wrote as not working is if the file or path doesn't actually exist as you have it.
Also notice that I didn't use "XML" in my code even though I'm reading from an "xlsx" file. It does work either way, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2018 at 12:50 pm
Thanks Jeff, this is very weird. I tried for a long time to get it to work and the file exists (I copied/pasted the name). May I ask the following:
1. What Excel version is your file? Mine is Excel 2010
2. What SQL Server version are you using? Mine is Sql Sever 2016 SP2
November 4, 2018 at 1:04 pm
I've not been able to make it fail with the following code, which I've just tested on my box.
SELECT *
FROM OPENROWSET
(
'Microsoft.ACE.OLEDB.12.0'
,'Excel 12.0;
HDR=Yes;
Database=C:\Presentations\Automating Excel Hell with T-SQL\#440 Pittsburgh\Sample Spreadsheet for Import 20140301.xlsx;'
,'SELECT * FROM [Sheet1$];'
)
;
You are missing a couple of semicolons but those don't appear to matter much. Notice that I've not had to make any special accommodations for file paths that contain many spaces. The only way that I can see what you wrote as not working is if the file or path doesn't actually exist as you have it.
Also notice that I didn't use "XML" in my code even though I'm reading from an "xlsx" file. It does work either way, though.
Thanks Jeff, this is very weird. I tried for a long time to get it to work and the file exists (I copied/pasted the name). May I ask the following:
1. What Excel version is your file? Mine is Excel 2010
2. What SQL Server version are you using? Mine is Sql Sever 2016 SP2
November 4, 2018 at 3:59 pm
Jeff Moden - Sunday, November 4, 2018 11:45 AMI've not been able to make it fail with the following code, which I've just tested on my box.
SELECT *
FROM OPENROWSET
(
'Microsoft.ACE.OLEDB.12.0'
,'Excel 12.0;
HDR=Yes;
Database=C:\Presentations\Automating Excel Hell with T-SQL\#440 Pittsburgh\Sample Spreadsheet for Import 20140301.xlsx;'
,'SELECT * FROM [Sheet1$];'
)
;You are missing a couple of semicolons but those don't appear to matter much. Notice that I've not had to make any special accommodations for file paths that contain many spaces. The only way that I can see what you wrote as not working is if the file or path doesn't actually exist as you have it.
Also notice that I didn't use "XML" in my code even though I'm reading from an "xlsx" file. It does work either way, though.
Thanks Jeff, this is very weird. I tried for a long time to get it to work and the file exists (I copied/pasted the name). May I ask the following:
1. What Excel version is your file? Mine is Excel 2010
2. What SQL Server version are you using? Mine is Sql Sever 2016 SP2
Mine is also Excel 2010 and I'm only using SQL Server 2008 (Developers Edition at home and it's not even "R2"). At work, I'm using SQL Server 2016 (Enterprise Edition) at SP2, as well.
I don't know if it matters on 2016 but I've never used it without all of the semicolons (you're missing some in your code). While that doesn't seem to matter on 2008, perhaps it does on 2016 (although I just can't imagine that because I'm using the very same driver).
There is a 2015 version of the ACE drivers out and it has seen a service pack. I've got it loaded on my 2016 box but haven't needed to make the switch and so I've not tried it (and can't get to it right now).
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2018 at 4:26 pm
Cant try it at the moment but in any case a word of advise.
ACE driver with its SP1 is the most complete at the moment if not using ACCESS specific functionality - the ACE runtimes posts this one do not have all functionality and as far as I know do not add any extra functionality that is normally required within SQL Server
November 4, 2018 at 4:34 pm
frederico_fonseca - Sunday, November 4, 2018 4:26 PMCant try it at the moment but in any case a word of advise.ACE driver with its SP1 is the most complete at the moment if not using ACCESS specific functionality - the ACE runtimes posts this one do not have all functionality and as far as I know do not add any extra functionality that is normally required within SQL Server
It DOES work well for the basic stuff like what is in the code both the OP and I posted. The OP is only having an issue when the path has spaces in it and I've not had such an issue and posted code that I use that does work correctly. While I thought the JET drivers were much more useful, I've not had a problem with the ACE drivers for typical spreadsheet loads.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2018 at 4:59 pm
Jeff Moden - Sunday, November 4, 2018 4:34 PMfrederico_fonseca - Sunday, November 4, 2018 4:26 PMCant try it at the moment but in any case a word of advise.ACE driver with its SP1 is the most complete at the moment if not using ACCESS specific functionality - the ACE runtimes posts this one do not have all functionality and as far as I know do not add any extra functionality that is normally required within SQL Server
It DOES work well for the basic stuff like what is in the code both the OP and I posted. The OP is only having an issue when the path has spaces in it and I've not had such an issue and posted code that I use that does work correctly. While I thought the JET drivers were much more useful, I've not had a problem with the ACE drivers for typical spreadsheet loads.
I just saw there is an ACE driver with SP2, Will try it out tomorrow and report the result. The ACE driver version I have may be too old, prior to SP1 I think.
November 4, 2018 at 5:25 pm
mr-967650 - Sunday, November 4, 2018 4:59 PMJeff Moden - Sunday, November 4, 2018 4:34 PMfrederico_fonseca - Sunday, November 4, 2018 4:26 PMCant try it at the moment but in any case a word of advise.ACE driver with its SP1 is the most complete at the moment if not using ACCESS specific functionality - the ACE runtimes posts this one do not have all functionality and as far as I know do not add any extra functionality that is normally required within SQL Server
It DOES work well for the basic stuff like what is in the code both the OP and I posted. The OP is only having an issue when the path has spaces in it and I've not had such an issue and posted code that I use that does work correctly. While I thought the JET drivers were much more useful, I've not had a problem with the ACE drivers for typical spreadsheet loads.
I just saw there is an ACE driver with SP2, Will try it out tomorrow and report the result. The ACE driver version I have may be too old, prior to SP1 I think.
That's probably not the problem because I'm using version 12 with no SPs and it works just fine. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2018 at 7:06 am
Jeff Moden - Sunday, November 4, 2018 5:25 PMmr-967650 - Sunday, November 4, 2018 4:59 PMJeff Moden - Sunday, November 4, 2018 4:34 PMfrederico_fonseca - Sunday, November 4, 2018 4:26 PMCant try it at the moment but in any case a word of advise.ACE driver with its SP1 is the most complete at the moment if not using ACCESS specific functionality - the ACE runtimes posts this one do not have all functionality and as far as I know do not add any extra functionality that is normally required within SQL Server
It DOES work well for the basic stuff like what is in the code both the OP and I posted. The OP is only having an issue when the path has spaces in it and I've not had such an issue and posted code that I use that does work correctly. While I thought the JET drivers were much more useful, I've not had a problem with the ACE drivers for typical spreadsheet loads.
I just saw there is an ACE driver with SP2, Will try it out tomorrow and report the result. The ACE driver version I have may be too old, prior to SP1 I think.
That's probably not the problem because I'm using version 12 with no SPs and it works just fine. 😉
hum. somehow my post didn't contain all that I wanted.
So.
ACE runtime 2010 SP1 - allows opening a excel file that is being used by others
ACE runtime 2016 - fails on the above situation.
Never tried it with ACE runtime 2013.
Apart from the above most of the functionality that is used within SQL Server is the same.
November 5, 2018 at 7:24 am
frederico_fonseca - Monday, November 5, 2018 7:06 AMJeff Moden - Sunday, November 4, 2018 5:25 PMmr-967650 - Sunday, November 4, 2018 4:59 PMJeff Moden - Sunday, November 4, 2018 4:34 PMfrederico_fonseca - Sunday, November 4, 2018 4:26 PMCant try it at the moment but in any case a word of advise.ACE driver with its SP1 is the most complete at the moment if not using ACCESS specific functionality - the ACE runtimes posts this one do not have all functionality and as far as I know do not add any extra functionality that is normally required within SQL Server
It DOES work well for the basic stuff like what is in the code both the OP and I posted. The OP is only having an issue when the path has spaces in it and I've not had such an issue and posted code that I use that does work correctly. While I thought the JET drivers were much more useful, I've not had a problem with the ACE drivers for typical spreadsheet loads.
I just saw there is an ACE driver with SP2, Will try it out tomorrow and report the result. The ACE driver version I have may be too old, prior to SP1 I think.
That's probably not the problem because I'm using version 12 with no SPs and it works just fine. 😉
hum. somehow my post didn't contain all that I wanted.
So.
ACE runtime 2010 SP1 - allows opening a excel file that is being used by others
ACE runtime 2016 - fails on the above situation.Never tried it with ACE runtime 2013.
Apart from the above most of the functionality that is used within SQL Server is the same.
That is good stuff to know. Thanks, Frederico.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2018 at 8:08 am
frederico_fonseca - Monday, November 5, 2018 7:06 AMJeff Moden - Sunday, November 4, 2018 5:25 PMmr-967650 - Sunday, November 4, 2018 4:59 PMJeff Moden - Sunday, November 4, 2018 4:34 PMfrederico_fonseca - Sunday, November 4, 2018 4:26 PMCant try it at the moment but in any case a word of advise.ACE driver with its SP1 is the most complete at the moment if not using ACCESS specific functionality - the ACE runtimes posts this one do not have all functionality and as far as I know do not add any extra functionality that is normally required within SQL Server
It DOES work well for the basic stuff like what is in the code both the OP and I posted. The OP is only having an issue when the path has spaces in it and I've not had such an issue and posted code that I use that does work correctly. While I thought the JET drivers were much more useful, I've not had a problem with the ACE drivers for typical spreadsheet loads.
I just saw there is an ACE driver with SP2, Will try it out tomorrow and report the result. The ACE driver version I have may be too old, prior to SP1 I think.
That's probably not the problem because I'm using version 12 with no SPs and it works just fine. 😉
hum. somehow my post didn't contain all that I wanted.
So.
ACE runtime 2010 SP1 - allows opening a excel file that is being used by others
ACE runtime 2016 - fails on the above situation.Never tried it with ACE runtime 2013.
Apart from the above most of the functionality that is used within SQL Server is the same.
Hi Jeff,
Thank you for posting this useful info.
I tried today using your syntax above (only changed the path) and it does not work: I am getting the same error:
The Microsoft Access database engine could not find the object 'data$'
If I remove the spaces, it works.
I am using ACE runtime 2010 SP2 (you use SP1)
I am running the query on a Windows Server 2016 SP2 (yours is SQL Server 2008)
Not sure why you cannot reproduce the error, it may be because our environments and drivers are different.
November 5, 2018 at 8:30 am
mr-967650 - Monday, November 5, 2018 8:08 AMfrederico_fonseca - Monday, November 5, 2018 7:06 AMJeff Moden - Sunday, November 4, 2018 5:25 PMmr-967650 - Sunday, November 4, 2018 4:59 PMJeff Moden - Sunday, November 4, 2018 4:34 PMfrederico_fonseca - Sunday, November 4, 2018 4:26 PMCant try it at the moment but in any case a word of advise.ACE driver with its SP1 is the most complete at the moment if not using ACCESS specific functionality - the ACE runtimes posts this one do not have all functionality and as far as I know do not add any extra functionality that is normally required within SQL Server
It DOES work well for the basic stuff like what is in the code both the OP and I posted. The OP is only having an issue when the path has spaces in it and I've not had such an issue and posted code that I use that does work correctly. While I thought the JET drivers were much more useful, I've not had a problem with the ACE drivers for typical spreadsheet loads.
I just saw there is an ACE driver with SP2, Will try it out tomorrow and report the result. The ACE driver version I have may be too old, prior to SP1 I think.
That's probably not the problem because I'm using version 12 with no SPs and it works just fine. 😉
hum. somehow my post didn't contain all that I wanted.
So.
ACE runtime 2010 SP1 - allows opening a excel file that is being used by others
ACE runtime 2016 - fails on the above situation.Never tried it with ACE runtime 2013.
Apart from the above most of the functionality that is used within SQL Server is the same.
Hi Jeff,
Thank you for posting this useful info.I tried today using your syntax above (only changed the path) and it does not work: I am getting the same error:
The Microsoft Access database engine could not find the object 'data$'
If I remove the spaces, it works.
I am using ACE runtime 2010 SP2 (you use SP1)
I am running the query on a Windows Server 2016 SP2 (yours is SQL Server 2008)
Not sure why you cannot reproduce the error, it may be because our environments and drivers are different.
That was Frederico that posted that latest information.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply