How to rename columns in excel having same column names?

Help Studio
, ,
1

Please have a look at the sample excel file.

image
image811×249 6.42 KB

Now I know a read range cannot be done since there are duplicate column names. So what can I do to rename the duplicate columns and then perform read range or maybe can we do both dynamically?

Your input/ideas are highly appreciated. Thanks!

2

Read Range without headers set in the activity. You can either start from A1 and then your above headers will be row 1. Or start from A2. Either way, you can then rename the columns and then remove the first row (if you started from A1).

3

Hi @Sunny_J ,

There are some steps that need to be taken to rename the columns in order to access the column values by their respective unique column names.

Two such approaches are provided below/available in the marketplace :

In this snippet, you could understand one such method to eliminate this problem using the new column names list that is to be provided.

Another approach is using the Custom Activity below, which renames the columns automatically - If Column name Code is duplicated, then after using the activity, it will rename the second duplicated column as Code(1)

1 Like
4

HI @Sunny_J

Checkout this sample xaml file and input file and implement in your project accordingly

Sequence.xaml (10.4 KB)
SampleExcel.xlsx (8.4 KB)

Overview of steps

  • Reading the sheet without headers
  • Looping through the DT.Row(0).ItemArray (i.e.the actual headers)
  • making a regex matches(with String.Join(“,”,DT.Row(0).ItemArray)) count if the matches having a count greater than 1 then lookup the range and append one with the column name
    image
    image998×859 31.4 KB

image

Hope this Helps

Regards
Sudharsan

1 Like
5

Hi,
I ran the xaml and I think it doesn’t take into account if the column names are repeated more than 2 times. So if they are, your script only adds 1 as a suffix to the column name for the first instance.