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.