import excel file

Question

hi

i have a excel file and use below code for retrive

SELECT * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 10.0;Database=E:\Bakhi\Transfare\newTTMS\Ostan.xls;IMEX=1;HDR=YES',
'select * from [vZones_TTMS]')

but i get this error :

OLE DB provider "Microsoft.Jet.OLEDB.4.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.Jet.OLEDB.4.0" for linked server "(null)".

please help me

thanks 

Answer 1

Hello,

Are you using a 64 bit machine? The JET data provider is not available for 64 bit, only for 32 bit. You have to use the new ACE instead, see http://www.microsoft.com/en-us/download/details.aspx?id=13255

---

Olaf Helper

Blog Xing

Answer 2

Check this out:

 

select * FROM OPENROWSET(

  'Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;Database=C:\Users\Excel\Desktop\Book1.xls;HDR=YES',

  'SELECT * FROM [Sheet1$]')

 

 

 

 

SELECT *  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;HDR=YES;IMEX=1;Database=C:\Users\Excel\Desktop\OptionTradingWorkbook.xls;HDR=YES',

    'SELECT * FROM [OptionPage$]');

 

 

 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

 

--Example of tsql

SELECT *  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;HDR=YES;IMEX=1;Database=C:\Users\Excel\Desktop\OptionTradingWorkbook.xls;HDR=YES',

    'SELECT * FROM [OptionPage$]');

 

 

 

 

 

I changed the 'AdHocRemoteQueriesEnabled' setting in my SQL Server 2012, like recommended in this link:

http://colinmackay.co.uk/2011/08/12/running-queries-on-excel-spreadsheets-using-sql-server-2008/

 

 

 

I followed a few more samples here:

http://go4answers.webhost4life.com/Example/import-excel-file-sql-server-64-bit-157244.aspx

 

 

 

I downloaded and installed the AccessDatabaseEngine.exe from here:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

 

 

 

---

Ryan Shuell