Rename Duplicate Column in an excel

Help Forum
,
1

I have an excel with almost 190 columns and have observed that there are duplications in columns and I want to rename the duplicate columns.
I tried to read the excel so that I can rename the column but that is throwing column duplication error.
Tried to read execl without headers and change the particular column name as per index but while writing the excel the column headers are not properly working.
Is there a way to read only column names from excel, rename the duplicates and save the column names and this should not change/effect any value in excel.
Please check the excel I am atatching for refernce.In this excel column 25 and column 91 have duplicates and I should rename/change column 91.
Book1.xlsx (13.1 KB)

Can you please provide the flow how to do it
Thanks in Advance.

1 Like
2

Do you want to find exact duplicate or contains ? I see ‘Supplying Plant’ and Supplying Plant1’

image

3

use this module

ReadExcelTableWithDuplicateColumns.xaml (29.6 KB)

pass in arguments like this
(you can ignore out_newColumnNameList and out_originalColumnNameList)
if it sees duplicate columns, it will rename them e.g. Supplier → Supplier-0 (1st duplicate) → Supplier-1 (2nd duplicate)

it will only rename in the datatable variable, NOT inside the excel, the headers wont change in the excel

image
image729×589 23.8 KB

4

Hi @deepaksvg99 ,

Could you check if the Below Component works for your case ?

5

Hi SrenivasanKanna,
That should be the expected output.
Have two columns as Supplying Plant and need to change the name of second one to Supplying Plant1

6

Hi supermanPunch,
It seems to be external activity but I should not use external activity in my current project

7

Hi jack.chan,
I tried with xaml shared but could not process it as it have more xamls required in the invoke methods.

8

Hi @deepaksvg99

One approach could be this

  1. Read the excel without the headers

  2. Store the values of row(0) in a string array

ColArr = dt_Data.Rows(0).ItemArray.Select(Function(c) If(c.ToString.Trim.Equals(""), "Column", c.ToString)).ToArray
  1. Create dictionary to get the count of column names
ColCountDict = ColArr.Distinct.ToDictionary(Function(k) k, Function(v) ColArr.Count(Function(x) x.Equals(v)))
  1. Reverse the ColArr
ColArr = ColArr.Reverse.ToArray
  1. Iterate through the column name array to change the name of column names if they are duplicate (i.e., appending the number)

image
image621×550 12.8 KB

image

  1. Remove the first row from the data table

image

Refer the xaml

DuplicateColumnNames.xaml (10.6 KB)

2 Likes
9

very sorry, here you go

download this version and replace the old one
ReadExcelTableWithDuplicateColumns.xaml (24.8 KB)
download these 2 as well and place in the same folder
LetterToExcelColumnNumber.xaml (6.4 KB)
NumberToExcelColumnLetter.xaml (7.1 KB)

10

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