For Each Row in DataTable taking over an hour to run

[T.S]

My datatable has about 53000 rows and 13 columns. One of the columns is an ID and I would like to remove invalid IDs and remain with valid IDs that have 8 string characters.
Manually, I have determined that the invalid IDs are about 200.
How can I loop through the rows to find and remove these invalid IDs in the shortest time possible?
I want to remain with the valid IDs in a datatable so I can perform other actions on them. The invalid IDs will be emailed to a system admin for remediation.

Thanks for the help

[supermanPunch (Arpan)]

@T.S

How can I loop through the rows to find and remove these invalid IDs in the shortest time possible?

Hi @T.S ,

Could you let us know if you would want to Remove/Update the ID column value or remove the whole row consisting of that invalid ID ?

[T.S]

I would like to remove the whole row consisting of the invalid ID.

[copy_writes (chethan)]

HI @T.S

You can use LINQ Quirey to remove it, If you want to read more than 10,000 rows I recommend using LINQ Quirey.

If you need the LINQ Quirey please provide sample input so we can help you.

Thanks,
Chethan P

[supermanPunch (Arpan)]

@T.S

valid IDs that have 8 string characters.

@T.S ,

In that case, maybe we can check with the below Expression on finding the Invalid IDs :

invalidIds = DT.AsEnumerable.Where(Function(x)Not(x("ID Column").ToString.Trim.Length=8)).Select(Function(x)x("ID Column").ToString).ToArray

Here, invalidIds is a variable of type Array of String and DT is your Input Datatable variable.

For Removing the Invalid ID Rows in DT, We can check with the below :

UpdatedDT = DT.AsEnumerable.Where(Function(x)x("ID Column").ToString.Trim.Length=8).CopyToDatatable

Here, UpdatedDT is of the type Datatable. You could use the same datatable as well.

[T.S]

Sample input will look like the image.
I am only interested in the rows where column 13 has only 8 characters.

Thanks

[ppr (Peter Preuss)]

We do feel that with a combination of Regex / StringCheck and LINQ we can do like

Assign Activity (valid Rows DT)
dtValidRows =

(From d In yourDataTableVar.AsEnumerable()
Where Not isNothing(d("YourColName"))
Let n = d("YourColName").toString.Trim
Where Not String.IsNullOrEmpty(n)
Where n.Length.Equals(8) 
Where System.Text.RegularExpressions.Regex.isMatch(n,"[A-Za-z0-9]+")
Select r = d).CopyToDataTable

Assign Activity (invalid Rows DT)
dtInvalidRows =

yourDataTableVar.AsEnumerable.Except(dtValidRows.AsEnumerable, DataRowComparer.Default ).CopyToDataTable

[ppr (Peter Preuss)]

@T.S

13 has only 8 characters.

Yes, but letter and digit only

we adapted to:

@ppr

Where System.Text.RegularExpressions.Regex.isMatch(n,"[A-Za-z0-9]+")

[T.S]

I am still new to studio, here are the errors I get

[ppr (Peter Preuss)]

we corrected the LINQ as a closing bracket was missplaced
Provided LINQ is in VB.Net Language

Could it be the case that your project is set to C#?

[T.S]

My project is in VB.Net.
The LINQ works however it outputs only one record, yet I am expecting over 50,000.
How can I remedy this?

[ppr (Peter Preuss)]

Can you share some details about the processed data e.g. Excel / CSV or a screenshot from the immediate panel?

Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum