Deleting rows from a datable

Help Studio
1

Hi all,

Something that my teams have always enjoyed doing in the past is trying to remove a datatable row based on an list of values, but this has always been tricky as you can’t remove the datarow whilst iterating through the datatable. However, today I found a neat way of doing this…

Read in your list of values to check in the datatable - e.g. list of unique reference numbers URNs

Create a for each loop to loop through your newly read list of URNS (either datatable, or list doesn’t really matter)

Use the Lookup Data Table activity to lookup the URN in the matching column in the datable and return the row index.

Now you have the row Index simply grab a remove row activity and use that row Index to delete the row. Because you are looping through the list of URNs and not the target datatable, you are able to remove this row with no issues.

Maybe not the most resource effective way to do this, but by far the simplest.

Hope this helps.

RD

1 Like
2

@richarddenton
Another technique is to use LINQ

on the beginning starts with a projection (Select) and a indexed statement on e.g. keyValuePair or Tuple

dtData.AsEnumerable.Select(Function (r,index) new Tuple(Of Int32, DataRow)(index, r)
Then later in the method chain the filter can be implmented like
…Where(Function (t) t.Item2(“ColName”).toString …Condition…And …)

As we still transporting the tuple we have the index info in place

Finaly we select the index from the filtered elements
…Select(Function (t) t.Item1).toList

And retrieve a List of integers with the indexes on we want to work

So in the Single Line it is producing the IndexList:
dtData.Asenumerable.Select (Function (r,index …).Where(…).Select(…).toList
For sure we also can use the Query Syntax of Linq for this

Reversing the list with ListIndexVar.Reverse can be used in a for each
And with a Delete Row Activity the item from for each is used for removing the rows

As we haved reversed the index list we dont have to take care about changed indexes in the datatable because of Deletion

2 Likes
3

Thanks, and yeah I get this, but I think there are a lot of people out there who wouldn’t be comfortable using LINQ. I have an SQL background, and always found LINQ a very ugly syntax to work with. Hence, I wanted to share what I think is the easiest approach, not necessarily the best.

Having said that - I think this feed is now a very nice source for people to have two methods of removing rows from a datatable, depending on which they’re most comfortable with.