Linq Compare 2 Data Table and Update Column Result

Hi All,

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

1

thankyou all

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,

Required output -
image

Hope it helps!!

Hi,

Can you try the following sample?

 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

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

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

Hope it helps you out!

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

4

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

4

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

4

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!!

Hi,

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

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,

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

1 Like

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

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