I have two Datatables(Master Datatable and second Datatable), I want to compare both datatable by a column name(Emp_Id) and need to update the second datatable if there is any changes available on the Master Datatable. can anyone help me on this?
From the Above example, Mail id needs to update to the second datatable for Emp_id 10011 and Location needs to update to the second datatable for Emp_id 10013.
Could you try to use Join Datatables Activity and Check ?
The Following Steps should help you in achieving the result :
After Reading the Excel Sheets as Datatables, we keep the First Datatable as Input Datatable 1 and Second Datatable as Input Datatable 2 in the Join Datatables Activity. We Select Inner Join as the Join Type. We Declare and create an Output Datatable. The Columns to Join as Emp_id.
Now the Output Datatable will produce the Column Names containing both the Input Datatables, as they have the same column names they will have a suffix added to them as _1. We would require to either remove the First Datatable Column Names and keep only the Column Names ending with _1, as they are of the Second Datatable.
This Should be the Output required, although the column names are different, we can then Change it to the desired Column Name.
After Comparing both datatable with Emp_id, it should find the changes specifically(Name Or Mail Or phno Or location) in Master Datatable based on Column value(Emp_id) and update the changes in the second Datatables instead of updating the whole data.
And also need to update to Second DataTable if there is any new Emp_id available in the Master DataTable.
Let’s Consider the Below Datatable as Master Datatable :
Let the Datatable below be the Second Datatable :
As you can notice, in the Master Data, there is an explicit row with Emp_id 1015. In the Second Datatable, there is an Explicit row with Emp_id 1016. Hence, the Resultant Datatable as per the Requirement Should appear as below :
Resultant Datatable = Updated Values from Master DT + New Rows from Master DT (Not available in Second DT, Emp_id 1015) + Not Matching Rows from Second DT (Emp_id 1016)
If the above is the Output Expected, Check the Workflow Below : DT_Update_Values.xaml (16.4 KB)
Check whether the below video can help you. It will help to get the similar and dissimilar rows from both the excels and once it identifies the dissimilar rows, you can loop through it and update the value.
Instead of checking one by one, you can get the dissimilar list and do the verification.
Maybe the case can be based on another interpretation (kindly note: join vs. match vs. lookup)
We can also wotk with SequenceEquals but in general we can state:
when a matching EMPID is within the second dt, then use this row column values
when not a matching row is found in dtsecond for a dtmaster row, use dtmaster row column values
So we can simplify it to following:
(From dleft In dtLeft.AsEnumerable
Let dr = dtRight.AsEnumerable.Where(Function (x) x("Emp_id").toString.Trim.Equals(dLeft("Emp_id").toString.Trim)).DefaultIfEmpty(dleft).First()
Select r=dr).CopyToDataTable
ashokkarale
sharazkm32
sonaliaggarwal47
LamaX