Comparison of two data tables

[Shree_Vybhavi]

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

[fernando_zuluaga]

maybe this would help you, give a check please

Compare Datatables one specific column with other specific Column and Get Matched and Not Matched Records`

Hello Community Use Below Code to Compare 2 Datatable Column and Get Matched and Not Matched Records Note- Based on your datatable column datatype update datatype here with column field. To Get Matched Records Datatable Out_Matched_Data = In_DataTable1.AsEnumerable().Where(function(row) In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable() To Get Not…

@Shree_Vybhavi

[Palaniyappan (Forum Leader)]

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

[Shree_Vybhavi]

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

[Shree_Vybhavi]

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

[bcorrea (Bruno Correa)]

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

[Shree_Vybhavi]

could you please provide full query

Regards,
Vybhavi

[bcorrea (Bruno Correa)]

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

[Shree_Vybhavi]

I’m getting error like this

Pls suggest the solution.

[bcorrea (Bruno Correa)]

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.

[supermanPunch (Arpan)]

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

[ppr (Peter Preuss)]

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.

[aksh1yadav (AKSHAY YADAV)]

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

Regards…!!
Aksh