How compare and merge different data tables?

Learn Academy Feedback
1

Hi all, I have 2 data tables dt1 and dt2 in that dt1 has 5 columns and dt2 has 4 columns from this one columns has common data,
so if the column 1 of dt1 equal to column 1 of dt2 then merge the data except column 1.
Please help.

2

Hi @Arnold_Sch,

First, you can use Join Data Table activity here to merge data on specific common values from column. As shown in below shot,
image

Then, use Filter Data Table activity to remove that common value columns as you want it that way. As shown in below shot,
image
In output columns tab you have to select Remove radio button and then, specify those 2 column names.

Note - Output of the Join Data Table activity should be Input for Filter Data Table activity (dt2)
That’s it :slightly_smiling_face:

1 Like
3

@samir This works but actual problem is I have same data in column1 in both dt1 and dt2, so now I need to check if column 1 of dt1 matches with column1 of dt2 then merge remaining columns(i dnt want column1 data to be write in output excel file) write output in another excel file.

4

Yes @Arnold_Sch, this will work as you want.

Here’s one example. I’m attaching here sample workflow for your better understanding —> test.xaml (10.9 KB)
image image
image

Check both build data table data and output, you’ll see that there’re only 2 records are matching (Name from dt1, FirstName from dt2) and result datatable have no name, firstname coulumn but rest of those columns.

5

@samir this is working, but still column 1 of dt2 writing into output file.

6

see @Arnold_Sch ,

you can in messageBox, there’re no Name, neither FirstName column in output

In you’re case, if key column name for both datatable is same (e.g Name) then you’ll get Name, Name_1 columns in o/p datatable, so you have to remove Name_1 column as well. For your better understanding here’s one example.

dt1 columns —> Name | Age
dt2 columns —> FirstName | Country

You can see that, key column names form both datatables are different (Name coulmn in dt1 & FirstName in dt2)
then, after join dataTable, output datatable (dt3) have these two column name along with remaining columns
dt3 columns —> Name | FirstName | Age | Country
So, to remove those columns in Filter DataTable, here’s no problem, because we’ve specified dt1.Columns(0).ToString and dt2.Columns(0).ToString.

But if they’re same, as following…
dt1 columns —> Name | Age
dt2 columns —> Name | Country
Name in dt1 and in dt2 as well, then, o/p datatable can’t have 2 columns with same name so it concatenate 2nd with _1, as shown below,
dt3 columns —> Name | Name_1 | Age | Country

this might be in your case… so to remove that column (Name_1), you can use
image
Or directly specify column names like, “Name” and “Name_1”

that’s it. :slightly_smiling_face:

1 Like
7

@samir Thanks a lot , it works.

8

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