How to join data table from multiple excel files

[harizd]

Hello everyone,

I have three separate excel files that I need to filter, extract and compile it in a single excel file.

Below three are the example of the mock excel files I try to replicate based form my actual project:

First file

Second file
(this second file has an extra row of information I don’t need which I need to filter out.)

Third file

*The highlighted headers are the information I want to extract from the column

I want to output those extracted information to a new file and compile it like this:

This is the example of the output I have done manually

I tried using the merge table activity to join all three tables together but it came out with a lot of row redundancies and blank spaces, and the information are not matched and not in proper order.

Can anyone help or suggest me any other way to do this?

Thanks,
Hariz

[olgu (Olga)]

May you try to build datatable with 7 columns in the order as you want in output. Then read excel one after another, and add there data in the adequate columns, checking data which might match. I think the only matches that suits that case are “ID” and “Name”, so just try to find in the datatable if any of these keys exists: Is so, add data on this specific row, if not, add to a new, blank row.

[harizd]

thanks for the reply. I will try this

[shankm]

Hi @harizd,
You can also use the data table join. That will be a little bit difficult. But, can achieve what you need.
NB: you need to use some codes

[sanjayshaswani (Sanjay Kumar Shaswani)]

HI @harizd,
Read all the 3 tables into 3 datatables (DT1, DT2, DT3)

“Join Data Tables” activity can be used to join different tables.

use one “Join Data Tables” activity to join first 2 tables (DT1 & DT2), which will generate a new datatable, Say: TempOutputDT1.

use second “Join Data Tables” activity to join 3rd table (DT3) and TempOutputDT1, which will generate a new datatable, Say: TempOutputDT2…

then use assign activity and this function to choose the columns you need:
New System.Data.DataView(TempOutputDT2).ToTable(False,{“ID”,“Name”,“Age”,“Height(CM)”,“Weight(KG)”,“BloodType”,“Favourite Food”})

[harizd]

oh wow! thanks @sanjayshaswani

This is really helpful! I’m still not familiar with the programming aspect of UIPath. Could you explain to me what does this line of code that you had written in your previous comment mean?

New System.Data.DataView(TempOutputDT2).ToTable(False{“ID”,“Name”,“Age”,“Height(CM)”,“Weight(KG)”,
“BloodType”,“Favourite Food”})

I recognize that that TempOutputDT2 is a variable and {“ID”,“Name”,“Age”,“Height(CM)”,“Weight(KG)”,
“BloodType”,“Favourite Food”} is an array of how the table order would be. But may I know what is System.Data.DataView() and .ToTable? and what does the False boolean do in this line of code? I genuinely want to know and learn more about this.

Thank you,
Hariz

[sanjayshaswani (Sanjay Kumar Shaswani)]

HI @harizd,
Thanks for Asking… you can find out about each of the methods by yourself in UiPath itself.

click next to the method say “Dataview” or “ToTable” and press ctrl+space and it will display a small description of the method as shown below.

BTW false is specified to tell weather we want distinct rows only.