Merge Datatables with Different PrimaryKey

Help
1

Hi, please bear with me as I am quite new to UiPath. It’s a small issue but I’ve been puzzling over it for a while.

I am trying to merge 3 datatables using “No” as the PrimaryKey. “No” is a number prefixed either by an S or T. So far I’ve managed to read them as 3 DTs, used regex to replace either S or T for DT1, and then assigned “No” (e.g. 1001, 1002) as the PrimaryKey. I can then merge the 3 DTs.

But my problem is that if I do this, in the merged DT, “No” shows up with only the numbers, without the S or T. How can I merge them so that it shows up as the desired output below instead?

Example:

No Data A
S1001 A1
S1002 A2
T1003 A3
S1004 A4
T1005 A5
No Data B
1001 B1
1002 B2
1003 B3
1004 B4
1005 B5
No Data C
1001 C1
1002 C2
1003 C3
1004 C4
1005 C5

Desired Output:

No Data A Data B Data C
S1001 A1 B1 C1
S1002 A2 B2 C2
T1003 A3 B3 C3
S1004 A4 B4 C4
T1005 A5 B5 C5

Thank you!

2

Quite simple. Add a new column in which you store the numeric part and do the merge on that one.
Also, there is no need for a regexp, just eliminate the first character.

3

Sorry, I’m still not sure how to do this. Could you walk me through the workflow or provide an example workflow? Thanks!

4

Invoke VBA code, passing an I/O parameter.
The VBA code adds an expression column, so you do not need to do a for each loop on your datatable.

table.Columns.Add("newkey", Type.GetType("System.String"),"substring(Column1,2,3)")
5

I’m really sorry, this is all new to me so I still don’t get how to apply this. Invoke VBA only works in an excel scope right? Which excel sheet do I invoke it in?

6

No. Invoke VBA works without container, so it can be put right after you read the datatable from your source

7

Would it be possible for you provide a sample workflow and I’ll try to work out how to apply it from there?

8

Main.xaml (6.0 KB)

9

Thanks so much for your help. I’ve continued on your example and tried to merge it, but it didn’t manage to merge it properly. Main.xaml (14.1 KB)

  • Firstly, the invoked code seems to be running on all 3 DTs, when it should only run on DT1.
  • The column name of the integer-only column seems to be “newkey + substring (Column1,2,3)” which may be why it is causing errors when merging.

Could you let me know where I’m going wrong?

Thank you!

10

In the sample you provided it runs ok.

You misread. The headers are not separated from the values by a new line in the actual values, but if you check the detail you can see this is right

image image

Looking again at your request, you want to join tables, not to merge them.
Merging would mean they have the same columns and you put the tables one “below” the other.
Please check attached
test5.xlsx (7.4 KB)
Main (3).xaml (12.1 KB)

11

Thanks! But I’m afraid the workflow doesn’t work for me I suspect because I don’t have the Join Datatables activity. I’m running an old version of Uipath studio v 2018.2.3 at work and I’m not allowed to upgrade it sadly. Is there another way to join the datatables based on a common column that doesn’t use Join Datatables?

12

Are you sure? This is available in 2018.4.4, but I can’t check for 2018.2.3

image

13

Additional question : where do you take your data from? If it’s from a database, it’s easier to do all the joining in sql, instead of post processing in UiPath.

14

Yep, definitely no Join Datatables activity. The data is just from multiple excel files. Anyway I’ve since expanded on your example and found a workaround (basically by adding and renaming and then removing extra datacolumns etc.) , which may not be the most efficient but it gets the job done. Thanks for your help! :smiley:

15

Please post the final xaml, as I want to see how your solution turned out.

16

It’s a bit long-winded but it works. Something like this:
Main2.xaml (19.6 KB)
merge multiple.xlsx (10.7 KB)

1 Like
17

A little complicated, but nicely done :wink:

18

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