I have two excel files
1)Global sheet
2)Final File
where
I need to add the location which is present in global sheet excel file into the entity column in final file excel while comparing the names of employees but the column name “Employee Name” is present in one excel and “Name” is present in another excel
Kindly provide me a solution on how to solve it!
If the data is not confidential could you provide the excels with dummy data.
Regards
You can try this linq in invoke code
Read both tables into dt1(final) and dt2(Global)…send dt1 as in/out and dt2 as in argument
Dt1.AsEnumerable.ToList.ForEach(sub(r) r("Entity") = If(dt2.AsEnumerable.Where(function(x) x("Name").ToString.Equals(r("Employee Name").ToString)).Count>0,dt2.AsEnumerable.Where(function(x) x("Name").ToString.Equals(r("Employee Name").ToString))(0)("Location").ToString,""))
Cheers
No there is no luck with this! but Thanks for your efforts
Did you write the data back to excel?
and are employee name and name are exactly matching?
ideally this should be working. Can you show what you tried
And a input and output would help
cheers
Can just let me know the steps I’ll follow them !
Can you share what have you written in Invoke code. Can you share the query. I will help you resolve that.
Regards
Error_datatable.AsEnumerable.ToList.ForEach(sub(r) r(“Entity”) = If(Global_Dt.AsEnumerable.Where(function(x) x(“Name”).ToString.Equals(r(“Employee Name”).ToString)).Count>0,Global_Dt.AsEnumerable.Where(function(x) x(“Name”).ToString.Equals(r(“Employee Name”).ToString))(0)(“Location”).ToString,“”))
In the Invoked arguments, give the below way and try.
Use Write Range Workbook and pass Error_datatable to write the output to new sheet.
Hope it helps!!
Ok will try this approach
Okay, try this and let me know if it works… @Kancharla_Mahija
This is a standard database operation called a join. Use the Join Data Table activity.
Errordatatable should be in/out…please change…I have given in previous post also
Later write the datatable back
Cheers
Ok will share them with dummy data do you need the main.xaml as well?
Finalfile_dummy.xlsx (9.6 KB)
Book2.xlsx (10.8 KB)
Try this code in Invoke Code:
Error_datatable.AsEnumerable.ToList.ForEach(sub(r) r(“Entity”) = If(Global_Dt.AsEnumerable.Where(function(x) x(“Name”).ToString.Equals(r(“Employee Name”).ToString)).Count>0,Global_Dt.AsEnumerable.Where(function(x) x(“Name”).ToString.Equals(r(“Employee Name”).ToString))(0)(“Location”).ToString,“”))
Invoked Arguments:
Output:
xaml:
Sequence14.xaml (8.3 KB)
Try running the above workflow. Change the excel files paths in Read Range and Write Range. Check and let me know if it’s working for you.
Regards
Did you first happen to read both sheets?
Are the names exactly matching?
We see the same is working
Cheers
