How do I compare two datatables with same columns but different contents?

Help Activities
1

Hello,

I really hope you can help me :slight_smile:

I need to compare datatable 2 (new version) with datatable 1 (old version).
The column names are the same but the contents can be different. There is no ID or key, because each key figure can be occure more than once.
However, the combination of the key figures in each column are unique.

It should be checked in every row if the combination of Column1+Col2+Col3+Col4 from dt2 is already included in dt1. If not, the new combination from dt2 should be shown up as the result in dt3.

Thanks in advance,
Ani

2

Join (full) both the datatables based on all the columns (using join datatable activity)
say DT1 is:
a,b,c
1,2,3
4,5,6
6,4,2

and DT2 is:
a,b,c
1,2,3
4,5,8
6,4,2

if you full join them on all the columns you will get a dt like this:
a,b,c,a_1,b_1,c_1
1,2,3,1,2,3
4,5,6, , ,
, , ,4,5,8
6,4,2,6,4,2

so filter out this dt where column “a” is empty ,this will give you the rows which are present in dt2 but missing in dt1 (that is ; [4,5,8])
now you can remove the extra columns i.e. “a”,“b”,“c” and merge this datatable with dt1 to get the final result dt
in which we have data from dt1 and the non matching data from dt2

3

Hi

Welcome Back to UiPath forum

Anyhow as we are trying to merge them and have the newer one from dt2 to dt1 let’s merge them remove the duplicate records which is the same

Then first let’s read both the excel and read as dt1 and dt2

Now use a MERGE DATATABLE activity where source is dt1 and dt2 is destination one

Now dt2 will have the consolidated data

Use a REMOVE DUPLICATE ROWS actuwhere pass dt2 as input

This will have only the matching one and unmatched new one

Hope this would help you

@Ani_Ma

4

Thanks. That’s what I tried. But with this method I also got the combination of dt1 and I don’t need this data anymore.

1 Like
5

Then in that case we can try this

getting the rows from dt1 not matching rows in dt2

Dt_final = dt1.AsEnumerable.Except(dt2.AsEnumerable, DataRowComparer.Default).CopyToDatatable()

Or

getting the rows from dt1 not matching rows in dt2

Dt_final = dt1.AsEnumerable.Except(dt1.AsEnumerable, DataRowComparer.Default).CopyToDatatable()

Cheers @Ani_Ma

1 Like
6

Great, thanks, that worked :slight_smile:

7

Works very good, thanks!

1 Like
8

Great

If there is no further queries it would be great if you can close the topic with right comment marked as solution

@Ani_Ma

9

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