Excel Through ISQLW - 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 2000 Forums

Transact-SQL (2000)

Excel Through ISQLW

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-26 : 02:15:17

Dear All

I Need to acces the Excel Sheet through ISQLW

I Used the Following Code

sp_addlinkedserver N'KKExcel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0',
N'c:\Sheet.xls', NULL, N'Excel 5.0'
GO
sp_addlinkedsrvlogin N'KKExcel', false, sa, N'ADMIN', NULL
GO

SELECT *
FROM EXCEL...Sheet
GO

But It returns The Follwoing Error

Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider 'EXCEL' does not contain table 'Sheet1'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='EXCEL', TableName='Sheet1'].

Any One Please Help Me

Thanks
KK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-26 : 04:28:03

What is the sheet name of the excel file?
Make sure you spelled it correctly
Also read this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-26 : 05:38:24

Sheet Name Sheet1
I didn't rename

Thanks
KK

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-26 : 05:39:02

Sheet Name Sheet1
I didn't rename

Thanks
KK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-26 : 05:55:33

Then try

SELECT *
FROM EXCEL...Sheet1$

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-26 : 06:00:07

I am using Testing.xls
the sheet name is sheet1
the column name is cola

But when I executing the query

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM EXCEL...sheet1')
I am getting the following error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

Thanks
KK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-26 : 06:06:50

Read my previous reply. The sheet name should be suffixed by $
Use sheet1$

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-26 : 06:10:57

The same Error was thrown

Thanks
KK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-26 : 06:26:54

Did you try this?

SELECT *
FROM EXCEL...[Sheet1$]

Madhivanan

Failing to plan is Planning to fail