To Update datatable other than for each

Help
1

I have two datatables - huge data - 50k records… each.
Need to match col1 of dt1 to col1 of dt2, for the matched update col3 of dt1.
For each is taking much time… keeps looping.
Any other alternate method Please!!?

2

Yes,Use inner join,
image ,

image
image447×778 18.1 KB
,
Hope this helps

3

@glory1
In such cases using LINQ, invoke code of optimized procedures are options for evalutions.

Before blind writing solutions adressing your case, just provide a few data samples representing your data. Feel free to anomyse it, but do it in a manner, that the corresponding datatype is covered. Does mean: do not anomyse numbers with strings

1 Like
4

Mayyur,
but for all the matched records dt1 col3 should be updated;
for that only, if i use ‘for each’ its taking much time… need alternate to ‘for each’

5

Can you give your xlsx…or any sample xlsx

1 Like
6

Book1.xlsx (10.4 KB)

Attached the sample - match dt1 - dt2 customer ids, change the status of dt1 - output in dt3.
Alternate to For each -

if Linq, invoke code - optimized proce’ - needed help please… Thanks.

7

@glory
joining via the Customer ID would be possible.
But the status change from Active (dt1) to Cancelled is not derivable into a rule by the given sample data.

Can you elaborate on this?

8

did not understand what to eloborate

9

@glory1
dt1:
grafik
dt2:
grafik
result
grafik

dt1: 1244161 is matching dt: 1244161 and this dt1 row has status Active
How? Which rule is to apply that output will have status cancelled?

On this you were asked to elaborate and to give us details

10

The status of Matched customer ids (in dt1 with dt2) to be changed to cancelled and written into new dt3.

11

@glory1
OK, the aimed goal was already seen, but compared to:

grafik
No match in dt2
But should be part of the output:
grafik

So the requirement looks to me in that form

dt3 = dt1
A dt1 customerID that has a match in dt2 will change the status to Cancelled in dt3

Is this the right understanding from your requirement?

12

Yes it is. That is correct. Thank you!

13

@glory1
Ok, I will have a look on it during my lunchtime and will revert to you after this

14

@glory1
Have a look on demo xaml:
glory1.xaml (8.9 KB)

Kindly note:
rewire the path to excel according to your environment
add a write range to save dt3 into an Excel or other needed output
Have a look on the comments

Let un know your feedback

15

Thank you so much PPR. Its working.

but, I have additional challenges -

If i copy d1 to d3, I did not get all the 120 columns of dt1 in dt3.
or

  • what if I need to update the status in dt1 itself, for the matched records with dt2.
    I tried this way -

(From d1 In dt1.AsEnumerable
Join d2 In dtt2.AsEnumerable
On d1(“Customer ID”).ToString.Trim Equals d2(“Customer ID”).ToString.Trim
Let ur = New Object() {d1(0),d1(1),d1(2), “Cancelled”}
Select dt1.LoadDataRow(ur, True)).ToList

It error’ed out… Please help.

16

@glory
The behaviour is originated different. Based on your sample the row were reconstructed on the no of columns from the excel and not 120 columns.

However I have an Idea on an alternate and will prepare it for you on my evening

17

@glory1
Have a check on updated XAML:
glory1_V2.xaml (9.7 KB)

1 Like
18

i am pretty sure that a simple use of the activity Join Data Table with a inner join and a For Each Row updating that column have to be faster and easier than all of this…

1 Like
19

I would suggest as 50k is a Nice Set that 2 different Implementations are sent into a Test and execution time will be logged.

With the Resultat of such a Test WE can Argument based on facts and Not on opinion Base.

But First of all lets Help glory1 in His Task. All Others is contraproductive and Not supporting in all.

20

i dont agree with your point of view… Im suggesting him to use uipath activities to accomplish what he needs, do you really think this kind of help:

(From d1 In dt1.AsEnumerable
Join d2 In dtt2.AsEnumerable
On d1(“Customer ID”).ToString.Trim Equals d2(“Customer ID”).ToString.Trim
Let ur = New Object() {d1(0),d1(1),d1(2), “Cancelled”}
Select dt1.LoadDataRow(ur, True)).ToList

giving to someone that dont have a lot of developing background is really helping? Most will not be able to understand and learn from this, unless is some genius…