Merge two data tables from two different excel workbook and match columns

Help
, ,
1

Good Evening,

I am trying to do the following:

  1. Combine two different workbooks that have one column in common which are ID’s number
  2. Once the bot matches the ID I want it to create a new sheet show all the ID’s that match and the ones that don’t match HOWEVER combine the two ID columns into one single column of ID’s that have all the ones that were a match and not matched
  3. The new sheet should have all columns that were in the two workbook and their information should match the rows to their respected ID’s

I have added two demo excel for reference.
Book2 Demo.xlsx (11.0 KB) Book1 Demo.xlsx (8.9 KB)

I already used Join Data Tables to create the new excel sheet. I’m just stuck in only having one column of all ID’s matched and not matched. Can anyone please help me?

2 Likes
2

Hi @Sugaray

What Join Type did you use to join the two tables ?

cheers :smiley:

Happy learning :smiley:

3 Likes
3

I used this Join activityjoin%20demo

2 Likes
4

Did you get any output as i can see to your codes its fine you will get some value on it ?
@Sugaray

cheers :smiley:

Happy learning :smiley:

2 Likes
5

I got the following output. join%20output

1 Like
6

However, I would like my output to become like tis some how with all the IDs in one columnjoin%20future%20output

1 Like
7

I thnik the first one is the right output.Thats exactly what the output of the left join.
@Sugaray

cheers :smiley:

Happy learning :smiley:

2 Likes
8

But if you eager to achive this .There a way how to solve this kind of issue.

@Sugaray

cheers :smiley:

Happy learning :smiley:

1 Like
9

Could you explain to me how to have all ID’s in one column please?

1 Like
10

I can do this with in this issue? But if the excel dynamically change we need to come up with another strategies.

Scenario
1.Use Excel application scope activities.
2.Use Read Range Activites and set the value to the Datatable variable(Create Variabl with datatable type).
3.Get the last row of Column(“ID nVision”).Tostring.
4.Use assgn activities and set the value of ID nVision
5.Then Delete the column of ID nVision.
6.Get the row count of id and set it to integer datatype variable.
7.Then use write cell activities to write the value of ID nVision.

Hope i guide you well :smiley:

cheers :smiley:

Happy learning :smiley:

2 Likes
11

unfortunately the excel will change on a monthly basis. Sometimes there are more or less IDs rows in the workbook. This was a demo excel because the original one has over 30,000 rows.

1 Like
12

Yah thats what i am thinking now.I will try using SQL Commands Or C#.net if possible

Once i got an answer i will notify. But now forgive me… :cry:

cheers :smiley:

Happy learning :smiley:

1 Like
13

Thank you for your help! I appreciate it.

1 Like
14

Good evening @pattyricarte,
By any chance were you able to figure out this problem? I’m still trying on my end but not being successful at it.

2 Likes
15

Hi @Sugaray
Good morning were still working on it! But if you want to address your issue you can use the scenario that i gave to you.And i will update you once done in figuring how to do it dynamically. My apologies @Sugaray

cheers :smiley:

Happy learning :smiley:

2 Likes
16

Hi @Sugaray

I already did ,I created in the sql using script,But as i can see it almost the same with the scenario and yet i cannot convert it in UIPATH.

cheers :smiley:

Happy learning :smiley:

2 Likes
17

Hi @Sugaray

Before

image

After
image

But if your desire is to get this kind of output.

What I did is to make a full join and do some modification in excel and create that kind of format.

Kindly advise!

cheers :smiley:

Happy learning :smiley:

3 Likes
18

Good Morning @pattyricarte,

That’s amazing! how were you able to do it? Did you do the Modifications by some activities on UiPath? Could you please show me? was the column width also auto fixed too?

2 Likes
19

Hi @Sugaray

Hi Good eve. Tomorrow I will send the xaml files.

And please check if the functions satisfy your needs.

cheers :smiley:

Happy learning :smiley:

2 Likes
20

thank you so much! Can’t wait to see it.

2 Likes