How to delete rows from an excel file based on a certain Condition

Hi Team,
I have a data in an excel file and want to delete some rows (complete row) based on a column condition “Column1” = “Delete this row” . I did it with dataTable and when tried to add new filtered Datatable, the Formula present in few columns are removed. Can I remove rows directly from excel instead of converting it to DT and then writing back again in excel. ?

one of alternate options can be

  • read range
  • calculate the index of the rows, which are to delete
  • loop over the index list and delete row with insert/Delete Rows activity

ensure the index list is ordered descending and starts with the highest index

Hi @ppr ,
To calculate index of rows to delete , Do I would have to add foreach in the excel on entire data (which would be time consuming if data is large)and check if condition satisfies . Or is there any quick way to get all the index of such rows in less time?

you can use filter datatable activity for remove or keep based on column value

as mentioned we calculate it on the datatable level for the excel e.g. with a LINQ

Assign Activity:
arrDelIndex | array of Int32 =

(From i in Enumerable.Range(0, YourDataTableVar.Rows.Count)
let v = YourDataTableVar.Rows(i)(ColNameOrIndex).toString.Trim
Where v.Equals(YourConditionValue)
Select x=i+Offset).toArray

we correct the index to the position which we will use later in excel

  • 1 as datatable row index is 0based and Excel is 1based
    And additonal +1 when readrange was done with addHeaders = true

Thank you so much @ppr . It worked. :slight_smile:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.