Merge two excel sheets based on unique key which is dynamic in nature

Help
1

Hi Everyone - I need help in below scenario.

I have attached two excel sheets one is named as “DB Sheet” and another is “onlinereport”.
Sheet “DB Sheet” is static and sheet “online report” is dynamic (changing every time when report is generated).
In both the sheets col EmpID is common and I have to append emailID column from “DB sheet” to “onlinereport” sheet for the EmpID present in “online report” sheet.

How I can achieve this scenario.

onlinereport.xlsx (8.2 KB) DB Sheet.xlsx (9.0 KB)

2

merge datatables activity will do that…

3

@navincemk
Have a look on this demo XAML showcasing your scenario
navincemk_JoinDT.xaml (10.1 KB)

Result from Debug:
grafik

Kindly note:

  • the implementation were focusing on using much essential activities as possible
  • in case of handling additional cases (e.g. variations like trailing spaces in cells) the implementation can be driven more specific e.g. with the help of LINQ

Let us know your feedback

EDITED: reattached sucessfully validated XAML

4

@ppr Thanks for your reply. But I am not able to see the code due to error shown in attached screen. Can you please attach screen shot for reference. Looks like there is some error in .xaml file. Please attach screen shot of activity as well as property screen.

image
image652×636 12.2 KB

5

@navincemk
This Happens If the Link is clicked and the File ist Standalone Opened in UiPath Studio. Just create a new empty Project or use your existing and save the xaml within. Let me know If this was working

6

@ppr I tried it but still seeing same problem.

7

OK i will Check at my end. Give me some minutes

8

@navincemk
strange things several checks didnt replicate this as reported by you. XAML were validated sucessfully even with different package versions.

Here we go:

grafik
grafik306×670 16.9 KB

read ranges as usually for reading in the excels
join table like this:
grafik

remove datacolumn like this:
grafik

9

@ppr Thanks sir and I know the reason of this issue now. The version I am using does not have Join Data Table activity. My organization provided me only this version.

Can you suggest me other way to do it as I do not have this activity available?

image

10

@navincemk
can you tell me which studio version you are using and which packages you have in use:
grafik

However I will provide an alternate to you, lets see if we overcome those restrictions

11

@ppr

I have been provided 2017 version and UiPath.System.Activities and UIAutomation activities can be used above 2018 version. Below is screen shots.

image

image
image1346×748 24.3 KB

12

@navincemk
have a look on this alternate:
navincemk_JoinDT2.xaml (12.7 KB)

13

@ppr

Thanks sir for your help. Looks like my current version is not supporting the workflow. But these two workflow you provided in working perfectly fine when I use my community edition on my personal laptop.

Thanks for your help. If you see the solution of it please let me know otherwise thanks for you help.

When I load the workflow it is showing below error:

image
image699×511 12.3 KB

And when I try to save it show:

image
image1089×450 13.5 KB

14

@navincemk
Let US sort IT Out tomorrow (wendsday) WE will get IT run

15

@ppr

Thanks sir. If you want we can connect and discuss over phone and live meeting.
Thanks a lot for your help. Let me know when you are free.
Please email me at:
reply4navin@gmail.com

16

@ppr

I am able to close this item using LINQ query. Thanks for your time and help.
I awill connect with you later in case I find another issue. Thanks a lot …

(From a In dtReport.AsEnumerable() Join b In dtDB.AsEnumerable() On a(“Emp ID”).ToString Equals b(“Emp ID”).ToString Select dtCombined.LoadDataRow(new Object() {a(“Emp ID”).ToString,a(“Name”).ToString,a(“Complaint”).ToString,b(“email ID”).ToString},False)).CopyToDatatable

1 Like
17

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.