Solved Adding Serial Number Column to existing collection

Hi All,

i am storing data in collection from web table and i want to a New Field in that Collection with Field Name as Serial Number and value should be add as 1,2,3,4,5...and so on.

is there any way to add serial number in collection ? i heard we can do using code stage but i do not know how can i achieve this.

can anyone please help me out with this....

Hi,
Once after collection has rows extracted from web table, loop that collection within that loop stages use calc action stage to add a new column and calc stage to fill the row i.e 1,2,3,.... use a counter data item initialize it with 1 and increment for every loop and store that result in that column.

Hi naveedraza,

There are multiple ways to do this.
- One of them is to paste the collection into an Excel and then just set the cell value for the new column as SrNo (only the column heading).
Then paste back the excel data to a collection. You now will have an empty additional column where a Calc stage will need to be used to set the values for the new column using a loop.
Alternatively you could also very well write the 1,2,3...values in the excel itself and then paste the entire sheet to a collection and then nothing more to do further.
To increment the values in the Excel itself you can follow the below steps
i. Paste the collection to Excel
ii. Use Set cell value action and set the column heading as SrNo
iii. Use Set cell value action and set the value in C2 as 1
iv. Use Set cell value action and set the value in C3 as "=$C2+1"
iv. Use Copy Paste Worksheet Range action and copy from C3 to "C"& [LastRow] where LastRow is derived from Get Number of Rows action of Excel VBO.

No loop involved anywhere and the collection will be ready when this sheet is pasted into a collection.

View attachment 1530602615217.png

- Another option is to have a new Collection in Blue Prism with just one hard coded column heading as SrNo.
Then use a loop counter and increment the values till the number of rows in the other collection (there is an action to get the number of rows in the Collection VBO - not the Collection Utility VBO).
Once the new collection is ready use Merge collection with input as Collection 1 and 2, but the output allows only in a new third collection with all columns and data of 1st collection along with SrNo column and its incremental values.

- You can also write a Code stage to loop through the collection and add the column and its values from the Code itself. There are some Collection related Code on this forum in C# which you can take a look at and directly use it in C# or convert it to VB.net code if needed.

- The ROW function of Excel can also be made use of.
Just set this formula in C2 as "=ROW($A1)"
and do a copy paste from C2 till last row as explained above.
No looping involved particularly useful when large data is involved.
View attachment 1530603296590.png

You can do it in multiple ways depending on how large or small your data is.

Hi Sukesh and Vijay - Thank you so much for quick response and nice explanation really appreciated !

@ Sukesh: currently i am doing loop in collection to add Serial number.
@ Vijay: Vijay as you know i am a VBA Developer i can load collection data into excel and there i can add serial number again load back to collection.

I don't want to loop in collection because some time i will get 1000 plus rows in collection and off course i can follow Vijay instruction to achieve this. However i want to achieve same using Code stage, can we manipulate collection data using code stage in BP ?

Got it...i just wrote a code to add serial field with 1,2,3... value...

here are the steps.
1. i added new page in "Utility - Collection Manipulation" VBO
2. Added Start up parameter as "InputCollection" and End Up Parameter as "CollectionOut"
3. Added code stage with Input as "CollectionIn" , "ColumnName" and "StartRw" and Output as "CollectionOut", below is the code
4. Next i used this VBO Action in my Object using Action Stage and i am done

naveedraza said:

Got it...i just wrote a code to add serial field with 1,2,3... value...

here are the steps.
1. i added new page in "Utility - Collection Manipulation" VBO
2. Added Start up parameter as "InputCollection" and End Up Parameter as "CollectionOut"
3. Added code stage with Input as "CollectionIn" , "ColumnName" and "StartRw" and Output as "CollectionOut", below is the code
4. Next i used this VBO Action in my Object using Action Stage and i am done

Code:

If Not CollectionIn.Columns.Contains(Column) Then
    CollectionIn.Columns.Add(Column,Type.GetType("System.Decimal"))
End If
for each dr as System.Data.DataRow in CollectionIn.Rows
    dr(Column) = StartRw
    StartRw = StartRw + 1
next

CollectionOut = CollectionIn

Click to expand...

That is exactly what I was suggesting you here. Good to know that you got it worked out.

- You can also write a Code stage to loop through the collection and add the column and its values from the Code itself. There are some Collection related Code on this forum in C# which you can take a look at and directly use it in C# or convert it to VB.net code if needed.

Yeah ..Sure Vijay

Thank you so much. your advises are very good and workable