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?
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.
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”})
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.