Import Excel to SQL

[k3lvin]

Hey there,

I am trying to import some data with in Excel to SQL. I am a novice to SQL. Using Microsoft SQL Server Management Studio.

Running the code below:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\KPI\test.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...['KPI$']

Gives the following 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 can somebody help, thanks!

 

[MartinCroft]

Dont think you need a password if you are running from SQL.

This works on my local instance

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0', 

 'Data Source=c:\dump\test.xls; extended Properties=Excel 8.0')...sheet1$

 

[k3lvin]

Hey thanks for the reply.

I have using your exact syntax and still getting the error.

Code:
SELECT * FROM OPENDATASOURCE 'Microsoft.Jet.OleDB.4.0', 'Data Source=C:\KPI\test.xls; extended Properties=Excel 8.0' )...KPI$

Error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OleDB.4.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 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OleDB.4.0" for linked server "(null)".

What am I doing wrong!

 

[gmmastros]

Do you have the file open in Excel at the same time you are trying to import to SQL? If so, close Excel first.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[k3lvin]

I think I did have the file open after using your code. Your code works fine now! Thank you so much for your help, really appreciate it.