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

Help Studio
, ,
1

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. ?

2

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

3

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?

4

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

5

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)
Order By i DESCENDING
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
6

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

7

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