Sql query to delete the excel sheet data

I have connected excel as a DB, now using the SQL Query how can I delete the sheet1 data.
@Palaniyappan

Fine
if this is done
image

then we can EXECUTE NON QUERY activity with typical DELETE STATEMENT
and delete the record we want

Cheers @balkishan

1 Like

Not records bro, complete sheet data

is it not easier to just delete the sheet and make a new one?

1 Like

I have used excel as a db, and execute the query to get the particular sheet data, and get the maximum numbers of row from the data sheet.

So can you tell me using the Max numbers of row to delete the data using query only.

Approach.
image

I don’t want to delete the sheet. If I delete the Sheet and create new one then it will change the Sheet order. So this is the reason I don’t want to delete and re-creation sheet. Bcz I have multiple sheet in the same excel.

delete from [Sheet1$];

you can also add where condition if needed

1 Like

Got this error.
image

I want to delete the complete sheet data.

1 Like

OK, I looked it up and apparently:

OLE DB provider only allows to insert or update records to excel sheet. It does NOT allow delete operations.

So If you want to delete the data. I would suggest tot read the excel using read range activity and delete teh data using delete range activity.

Or if you realy want ot use sql, update all values to null

2 Likes

yah @tdhaene @balkishan
OLE DB - with microsoft access has that constraint i hope
so we can directly use INSERT/DELETE or REMOVE DATAROW activity

Cheers

1 Like

This method I tried, since I have huge data so it takes hour to delete the data. I want quick solution to delete the entire sheet data.

Quick and dirty:
create macro that does this action and invoke macro.
Make sure that you excel settings are not set on calculate automatic if you have a lot of formulas and links

1 Like

Just tried this solution.

And in RowIndex what we need to put.

1 Like

Fine
in the properties of REMOVE DATA ROW
mention the position as 2
and no.of rows as yourdatatable.Rows.Count
and choose option as DELETE

Cheers @balkishan

1 Like

This can be possible

I am using this activity bro.

remove datarow just deletes a row from a datatable (which is stored in memory).
I don’t understand why this would helping you in cleaning data from your excel sheet

fine i though INSERT/DELETE activity
if its remove datarow, it wil delete single row at a time, which would be slower
so i would suggest to go INSERT/DELETE activity that would delete the whole excel with number of records we mention
Cheers @balkishan

1 Like

2 should be set in rowindex not in row

This method already applied bro, since it’s not working.
This activity work only inside the Excel Scope.

I have used Excel as a Database.

Fine
if our intention is to remove all the records in the existing excel unless we use any query to do that , i hope we dont need execute query here as of now.
we can use INSERT/DELETE activity and ofcourse inside application scope

Kindly correct me if i m wrong with the question
may be it could be possible with some other method but this came to mind suddenly when thinking of deleting all the records in the existing excel to retain the same template
Cheers @balkishan

1 Like