How to join data table from multiple excel files

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:

image First file

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

image 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:
image

image 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

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.

3 Likes

thanks for the reply. I will try this

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 :slight_smile:

1 Like

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.
FirstJoinDataTables

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

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”})
Assign

6 Likes

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

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.

1 Like

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