| Author |
 Topic  |
|
|
hungrytom
Starting Member
6 Posts |
 Posted - 07/19/2010 : 11:14:29
|
Hi!
I want to use openrowset to read a table or two from an excel file..
Im getting this "Could not find installable ISAM" error and after trawling the forums for days I haven't found any solutions that work for me..
Here's my query:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 14.0;Database=E:\Project Data\339_IMP\Code_labels_latest_Sep09_breakdown.xls', 'SELECT * FROM [loftins$]')
And here's the error message:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
I am running SQL Server 2008 r2 64-bit on Windows Server 2008 64-bit. I have installed Office 2010 and AccessDatabaseEngine_X64.exe available from
http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
I have tried reinstalling the ACE drivers and restarting the server. I am now slowly losing the will to live..
Please can someone help me? Any suggestions welcome..
Cheers,
Tom
|
|
|
deux-ex-machine
Starting Member
Portugal
1 Posts |
Posted - 07/31/2010 : 08:32:30
|
Hi,
Please use this instead:
SELECT * FROM OPENROWSET('MSDASQL','DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 12.0;DriverId=1046;DefaultDir=E:\Project Data\339_IMP;DBQ=E:\Project Data\339_IMP\Code_labels_latest_Sep09_breakdown.xls', 'SELECT * FROM [loftins$]')
That should do the job
Bye |
 |
|
|
mrblackbat
Starting Member
United Kingdom
1 Posts |
Posted - 09/07/2010 : 05:29:08
|
I encountered the same issue this morning; the OPENROWSET command seems to work if you change the version of excel to 12.0 rather than 14.0.
So in your case it would be:-
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\Project Data\339_IMP\Code_labels_latest_Sep09_breakdown.xls', 'SELECT * FROM [loftins$]')
|
|
|
|
sonushah81
Starting Member
USA
1 Posts |
Posted - 07/07/2011 : 11:43:39
|
Hi
@deux-ex-machine
I tried to run using both 14 and 12 no luck.
SELECT *
INTO #test_excel
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 14.0;Database = D:\Tom_CustomSQL\Dtrak.xlsx','SELECT * FROM [Sheet1$]')
error is
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
SELECT *
INTO #test_excel
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database = D:\Tom_CustomSQL\Dtrak.xlsx','SELECT * FROM [Sheet1$]')
Error is
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Invalid argument.".Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Ashah
SQL DBA
Boston |
|
|
|
hungrytom
Starting Member
6 Posts |
Posted - 07/07/2011 : 12:27:47
|
Hi Ashah,
The following works on our system (64-bit SQL 2008 R2, MS Office 2010):
SELECT * INTO tbl
FROM OPENROWSET('MSDASQL','DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 12.0;DriverId=1046;DefaultDir=E:\Project Data;DBQ=E:\Project Data\example.xls', 'SELECT * FROM [Sheet2$]')
HTH
Tom |
|
|
|
I_Mar
Starting Member
1 Posts |
Posted - 10/04/2011 : 08:43:02
|
Hi guys
I have exactly the same set up as Tom
"I am running SQL Server 2008 r2 64-bit on Windows Server 2008 64-bit. I have installed Office 2010 (only Excel) and AccessDatabaseEngine_X64.exe "
I tried using
SELECT * FROM OPENROWSET('MSDASQL','DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 12.0;DriverId=1046;DefaultDir=E:\Project Data\339_IMP;DBQ=E:\Project Data\339_IMP\Code_labels_latest_Sep09_breakdown.xls', 'SELECT * FROM [loftins$]')
as it was the solution suggested but now I am getting:
"OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Procedure spImportTempFrameworksLoad, Line 18
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)"."
Any help would be really appreciated!!
Thank you
Marios |
|
|
| |
Topic |
|
|
|
64bit ACE 12.0 - "Could not find installable ISAM"
