COMPARE TWO DATATABLES AND GET NEW DATATABLES WITHOUT USING LOOPS!

Hi Team,

(@aksh1yadav, @arivu96, @KarthikByggari, @Karthick_Settu, @ClaytonM, @pathrudu, @PrankurJoshi, @Priya_Pandey, @Divyashreem, )

I have two datatables (Demo1 and Demo2) as mentioned below.
Demo1:-

Name Age Place
Shambu 10 Desi
Pandit 20 India
Cshambu 30 Pune
Foodie 40 Mumbai
Anni 50 USA
Gaikwad 60 USA

Demo2:-

Name Age Place
Shambu 10 Desi
Pandit 20 Srilanka
Cshambu 30 Nagpur
Foodie 40 Mumbai
Anni 50 UK
Gaikwad 60 USA

I need to compare them and get the output as two new datatables (Result1 and Result2) as follows:-

Result1:-

Name Age Place Status
Shambu 10 Desi Repeted
Pandit 20 India New
Cshambu 30 Pune New
Foodie 40 Mumbai Repeted
Anni 50 USA Repeted
Gaikwad 60 USA Repeted

Result2:-

Name Age Place Status
Shambu 10 Desi Repeted
Pandit 20 Srilanka Mitigated
Cshambu 30 Nagpur Mitigated
Foodie 40 Mumbai Repeted
Anni 50 UK Mitigated
Gaikwad 60 USA Repeted

Please note that I need to achieve this without using the for each row loops.
Please tell me how may I achieve this.

VERY URGENT

Thanks and Regards,
@hacky

Hi @hacky - Try using Linq query as per your requirement to compare DT and get the output as DT. Thanks, AK

@AnandKumar26:-

PLEASE PROVIDE ME WITH THE LINQ QUERY>…
CAN YOU TELL ME THE LINQ QUERY TO COMPARE DT1 and dt2 and set the new column value to be “New” or repeted.

@hacky

I could be wrong, but since you need to update a column with a new value, you will need to use atleast 1 loop for each table you need to update. If you didn’t need to update a value, then LINQ alone would work.

So, I think you can use a combination of LINQ and ForEach. - Note that ForEach loops have been improved to be much more efficient and use less resources. (like 10k rows only takes 1-2secs to loop through)

Here is an example:

If Not demo1.Columns.Contains("Status")
     Add Data Column "Status
If Not demo2.Columns.Contains("Status")
     Add Data Column "Status

ForEach row In demo1
    Assign matchedRows = demo2.AsEnumerable.Where(Function(r) r("Name").ToString.Trim.Equals(row("Name").ToString.Trim) And r("Age").ToString.Trim.Equals(row("Age").ToString.Trim) And r("Place").ToString.Trim.Equals(row("Place").ToString.Trim) ).ToArray
    Switch (Boolean): condition: matchedRows.ToArray.Count > 0
        Case True:
            Assign row("Status") = "Repeated"
            ForEach rowMatched In matchedRows //TypeArgument DataRow
                Assign rowMatched("Status") = "Repeated"
        Case False:
            Assign row("Status") = "New"

ForEach row In demo2.AsEnumerable.Where(Function(r) r("Status").ToString.Trim = "" ).ToArray //TypeArgument DataRow
    Assign row("Status") = "Mitigated"

You can also use something like String.Join(","r.ItemArray) = String.Join(",",row.ItemArray) when comparing each column to check all columns more dynamically if desired.

I hope this helps get you in the right direction.

Like I said, if you need to change values in a datatable, I don’t think you can get away with not using a For each loop.

Regards.

3 Likes

Hi @hacky - Try this

Use this Linq Query to compare two Data Table - (From a In ReadDT.AsEnumerable() Join b In OutputDT.AsEnumerable() On a(“Col1”).ToString() Equals b(“Col1”).ToString() select a).CopyToDataTable

This query will bring only the matched value of First Data Table. Get the count of the DT and perform a loop

Use a Assign Property as - WriteDT.Rows(0).Item(“Col3”) = “Repeated”, Similarly for New

Hope this helps.

Thanks,
AK

1 Like

hai sir can u help me with ling quert on vb.net i neet to compare 2 datatable sir

Try using join tables option in uipath itself, It will solve your problem