Best way to combine two DataTables nicely

Learn
1

I have two DataTables, and I have to combine them by category. What is the best way to do this? I already have DataTableA and DataTableB, and I need a combined DataTableC.

DataTableA (Assume that each line is a row)
CategoryA
Tony
Michael
John
CategoryB
David
John
Kenny
Tommy
CategoryC
Taylor
Jose

DataTableB
CategoryA
Nancy
Jessica
CategoryB
Michelle
Brittany
Katy
CategoryD
Anna

Now I need a new DataTable that combines these two DataTables above by category. The item order under each category cannot be changed. Also, there can be some categories that exist in one of the DataTables, but not in the other. These categories still have to be included in DataTableC.

DataTableC is what I want:
CategoryA
Tony
Michael
John
Nancy
Jessica
CategoryB
David
John
Kenny
Tommy
Michelle
Brittany
Katy
CategoryC
Taylor
Jose
CategoryD
Anna

DataTableA should be the base datatable. If the same category cannot be found in dataTableB, you don’t need to do anything, but if a category doesn’t exist in DataTableA, but it does exist in DataTableB, it should be appended at the bottom.

How could we do this nicely?

2

Hello,

The following should do the trick with mostly UiPath activities. Given you can obtain a DataTable with only one column with distinct values of CategoryA

datatable.DefaultView.ToTable(True, "CategoryA")
  • Merge the two DataTables with Merge Data Table
  • Use Join Data Table with datatable.DefaultView.ToTable(True, "CategoryA") and previous result
  • Use Remove Data Column to remove unwanted columns from the result
1 Like
3

Thank you. Actually it is not just one column. I just simplified everything for this question.

So each row has more data columns like, “name”, “age”, “location”, etc. and this starts from column B on Excel. Colunm A has some random strings inside.

Column A is mainly used for category names. so it is like…

CategoryName | Name | Age | Location | …
Category A | (empty) | (empty) | (empty) | …
fjanfen | Tony | 23 | Dallas, TX | …
kfenkeg | Michael | 23 | Dallas, TX | …
kggk | John | 21 | Fort Worth, TX | …
Category B | (empty) | (empty) | (empty) | …
wggk | David | 24 | Dallas, TX | …

This is how both DataTable A and B look like, except that the data are different.
I need all data rows to be preserved in DataTable C. I just need to combine DataTableA and B, so that each category has data from both DataTables, where data from DataTable A comes first, and then data from DataTable B comes second.

4

I think the process should achieve that because:

  • Merge DataTable combine your DataTable preserving the order (and columns).
  • On the Join Data Table, the second member of the join have all columns you need (that’s the merged datatable). the first member is meant to sort by the second member by its values.
  • The Remove Data Column get rid of that extra first column to keep only the original columns.

Can you try it and tell me how it does?

5

Thank you for your reply, but I am not quite sure how to fill each blanks.

Merge Data Table:
Source(property): DataTableB
Destination(property): DataTableA

→ I believe that DataTableA now has a merged DataTable.

Join Data Table: (I am not sure exactly what to fill in the wizard)
Input DataTable1: DataTableA???
Input DataTable2: ???
Join Type: Inner
OutputDataTable: ???
Column Table 1 — Operation — Column Table 2
??? - ??? - ???