Comparison of two data tables

Hi all,

I have two data tables, from which i need to compare 3 columns .
one column should match exactly and other columns should match partially(contains).

To check one column we can convert one column of second data table into list and we can loop through but to compare three columns what should be done?
can anyone please suggest the query.
Thanks in advance

maybe this would help you, give a check please


Did we try with JOIN DATATABLE activity if we want to find the matching records
Cheers @Shree_Vybhavi

yes but when we use join we can have exact match that is ‘Equals’ but how can we check for contains?

image attached for reference…
Name should match partially (contains) AND empID should be exact match(equals)
when we do join for single column we can write like this ,

((From p In DT1.AsEnumerable () Join q In DT2.AsEnumerable () on p.Item(“Column1”).ToString.Trim Equals (q.Item(“Column1”).ToString.Trim) Select p).ToList).CopyToDataTable

what is the query to write contains

you can change Equals to: p.Item("Column1").ToString.Contains(q.Item("Column1").ToString)

could you please provide full query


((From p In DT1.AsEnumerable () Join q In DT2.AsEnumerable () on p.Item("Column1").ToString.Contains(q.Item("Column1").ToString) Select p).ToList).CopyToDataTable

I’m getting error like this :frowning:

Pls suggest the solution.

sorry with join we cant use contains, this will have to be changed to use more complex linq statements that i am afraid im not sure if possible, in one query… maybe @ppr can help you.

@Shree_Vybhavi Do you want to get the Matching Records from the two Excel Files Matching the conditions You specified?

As already noticed a Join Statement has to follow some constraints e.g the mandatory use of equals, the Must use of Leftside Datatable equals rightside Datatable referencing etc

However to rewrite IT in a cartesian product can have Chance to get IT done, but also have the risk of Side effects.

Such a Statement would Look Like this

(From p In DT1.AsEnumerable()
From q in DT2.AsEnumerable()
Where p.Item(“Column1”).ToString.Trim.Equals (q.Item(“Column1”).ToString.Trim) and p(colx).to string.contains(q(colx).toString) and …
Select p).ToList

I suggest to Test IT ob sideeffects and correctness. A CopyToDataTable will throw an Error If No rows are returned. So a Check Like resultrowsvar.count>0 is also recommended.

1 Like

@Shree_Vybhavi - it has been solved? or still you are seeking some assistance?