Append two DataTable with same columns but in different order

Help
, ,
1

Hi all, this is my first post in this wonderful UIPath community.
I’m new in UIPath and I’m starting to learn to work with it.
I’m trying to simulate a sequence that I developed in Python and I want to try to do in UIPath.
I have 2 excel files (excel1 with 10000 rows, excel2 with 5000 rows) and I need to create a third excel that will be the concatenation of that two files (excel 3 with 15000 rows).
Excel1 and Excel2 have the same column but in different order, for example in Excel1 I have the column “ID” as third column, in Excel2 the column “ID” is in the first column.

I created an “Excel application scope” for each excel file, where I created a “Read range” activity where I specified an output variable. At least, I created another “Excel application scope” where i create an “Write range” activity where I specified the name of a new file and the output variable of first excel, and than I created an “Appen range” activity with the other variable in order to append data of second excel. The problem is that the result is not correct because the column are not in order… Under the column “ID” of the first excel, I have another column of the second excel.

Can someone suggest me how to solve it?

Thank you very much.

2

Hello,

You can use Merge Data Table activity. Here’s an example: Main.xaml (14.3 KB)

4 Likes
3

Perfect, thank you. I obtained an Excel file with the correct columns.
Can I ask you how does “Merge Data Table” works? Does it recognize the same columns by name and then it appends data?

4

Hi,

The file was not downloading and it is showing code when i am trying to download the file.

5

Yes, that’s pretty much it. The column names and types have to match. If the column names match but the types do not then you’ll get an error. If the column names don’t match then both columns will be added (provided the MissingSchemaAction property is set to ADD). The order of the columns is established by the target DataTable (the one in the Destination property).

1 Like
6

Right click on the link and click Save link as.

1 Like
7

Thank you @florin.stan!!
If I can, I’d like to ask you what kind of activity I can use to add a new column to the data table after I have used “Read range” from an excel file.
In other terms, at first thing I created an “Excel application scope” where I specified the excel file’s path. Then, I created a “Read Range” activity where I have specified the name of a DataTable. After that, I dragged and dropped the activity “Add Data Column” where I specified in the properties:

  • Column : New DataColumn(“date_rif”)
  • DataTable : (the data table output of previous activity)
  • Default Value : 2014

But, after execution of the robot, the result excel has a column with value 2014 but without a column name “data_rif” as I specified in the properties.

Can you help me to understand why?

8

Please verify if AddHeaders property of Write Range activity is checked.