Export from Excel to SQL-2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Export from Excel to SQL-2005

Author

Topic

RohitGoyal2002
Starting Member

6 Posts

Posted - 2009-01-18 : 23:26:05

Hi i am using the following query to export data to SQL and getting the error. Can Somebody help me in resolving the error
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

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)".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 02:31:41

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q318161
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q326548

doco
Yak Posting Veteran

77 Posts

Posted - 2009-07-11 : 08:01:21

The real problem here is how does the server know where 'C:\' is? Typically the user is on a client machine and SQL Server, well, is on a server remote to the client. How then is the file path to be designated? In my case the server is 110 miles away. Of course miles or meters matters little. 'C:\' in the example given is relative to wherever SQL Server is installed.

Education is what you have after you've forgotten everything you learned in school

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-11 : 08:51:03

Also refer this for troubleshooting more errors
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

doco
Yak Posting Veteran

77 Posts

Posted - 2009-07-12 : 09:28:18

[code]
Public Sub ALTERNATE()

Dim cn As ADODB.Connection
Dim sql As String
On Error GoTo EH

Set cn = New ADODB.Connection
sql = "select * into DocoDBAMain..test_fund_dist FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
sql = sql & "'Excel8.0;Database=C:\local_workspace\excel\FundDist.xls;HDR=YES', "
sql = sql & "'SELECT * FROM [Sheet2$]')"
cn.Open "DSN=DOCO", "Donald R. Cossitt", ""

cn.Execute sql

TheExit:

cn.Close
Set cn = Nothing
On Error GoTo 0
Exit Sub
EH:
Debug.Print Err.Description
GoTo TheExit

End Sub
[/code]

I have set up a test db using 2008 express. I realize this is a TSQL 2005 forum but I think the post on topic. Anyway running the VB code above produces the error below.

quote:

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".

Not only on this machine and version of SQL Server but all machines and versions of SQL and Excel ( 2000, 2003, 2007 ) I have attempted. I have searched all over the internet trying to resolve the error to no avail. I am thinking it is because the server has no concept of 'C:\' or at the root I am referring to.

Ideas?

Education is what you have after you've forgotten everything you learned in school

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-07-12 : 21:50:27

A couple quick things to check.
1. Make sure your document is closed when you try (If open you will get that error).
2. The database location of "D:\..." will be on whatever machine is running the sql service, it is not client dependent. Make sure the file exists on the machie running SQL.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources