Merge multiple data table where 1 column is common

Help Activities
,
1

Hi I have multiple data table, in which all the datatables have a common column in it(lets say ID).I want to merge all and get it in a separate data table.
example -
DT1 :-
Id , Name:
11 , A
12, B
13, C

DT2 :-
Id , Country:
11 , Australia
12, Brazil
14, Canada

DT3 :-
Id , age:
11 , 23
13, 34
14, 27

Expexted result-
DT4 :-
Id , Name,Country,age:
11 , A, Australia, 23
12, B, Brazil
13, C, , 34
14, ,Canada, 27

2

@Huzaib_Umar

  1. Read Data: Use the “Read Range” activity to read data from each of your input files into separate tables (DT1, DT2, DT3).
  2. Merge Tables: Combine DT1 and DT2 based on the “Id” column using the “Join Data Table” activity. The result will be stored in a new table called DT4.
  3. Merge with Third Table: Merge DT4 with DT3 based on the “Id” column again using the “Join Data Table” activity. This will further extend the information in DT4.
  4. Update Age:Go through each row in DT4. If an “Id” in DT4 matches an “Id” in DT3, update the “age” column in DT4 with the corresponding “age” value from DT3.
3

@Huzaib_Umar
use this sequence, this code works with ANY number of datatables as long they have the common “Id” column
Sequence3.xaml (13.7 KB)

Example
dt1
image

dt2
image

dt3
image

result
Id,Name,Country,State
11,A,CA,SS
12,B,CF,S1

4

Hi @Huzaib_Umar

you can use Join Datatable activity
in that use Join Datatype as Full join

let me know if you face any issues

image
image536Ă—582 7.32 KB

dt3 is the output of Join Datatable of dt and dt1
dt4 is the output of join Datatable of dt3 and dt2

dt4 is the final output

image
image624Ă—552 9.36 KB

5

We do have at least two strategies:

  • Full Joins over the different datatables & and then extracting the relevant columns from the final join result

OR

  • merge all data into a single datatable (Merge datatable activity, but will at first step has different result)
  • THEN flat down all rows belonging to the same ID
  • Reorder optionally the the column orders
6

This isn’t a merge, this is a join. Use the Join Data Table activity and designate ID as the matching column.

7

Hi @Huzaib_Umar

You can use the Join datatable activity for this type to merge the datatable to one.
In three datatables, there is a one column which is column by taking the reference of that column you can use the Join datatable activity which joins to one datatable.

=> First Use join datatable for dt1 and dt2. which gives Datatable1.
=> Second Use join datatable for Datatable1 and dt3 which gives the dt4.

Hope it helps!!

8

Thanks @rlgandu , but it’s fine for 3 datatables but it will be a quite lengthy process if we have around 10-15 DTs.

9

Thanks a lot @jack.chan :slight_smile: It’s working.

1 Like
10

Thank you for replying @Shiva_Nikhil , I am looking for if there multiple DTs i.e., 10-20

11

thanks @mkankatala it would definitely work for less number of DTs. But unfortunately it would be quite lengthy for large number of DTs

1 Like
12

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