OPENROWSET error accessing XLSX if not manually saved

Question

OPENROWSET error accessing XLSX if not manually saved

Raynard_SwanXI

SSC Veteran

Points: 296
More actions
September 11, 2018 at 2:55 am

#378898

Good day,
Apologies if this is in the wrong forum. Please refer me to the correct one if this is the case, thank you.
I have a bunch of excel files that are added to a Dropbox folder on a monthly basis. These files should then be imported to a SQL Server Database.
My problem is that, when I try and import data from SOME of these XLSX files, OPENROWSET gives me the following error:
Msg 7399, Level 16, State 1, Line 14 The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 14 Cannot initialize the data source object of OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)".
This is a general error message and I was not able to find a solution related to my problem in another thread.
Once I open a file and SAVE it MANUALLY, then OPENROWSET SUCCESSFULLY reads the selected range from the XLSX file.
I think that means OPENROWSET has some kind of access or permition restriction to these files, but I do not know why as other files with the same format does not produce this error.
Is there a way to give OPENROWSET power to read these files even if they have not been manually saved? Any alternate suggestion on how to overcome this problem in SQL will be much appreciated.
Thanks,
Raynard

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

This suggests that the files with the problem were generated "auto-magically", by whomever provides them. Until you open them with Excel and then re-save them, you find it impossible to automatically open them. There's no fixing that. When people provide you an Excel file, it has to be "right", and there's not a darn thing you can do to stop that, save automate a workstation with a VBScript that will automatically open that DropBox folder and then open and re-save every Excel file that it finds there, on some kind of regular basis. That's one of the many reasons that Excel is NOT such a good tool to transfer data with.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Raynard_SwanXI SSC Veteran Points: 296 More actions · Answer 2

Thank you sgmunson for clearing that up.

I have written a VB script that successfully opens and saves all the xlsx and xlsm files in that folder (xls does not seem to work the same way, still need to figure this out.)

I understand that Excel is not a great way to transfer data but the contractor providing our firm with their performance data, automatically generates (as you suggested) these reports each month and then give them to us. They are not willing to give us access to their raw data hence we are attempting this workaround.

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Raynard_SwanXI - Wednesday, September 12, 2018 1:41 AM
Thank you sgmunson for clearing that up.
I have written a VB script that successfully opens and saves all the xlsx and xlsm files in that folder (xls does not seem to work the same way, still need to figure this out.)
I understand that Excel is not a great way to transfer data but the contractor providing our firm with their performance data, automatically generates (as you suggested) these reports each month and then give them to us. They are not willing to give us access to their raw data hence we are attempting this workaround.

Yep, ... I get it... some things we just can't avoid. Well done on the VBScript. It usually requires a familiarity with both VBScript and the Excel object model that many folks with SQL background often don't have. Although Google-fu on that topic would likely reveal mostly ready code examples...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Raynard_SwanXI SSC Veteran Points: 296 More actions · Answer 4

Thanks once again Steve. Fortunately I have more experience with Visual Basic than SQL so that was not to difficult (with the help of Google of course). I marked your initial reply as the answer.

Regards,
Raynard