topic RE: Duplicate column names are values in the excel file in Product Forum

Duplicate column names are values in the excel file

Ravig — Tue, 09 Aug 2022 10:51:00 GMT

Hi All,

Please help me out..
Duplicate column names and values in the excel file

I am downloading one excel file from website this file has duplicate column names and values. When tried to get into in collection getting an error as below.
I have checked there no object available to identity the duplicate column. I have tried in code stage using VB script no luck please help on this.

Error at code stage

Code:
Global references
Global code references

Or this there any other possibilities please let me know.

------------------------------
Ravi g
------------------------------

RE: Duplicate column names are values in the excel file

devneetmohanty07 — Tue, 09 Aug 2022 11:38:00 GMT

Hi Ravi,

Ideally if you try to read this data into a collection directly using any VBO, you always will face this issue. My recommendation here would be to get the list of all the header rows from excel. You can use a logic to iterate cells in first row and keep appending values to a collection till you find an empty cell. Then based on those values you can identify which values are duplicate in your case, get the index of those rows and based on that you can update the header name. If lets say you got fourth row as a duplicate which has the value PO#, update the cell 'A4' with the same value suffixed by some identifier like '_1' which should be 'PO#_1'

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Duplicate column names are values in the excel file

EmersonF — Tue, 09 Aug 2022 11:38:00 GMT

Hi Ravi, how are you? Well you can run the macro to remove the duplicate columns before opening with the collection, what do you think of that?

------------------------------
Emerson Ferreira
Cons, Intelligent automation
Avanade Brasil
Recife
+5581988869544
If my answer helped you? Mark as useful!
------------------------------

RE: Duplicate column names are values in the excel file

Ravig — Tue, 09 Aug 2022 11:58:00 GMT

Hi Mohanty,

Thanks for update.

Here not only header duplicate and values of column also duplicate.
here is example screenshot

------------------------------
Ravi g
------------------------------

RE: Duplicate column names are values in the excel file

Ravig — Tue, 09 Aug 2022 12:00:00 GMT

Hi Ferreira,

I am fine thank you.. and you?
Thanks for reply.

Yes correct.

------------------------------
Ravi g
------------------------------

RE: Duplicate column names are values in the excel file

arghya.b — Wed, 24 Aug 2022 16:39:00 GMT

@Ravig

Hi Ravi,

I have faced the same issue recently and found a way to resolve that to handle blank or duplicate column with excel.

Just modify or create the "Set Columns Name From First Row" Action on Collection Manipulation Object with the below code:

Dim iThisColumn as integer = -1
Dim ColumnNameString as string =""
For Each Column As DataColumn In Input_Collection.Columns
iThisColumn +=1
If(CStr(Input_Collection.Rows.Item(0).Item(iThisColumn))="")
Column.ColumnName="Column" & iThisColumn
Else if(ColumnNameString.Contains("*" & CStr(Input_Collection.Rows.Item(0).Item(iThisColumn)) & "*"))
Column.ColumnName = CStr(Input_Collection.Rows.Item(0).Item(iThisColumn)) & iThisColumn
Else
Column.ColumnName=CStr(Input_Collection.Rows.Item(0).Item(iThisColumn))
End If
ColumnNameString =ColumnNameString & "*" & CStr(Input_Collection.Rows.Item(0).Item(iThisColumn)) & "*"
Next
Output_Collection = Input_Collection

Let me know if this helps.

------------------------------
Arghya Bhattacharyya
Senior Consultant
Asia/Kolkata
------------------------------