Data Table - Add Duplicate Columns

I’m importing the following spreadsheet into a data table (and various similar spreadsheets) using the UiPath.Excel.Activities.ReadRange activity

As you can see, there are a number of columns that start Z, and some of these columns may have duplicate column names.

In these cases, I want to add the values together, and only have one column in the data table for that heading. So in the example above, the values for Z54 would be added, leaving just a single Z54 column.

I need the headers in the datatable to be the headers in the spreadsheet, so I can’t uncheck the “Add Headers” in options on the ReadRange Excel activity.

Is there a simple way around this? Thanks in advance!

Hi @andrewjames

I do not think my solution will be a simple one or one that you wouldn’t have already thought about. But here it is.

To solve the issue of identical column names, read the table with the headers box unchecked. This simply means that your columns will have generic names Column1, Column2, etc. The first row of your Data Table will contain all actual column names.
Now, figure out which columns you want to merge and simply do a For Each on the Data Table and add the values from column K to column G.

Afterwards, you need to clean up a bit:

  • assign actual column names from the first row of your Data Table to the columns of the Data Table (you can use a simple Assign activity for that: yourDataTable.Columns(0).ColumnName = "Forename"
  • delete that row from the Data Table after done
  • delete the column K from the Data Table as it is no longer needed

I hope it helps a little bit.

3 Likes

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