Remove rows from datatable that match MULTIPLE conditions

Help
1

I have a dataTable dt_Sample, and there are rows I need to remove from here. I used activity: Remove Data Row inside if statement but it returned an error “Collection was modified. Enumeration operation may not execute”.

It seems like I have to use Select function, but I don’t know if I can do that in my situation. There are multiple conditions I need to consider when removing the rows from the datatable. All data are in string. The column name is “SampleString”

  1. The data ends with “-123”, AND 2) The length of the string is 9
    OR
  2. The data ends with “-456”. AND 2) The length of the string is 14

In either case, the row needs to be removed from the table. Using if statement on this is easy, but I am not sure I can do this with Select function. Column name is specified, but I have to check if the data ends/starts with a certain value, and I also have to check the length of the string.

How would you go about this?

2

Hi @tomato25
Bussy kindly follow the below steps that could help you to resolve this issue

  1. Yes of course we can get the filtered datatble with select method like this
    **Out_dt.Select("[YourColumnName] like ‘%-123%’ or [YourColumnName] like ‘%-456%’ ")
  2. the output of the select method is array of Datarows…named out_datarows
    So use a for each loop and pass the above variable out_datarows as input and change the type argument as datarow in for each loop activity property
  3. Inside this use a if condition like this
    Item(“SampleString”). ToString.Length=9 and Item(“SampleString”). ToString.EndsWith(“-123”)
    If this condition gets passed it will go to THEN part of if condition where we can use the remove datarow activity and mention the datatable as out_dt and datarow as item
    Or if this condition fails it will go to ELSE part of if condition where we can mention with another if condition with condition like this
    Item(“SampleString”). ToString.Length=14 and Item(“SampleString”). ToString.EndsWith(“-456”)
    And here if condition gets passed it will go to THEN part of if condition where we can use remove datarow activity and do the same… Buddy
    Now the rows would be removed and we get filtered data
    Hope this would help you buddy
    Cheers @tomato25
1 Like
3

I am a bit confused… You are filtering twice???
In #1, you are using select function and filtering a datatable by %123%, and you are using similar condition inside if-statement??

4

Here is a good answer of how to do it with Lambda/LINQ. sql - Datatable.Select with Like in VB.net - Stack Overflow

Do the linq in an assign activity of type List and then in a separate For Each activity (of type datarow) use the delete datarow activity

1 Like
5

This is weird… Your method seemed to work at first, but it is somehow removing only some rows only…

My excel sheet from which I retrieve the DataTable has 500+ rows, but only the first 1/3 of the rows that match the condition are removed… and the remaining still stays in the excel sheet. I dont know why…

6

Yes of course
The first one with select filters the tows based on -124 and -456
The we need to check with length along with -123 and -456 right
Kindly correct me if I have understood the question wrongly
Cheers @tomato25

1 Like
7

Well we can even use filter datatable Activity were we can first filter the data based on -123 and -456 contained row values in a column
–we can mention the input datatable
And columnname and Condition as Ends with and value as “-123”
Use a OR condition inbetween a Next condition where mention like Ends With “-456”
Cheers @tomato25

1 Like
8

Once aftrr getting the output datatable named finaldt from filter datatable activity, pass the input as finaldt.Rows in for each loop activity and do the same procedure inside the for each loop…
Cheers @tomato25

1 Like
9

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