Linq Compare 2 Data Table and Update Column Result

Help Activities
, , ,
1

Hi All,

I want to compare 2 data table and update first dt after comparing them

1

thankyou all

2

Hi @Rpa12343

→ Use Read range workbook activity to read the first excel and store in a datatable called DT1.
→ Use another read range workbook activity to read the second excel and store in a datatable called DT2.
→ Use an assign activity to write the Status in DT1.
→ Create a variable called dt_Output,

- Assign -> dt_Output = (
                         From row1 In DT1.AsEnumerable()
                         Group Join row2 In DT2.AsEnumerable() 
                         On row1("ID") Equals row2("ID") 
                         Into gj = Group
                         From subRow In gj.DefaultIfEmpty()
                         Select DT1.Clone.Rows.Add(row1("ID"), If(subRow IsNot Nothing, "Success", "Failure"))
                                ).CopyToDataTable()

→ After that you can use the Write range workbook activity to write the dt_Output to First excel.

Check the below workflow for better understanding,

Regex_Practice
Regex_Practice848Ă—643 44.8 KB

Required output -
image

Hope it helps!!

3

Hi,

Can you try the following sample?

image
image730Ă—570 22.7 KB

 dt1 = dt1.AsEnumerable.Select(Function(r) dt1.Clone.LoadDataRow({r(0),if(dt2.AsEnumerable.Any(Function(r2) r("ID").ToString=r2("ID").ToString),"Success","Failed")},False)).CopyToDataTable()

Sample
Sample20240318-1a.zip (8.1 KB)

Regards,

1 Like
4

Hi @Rpa12343 ,

You can use Below Linq to compare 2 datatables and if matched then Status = “Success”, Else Status = “Failed”

(From row In dt1
Let a = If(DT2.AsEnumerable.Select(Function(x) x("ID").ToString).tolist.Contains(row("ID").tostring),"Success","Failed")
Select dt1.Clone.Rows.Add({row("ID").tostring,a})).copytoDatatable

Below is the screenshot of the output in the immediate panel

image
image1192Ă—564 22 KB

Below is the file
CompareDT_Compare the Status.zip (3.2 KB)

Hope it helps you out!

5

what if i want to keep carryting other columns and all value in dt1?

4

6

what if i want to keep carryting other columns and all value in dt1 ?

4

7

what if i want to keep carryting other columns and all value in dt1??

4

8

Okay I have changed the LINQ, check the below one… @Rpa12343

- Assign -> dt_Output = (
                         From row1 In DT1.AsEnumerable()
                         Group Join row2 In DT2.AsEnumerable() 
                         On row1("ID") Equals row2("ID") Into gj = Group
                         From subRow In gj.DefaultIfEmpty()
                         Select DT1.Clone.Rows.Add(row1("AAA"), row1("BBB"), row1("ID"), row1("CCC"), If(subRow IsNot Nothing, "Success", "Failure"), row1("DDD"), row1("EEE"), row1("FFF"))
                             ).CopyToDataTable()

Hope it helps!!

9

Hi,

How about the following? This sample will work if there are any number of other columns.

image
image499Ă—623 18.3 KB

dt1.AsEnumerable.ToList.ForEach(Sub(r)
r("STATUS")=If(dt2.AsEnumerable.Any(Function(r2) r("ID").ToString=r2("ID").ToString),"Success","Failed")
End Sub
)

Sample
Sample20240318-1a (2).zip (8.5 KB)

Regards,

10

so if i want to add more columns have to add manually by column name in linq right?

1 Like
11

Yes @Rpa12343 You are correct, if the columns are dynamic then we have to change the code based on conditions.

12

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