Compare two datatable based on common column if match found then update values from other datatable to specific column in first datatable values

Hi,
I want to compare two datatables based on Emp-Id from both datatable and if match found then want to update “EmpName” and “role” column from Dt1 with matched column “Name” and “Role” from Dt2 else update columns as “NA” in Dt1.

For example:
Dt1-

EmpId EmpDesignation EmpGroup EmpName Role
1 HR A
2 HR B
3 TL C
4 Dev A
5 TL K
6 HR A
7 TL C
8 HR D
9 TL A
10 Dev B

DT2:

ID Name Role
1 ABC L
4 XYZ A
7 NNO A
9 MMT L

Required output:

EmpId EmpDesignation EmpGroup EmpName Role
1 HR A ABC L
2 HR B NA NA
3 TL C NA NA
4 Dev A XYZ A
5 TL K NA NA
6 HR A NA NA
7 TL C NNO A
8 HR D NA NA
9 TL A MMT L
10 Dev B NA NA

Hi @Mansi_Mhatre

You can do below steps

  1. Apply for each loop on DT1
  2. Take filter datatable activity, filter based on ID and Role
  3. If filter result has rows then update the DT1 columns by assign- activity
    Assign - CurrentRow(“ColumnName”)=DT_FilterResult.Rows(0)(“ColumnName”).ToString
  4. After loops ends DT1 would be updated as per the requirement

Thanks,
prankur

you can produce the output with:

  • join datatable activity - left join
  • LINQ: left join, Any check, Where filter Approach

Sry but data can be more than 70000-80000. So looping is not idle for this scenario.

Can you provide queries for both to check

Hi @Mansi_Mhatre

You can try this code, drag invoke code activity select language as C#

DT2.AsEnumerable().ToList().ForEach(m=>{
DT1.AsEnumerable().Where(r=> m.Field<String>("ID")==r.Field<String>("EmpID")).ToList().ForEach(s=>{
	s.SetField<String>("EmpName",m.Field<String>("Name"));
	s.SetField<String>("EmpRole",m.Field<String>("Role"));
});

});

Thanks,
Prankur

1 Like

prepare an empty target datatable with build datatable, configure the cols:
EmpId EmpDesignation EmpGroup EmpName Role
use var name dtTarget for this

or clone dt1

Assign Activity
LHS: dtTarget
RHS


(From d1 In dt1.AsEnumerable
Group Join d2 In dt2.AsEnumerable 
On d1("EmpId").toString.Trim Equals d2("ID").toString.Trim  Into gj=Group
From g In gj.DefaultIfEmpty
Let ra2 = If(isNothing(g), New Object(){"NA","NA"}, g.ItemArray.Skip(1).toArray)
Let ra = d1.ItemArray.Take(3).Concat(ra2).ToArray
Select r = dtTarget.Rows.Add(ra)).CopyToDataTable

Kindly note: LINQ is written for the given datatable structure from above

Hi @Mansi_Mhatre,

this LINQ query can be used to achieve your desired result.

Use assign activity for the below query.

(From a In dt1.AsEnumerable
Group Join b In dt2.AsEnumerable On a(0) Equals b(0) Into grp = Group
From g In grp.DefaultIfEmpty
Select row = {a(0), a(1) ,a(2),If(isNothing(g), “NA”, g(1)),If(isNothing(g), “NA”, g(2)) }
Select dtFinal.Rows.Add(row)).CopyToDataTable

Screenshot Reference :
image

Hope this helps. Happy Automation!!!

1 Like

I have one excel sheet with given data, and want to perform some activities without loop as it contains more than 40000 data and multiple columns also.
Sheet data:
EmpId EmpName EmpGroup EmpDesignation Remark
1 ABC A HR
2 bcd A TL
3 thg B Dev
4 uth D Dev
5 ikhf C HR
6 tppl B HR
7 artu B TL
8 vbnd N TL
9 okjd G PM
10 mnbd C HR
8 jkh N TL
5 kopt C HR

Activities need to perform without loop:

  1. Remove duplicate rows on the basis of EmpId column i.e. if more than 1 EmpId found the only keep 1 row and remaining duplicate rows should be move to other sheet (" Duplicate values")

For given example:
Duplicate sheet will be-
8 jkh N TL

5 kopt D HR

  1. Filter EmpGroup with value ‘D’ and update Remark as “group D”
    Note: data is more than 40000 and i want all data after filtering and updating not just filtered datatable.

Output:
EmpId EmpName EmpGroup EmpDesignation Remark

1 ABC A HR

2 bcd A TL
3 thg B Dev
4 uth D Dev group D
5 ikhf C HR
6 tppl B HR
7 artu B TL
8 vbnd N TL
9 okjd G PM
10 mnbd C HR

  1. Then again for same datatable filter if EmpDesignation is ‘TL’ and
    EmpGroup ks not ‘N’ then update Remarks column as ‘group B’

Output:
EmpId EmpName EmpGroup EmpDesignation Remark
1 ABC A HR
2 bcd A TL group B
3 thg B Dev
4 uth D Dev group D
5 ikhf C HR
6 tppl B HR
7 artu B TL group B
8 vbnd N TL
9 okjd G PM
10 mnbd C HR

Then again check if EmpDesignation equals ‘Dev’ and Remark is blank then update Remark column as “Other employees”

EmpId EmpName EmpGroup EmpDesignation Remark
1 ABC A HR
2 bcd A TL group B
3 thg B Dev Other Employees
4 uth D Dev group D
5 ikhf C HR
6 tppl B HR
7 artu B TL group B
8 vbnd N TL
9 okjd G PM
10 mnbd C HR

I want to perform those actions without loop and with minimum datatables. This is just sample data for logic, data can be more than 40000 and with more than 20 columns.
Basically it should filter and update data but return whole datatable after output not just 1 datatable for 1 filter then perform other filter on that filtered which is wrong.

Please find final output

can you provide the linq query for –

i need to compare two datatables with same column for example-

DT1

name id
rajkumar
shahrukh
vijay
sreeram
guru

DT2

PatientName ID
Raj kumar
Sree ram V
Guru V
Vijay T

i need to match the name columns of both DT and if the name contains any one of the word in patient name then it should be matched and give the output in a new column as matched

may we ask you top an new own topic for your case? Thanks

can you give that datarow comparer code

Hi Ramya, if we need to concatenate two column values and write how do we do? Example instead of a(0), I want to have a(0)= a(1)+a(2). And I want to add this condition for a(2) = =IF(AND(C2>50000,C2<59999,I2>6000),1,0). Can you please help with this? Thank you