how to delete from excel sheet throught SQL query

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)

how to delete from excel sheet throught SQL query

Author

Topic

Rammi
Starting Member

19 Posts

Posted - 2007-08-02 : 01:53:42

Please help me that how can i delete excel rows from database through a SQL query.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-08-02 : 02:02:03

you can create a linked server from sql server to your excel spreadsheet. this would allow you to query using standard sql. here is a microsoft kb article detailing how this can be done http://support.microsoft.com/kb/306397

-ec

Rammi
Starting Member

19 Posts

Posted - 2007-08-02 : 03:11:11

I try both the ways by making linked server and use openrowset also. But unable to delete rows from excel sheet.

Query by making "EXCEL" as linked server.
delete FROM EXCEL...Sheet1$

or

delete FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test.xls;HDR=YES','SELECT * FROM [Sheet1$]')

In both cases i got the following error.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL" returned message "Deleting data in a linked table is not supported by this ISAM.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "DELETE FROM `Sheet1$` " against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 03:18:52

quote:
Originally posted by Peso

The OPENROWSET syntax with Jet provider does not give you full error description when problem occurs, such as permission errors.
-- Using this code for a file with no appropriate permissions throws a general error
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;HDR=No;IMEX=0;Database=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls', 'select * from [Sheet1$a1:q50]')
If you have some error and you don't understand why, try using MSDASQL provider temporarily.
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls',
'SELECT * FROM [Sheet1$]')
Now most error desriptions are output. When you have resolved the error, switch back to Jet again.

E 12°55'05.25"
N 56°04'39.16"

Rammi
Starting Member

19 Posts

Posted - 2007-08-02 : 03:31:55

The SELECT operations are working fine but when using DELETE operations it gives the errors.

Even using the MSDASQL in the query the following error is generated

Query:
DELETE OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\test.xls','SELECT * FROM [Sheet1$]')

Error:
Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "(null)" does not support the required transaction interface.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 03:55:34

I see you have the path "c:\test.xls". I believe that is the path on your local machine, right?
Please remember that the path to the excel file is RELATIVE TO THE SQL SERVER machine!

DELETE OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\mylocalmachinename\mypath\test.xls','SELECT * FROM [Sheet1$]')

Moderator, please move this topic to a proper forum.

E 12°55'05.25"
N 56°04'39.16"

Rammi
Starting Member

19 Posts

Posted - 2007-08-02 : 04:04:45

Yes, Peso you are true but i am using the same machine on which my SQL SERVER is there. I run the query by using the local machine path also but It ends up in same error.

Rammi
Starting Member

19 Posts

Posted - 2007-08-02 : 04:05:18

Yes, Peso you are true but i am using the same machine on which my SQL SERVER is there. I run the query by using the local machine path also but It ends up in same error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-02 : 04:20:27

It might be addressed somewhere here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Rammi
Starting Member

19 Posts

Posted - 2007-08-02 : 05:33:26

in the article it is written that "You are more restricted in deleting Excel data than data from a relational data source."

Will it be possible to update the excel

I use the following query to update the excel

update OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\test.xls;hdr=no','SELECT * FROM [Sheet1]') set a = 0

and got the following error

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-02 : 08:25:56

Make sure the file exists in the SERVER directory and first row has column names

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 08:59:41

Also, write [Sheet1$] not [Sheet1].

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-02 : 09:07:26

moved from script library

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Rammi
Starting Member

19 Posts

Posted - 2007-08-03 : 01:51:11

I try the following one also
Query:
update OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\test.xls;hdr=no','SELECT * FROM [Sheet1$]') set id = 0

Error:
Msg 207, Level 16, State 1, Line 0
Invalid column name 'id'.

In this the column name is correct and sheet is also present at the desired location. But still the error is coming

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-03 : 02:58:14

So you want to update id column of EXCEL sheet to 0?

Madhivanan

Failing to plan is Planning to fail

Rammi
Starting Member

19 Posts

Posted - 2007-08-03 : 03:13:45

Yes, If possible i want to set the value to blank in the sheet except ID (Column Name)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-03 : 03:34:33

SET ID = ''

E 12°55'05.25"
N 56°04'39.16"

Rammi
Starting Member

19 Posts

Posted - 2007-08-03 : 03:52:56

Hi Peso i tried this but it throws the error
Error:
Msg 207, Level 16, State 1, Line 0
Invalid column name 'id'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-03 : 03:58:06

It doesn't seems you have a column header named ID, not a named range named as ID.

E 12°55'05.25"
N 56°04'39.16"

Rammi
Starting Member

19 Posts

Posted - 2007-08-03 : 05:11:51

I have following data in my excelsheet with column header name "id" present in c drive.

id
20722
20444
20434
33444
12456

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-03 : 06:02:44

quote:
Originally posted by Rammi

I have following data in my excelsheet with column header name "id" present in c drive.

id
20722
20444
20434
33444
12456

When you do Select query on EXCEL files, what are the column names displayed in the Query Analyser?

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources