How to compare the 2 Excel by unique Element (EmpId)?

Help
, ,
1

I am having 2 excel 1.MasterExcel 2.UpdatedExcel,then i want to compare the Empid(Column Name)
If the master’s excel having the same Empid then its should do nothing else the new Empid and Appropriate Row will update into the master Excel.

Can any one Help me.

2

Hi @keerthi_97
use multiple for each row in Dt1 and Dt2

row(“Empld”).ToString.equals(row1(“Empid”).ToString)
then condition set it as true or else use assign row(“Column Name”)=row1(“ColumnName”)

Thanks
Ashwin S

1 Like
3

@keerthi_97

Use Join DataTable activity to do this.

1 Like
4

@keerthi_97
IT Sounds Like both Datatable have the Same column structure and If WE have an empid in Second Datatable that is Not present in the master Datatable then this row from Second Datatable should be added to mastertable. Is this understanding right?

1 Like
6

yeah, i did this one bt its throw Error like thisErrorExel

7

Hi @keerthi_97

Are you trying to delete the rows
(or)
Can you debug your workflow
Thanks
Ashwin S

1 Like
8

I am trying to update the new Empid number in the master Table from the my Second Excel Sheet.
Thanks
keerthi

9

Hi @keerthi_97

Check this

Manipulate Columns of a DataTable using For each

Thanks
Ashwin.S

1 Like
10

@keerthi_97
Have a Look on your Screenshots on empid 3115
This Id is Not unique and occurs two Times

I dont know all your requirements, but maybe your scenario can even be solved with a merge table Activity, as there are no unique IDS and No record Updates for already existing but edited rows is Handled by your scenario specification

If i understood the Screenshots wrong then i would suggest following.

  • Find Common IDS, can be done with a linq Join Statement
  • Calculate non Common rows. Can be done wir Set Operation except function
  • Add non Common rows to master datatable
1 Like
11

@ppr
yeah,its having two 3115 only but my scenario is checking the Empid if it is in master or not if is there meaning leave as it is or its not there means i want to update in master table

12

@keerthi_97
Fine, then go for

  • Find Common IDS, can be done with a linq Join Statement
  • Calculate non Common rows. Can be done wir Set Operation except function
  • Add non Common rows to master datatable

For First Task
Use an assign Activity

(From d2 in dtupdate.asenumerable
Join d1 in dtmaster.asenumerable
On d2(“EmpNo”).toString equals d1(“EmpNo”).toString
Select d2).tolist

Returns a list of datarows from Update Datatable, that has Common empno in Update and master datatable

2 Likes
13

Thank you, But How to use LinQ join and Set Opreation Except Function in ui path no idea about that…
can you please help me

14

@keerthi_97
Sure. But your First question is answered above
Use an assign Statement
Use Statement from above
Replace dtmaster, dtupdate Name with your datatablevariable names
Assign to a variable of list(of datarows)

Once you have done and IT works then WE will Guide you for the next steps

1 Like
15

yeah i did Main.xaml (8.9 KB) Is it correct or Wrong

16

@keerthi_97
Im still on Holiday, so cannot Review your xaml as i do answering with my Cell phone

But you can Post a Screenshot in Case you are unsure.

In the next step WE do handle following. The Join Statement can Return 0 or more rows. This WE do handle within an If else activity

Drag an If Activity into the flow and the condition is yourdatarowlistvar.count > 0

Once you have done WE will do the next

1 Like
17

In Assign Activity dtTemp=From A In DT1 Join B In DT2 On A.EmpNo not Equals B.EmpNo Into C From dtTemp In C.DefaultIfEmpty() Where dtTemp=null

where dtTemp is datatableVariable

please let me knw is it correct or Wrong.

18

@keerthi_97
Find a demo XAML here:
keerthi_97.xaml (11.4 KB)

I did a setup with dummy data but it should match to your structure as close as possible
The scenario of nonmatching rows or only new rows in Update Excel is initially incorporated.

Let us know your feedback

2 Likes
19

Myself Run the script by inseUpdateExcel.xlsx (11.0 KB) rt my Excels but master Excel didn’t updated the new one keerthi_97.xaml (8.5 KB) MasterExcel.xlsx (66.8 KB) i have attach thesaml file and master,updated Excels.
please check it…

20

@keerthi_97
dt1 representing the master is updated with the non common rows:
grafik

Sure the master Excel is not updated, as the updated dt1 is not written back to excel. For this task you can use a write range activity and it will write the entire new master data onto an excel

21

yeah, thank you… i got my requirement But i also need add column also if the new column is present in the updated data table then master also can update the column name as per.
can you help me.