Update large rows of datatable with values

Hi Devs, Pls I have a datatable of 400000 rows and I am updating confirm column with “open” but the bot always get stuck at the for each activity and doesn’t pass the activity for over 4 hrs now, please what could be the issue and a better approach to handle this update.Regards

@RPA-botDev

May be Try as below

Use Invoke Code write as below

dta.Select().ToList().ForEach(Sub(row) row(“ColumnName”)= “Open”)

Where dta is your datatable variable

Click Edit Arguments and pass your datatable dta as In/out direction

Hope this may help you

thanks

Please which method is fastest to enable processing easily

Hi @RPA-botDev,

First delete the column you want to change. Then add another column with the same name and mark the default value as “Open”.

Regards,
MY

Hi anyone here to help out on implementation, I discovered the issue was because I was the code was inside a for each activity so for it to update the column it has to first loop through the rows in parent for each but is there a better way to this please. See screenshot below, it’s taking so much time

can you please share the entire statement from the if condition? Thanks

newdt.AsEnumerable.Where(Function(obj) obj(“N”).ToString.Contains(row(“N”).ToString)).Count > 0 AndAlso newdt.AsEnumerable.Where(Function(obj) obj(“AMT”).ToString.Equals(row(“AMT”).ToString)).Count > 0

Deleting and recreating the column didn’t help?

this is the then action

when a for each row (and in your case also a nested one) is taking too much time we are looking for triggers representing processing work. Here we do have it with the where, as this statement is looping / filtering over all data again and again.

From the logic it looks like it is checking:

  • if there is any newDT row where col value has a contains of current loop row col value
  • check is done for 2 cols

this type of processing has a potential to get optimized. We do feel that it would be the best that you will share some reduced sample data (but reflecting all scenarios) along with the expected output and we will have a look on it. Thanks

1 Like

Take a look at the excel for sample dt and expected dt, so it’s checking if rows(“n”) in and rows(“amt”) in dt same value in newdt if true assign unmatch to column “check” in newdt

checkpp.xlsx (10.5 KB)

no it didn’t, I had long iteration due to the for each

look like you want to check if newdt row n & AMT value is present in any row from dt and update the status col (from newdt) to unmatch, rigth?

Exactly, that’s what I want to achieve, pls help

as written in the linked blog: Strategy is about filter the affected row and do update within for each row. Give a try:

Assign Activity
LHS: DRupdateList | List(Of DataRow
RHS:

(from d in newdt.AsEnumerable
Where dt.AsEnumerable.Any(Function (x) d("N").ToString.Contains(x("N").toString) AndAlso d("AMT").ToString.Contains(x("AMT").toString)
Select r=d).toList

then use a:
For each activity | item in DRupdateList | TypeArgument: DataRow

  • Assign activity: item(“Status”) = “Unmatched”
1 Like

Hi ppr, glad for your response, please one more step here is to convert DRupdateList to datatable cause I will need it as datatable not list of datarow to process other activities.

in general we can do with .CopyToDataTable method. But keep in mind then the new datatable will become a new identity and we will no longer have the relationship datarow-origin datatable

1 Like

Thanks ppr, I will finally implement, after successful run from my end or issue of concern I will inform you and mark solution

[quote=“ppr, post:16, topic:444611”]

from d in newdt.AsEnumerable
Where dt.AsEnumerable.Any(Function (x) d("N").ToString.Contains(x("N").toString) AndAlso d("AMT").ToString.Contains(x("AMT").toString)
Select r=d).toList

Hi ppr, please this activity has taken more than 1 hr to process 300,000 rows and is still running on my system, is there a better optimization