Collection

[AS]

Hi All,

I have a Collection, i Want to set the first row Values As Header Names but it has Duplicate values ie why it is not setting the header names, For Ex, in first row "Last Movement" is repeating again and again. How can i delete these duplicate values and set the first row as headers. Please Find the the Image Attached .

------------------------------
AS
------------------------------

[harish.m]

Hi ,

you Can utilize "collection manipulation" VBO - Action "Set column names from the first row"

------------------------------
-----------------------
If I answered your query. Please mark it as the "Best Answer"

Harish M
Lead developer
America/New_York TX
------------------------------

[AS]

Hi Harish, 

This action is not allowing me to set the column names from first row as the values are duplicates, please see the image

------------------------------
A S
------------------------------

[harish.m]

Hi,

 How are  you reading this data? May be try different approach to read the data and see whether it  is showing different result

There are multiple ways to delete it.
1)You can  get the column Names using COllection Manipulation VBO 
if the column Name contains text called "column" then delete that column
2) Check if the column values are empty  except the first row then delete the row ( It not idealistic but based on the data )

Once you delete the columns then you can use Set column Name from first row action.

Because of the dataTable or collection constraint  it is not working when you have two different columns with the same name It
Column Name are not case sensitive that being aid you can create some thing like COL1 and col1 but not COl1 twice.

------------------------------
-----------------------
If I answered your query. Please mark it as the "Best Answer"

Harish M
Lead developer
America/New_York TX
------------------------------

[AS]

Hi, 

I am reading this data as get worksheet as collection (fast). 

Not using Oledb because sheet name is dynamic. 

Also, can you please elaborate your solution, I am not getting it properly. 

------------------------------
A S
------------------------------

[Mukeshh_k · Accepted solution]

Hi A S - There's no need to go round about everything to get this done - this could be done in two steps with a custom modified code - Please see below:
Get worksheet as collection as you are doing to get the Data Table/Collection - Once you have the data Table ready as you showed in your query - for two of your requirements : i.e. first to delete the duplicate in column (I assume you would want to delete all the duplicate columns after the first occurrence below are the steps :

1) Open any of your Collection Manipulation Extended Object - Create an Action : Delete Column by Duplicates : Set Input as Collection In in the start Stage and in the Code Input, similarly create a Collection Out and set in the Output of code stage and the End stage refer below : 





Open Code Stage : Write the below code in the code stage -

For iColumn = Collection_In.Columns.Count - 1 to 1 step -1

Dim colValue = Collection_In.Rows(0)(iColumn)

Dim duplicateCount = 0

For jColumn = 0 to iColumn - 1

If Collection_In.Rows(0)(jColumn) = colValue Then

duplicateCount += 1

End If

Next

If duplicateCount > 0 Then

Collection_In.Columns.RemoveAt(iColumn)

End If

Next

Collection_Out = Collection_In.Copy

This code witll remove all the duplicates found in first row and its associated columns.

Step 2: Create Another customer action and rename that to Create Collection first row as Header - Follow similar steps of adding the Collection In and Collection Out as input/Output:
Open Code Stage and copy paste below code : it will rename the Header with first row names and then remove fierst row as that should now be the header of collection
Add Code:

Dim dt As New DataTable()

For Each col As DataColumn In Collection_In.Columns

    dt.Columns.Add(col.ColumnName, col.DataType)

Next

For iColumn = 0 to Collection_In.Columns.Count - 1

    Dim colName As String = Collection_In.Rows(0)(iColumn).ToString()

    If Not String.IsNullOrEmpty(colName) Then

        dt.Columns(iColumn).ColumnName = colName

    End If

Next

Collection_In.Rows.RemoveAt(0)

For Each row As DataRow In Collection_In.Rows

    Dim newRow As DataRow = dt.NewRow()

    For iColumn = 0 to Collection_In.Columns.Count - 1

        newRow(iColumn) = row(iColumn)

    Next

    dt.Rows.Add(newRow)

Next

Collection_Out = dt

Results :
Collection before these two stages:


Collection after these two stages:



Let me know if you face any difficulties implementing this.

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

[Mukeshh_k]

Let me know if you face any difficulties with libraries although this would only require basic libraries and those might already be present in your collection manipulation object.

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------