Copy Column from one collection to the other - Code stage

[Jan-HendrikLieb]

Hi All.

I have a collection with 40 columns, with all rows filled with data

I need to copy a specific column with all the data in that column to another new empty collection,
which will only contain the data from this column.
From there on, the values will be checked for different rules, e.g. correct format, length etc.

Every column has to be copied in this way, to a temporary collection with one column.

Right now I pass all values seperately with a calculation stage inside a loop with: add row, Read Collection Field, pass value with calc stage.

But it takes much too long, I have collections with 5000 rows and often the process gets terminated.

All other solutions for this problem I have found so far e.g. Split collection dont work for my use case.

I think the best way would be to create a code stage with an input collection, output collection, and a data item with the name of the column that has to be copied.

Does anyone know how create a code stage for this, or has a performant solution for my problem?

Thank you in advance,

Jan-Hendrik

[devneetmohanty07 · Accepted solution]

Hi @Jan-HendrikLieb

I created a custom object few months back for someone in the community which I can't recall. There was this action I created called as '​Copy Columns Among Collection' where it was primarily used for copying any specific column from an input collection to an output collection at any given position starting from '0'. I have attached the sample business object for your reference but if let say you are unable to import the attached file due to any organizational policy or compliance restriction, then in that case let me show below how you can create a similar action below:

First, extend the business object named 'Utility - Collection Manipulation' VBO and add an action called as '​Copy Columns Among Collection' where you need to use the following input and output  parameters:





Once you have mapped the parameters to individual collection and data item stages, you need to add a code stage as shown below:



Code:

Dim columnCount As Integer = Input_Collection_Two.Columns.Count
Dim additionalRowCount As Integer = Math.Abs(Input_Collection_One.Rows.Count - Input_Collection_Two.Rows.Count)

If Column_Position > columnCount Then

	Throw New Exception("The column position provided is out of range")

End If

If (additionalRowCount > 0 And Input_Collection_One.Rows.Count > Input_Collection_Two.Rows.Count) Then

	For i As Integer = 1 To additionalRowCount

		Dim newRow As DataRow = Input_Collection_Two.NewRow
		Input_Collection_Two.Rows.Add(newRow)

	Next

End If

Input_Collection_Two.Columns.Add(Column_Name)

Input_Collection_Two.Columns(Column_Name).SetOrdinal(Column_Position)

Dim rowCounter As Integer = 0

For Each dr As DataRow In Input_Collection_One.Rows()

	Input_Collection_Two.Rows(rowCounter).Item(Column_Name) = Input_Collection_One.Rows(rowCounter).Item(Column_Name)
	rowCounter += 1

Next


Output_Collection = Input_Collection_Two

Now, you can test the workflow with the following inputs:


Note: If you wish to copy the column to an empty collection then the column position will always be 0 otherwise you will get an exception. This action can be used with any kind of collection empty or filled, you only need to ensure that proper column position is passed which actually exists in the target collection where the column needs to be copied.

Once you run the workflow, you get the following output:


You can test the logic with different scenarios and you should be able to get it working. Please let me know in case of any further issues.

------------------------------
----------------------------------
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

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

[Jan-HendrikLieb]

Thank you so very much @Devneet Mohanty !

It works perfectly, as intended, Simply amazing!
I searched for forever for a solution to this problem

Also many thanks for the well written and detailed explanation how to create the Code Stage,
as you have guessed, i wouldnt have been able to import your VBO due to security reasons.

Best wishes,

Jan-Hendrik

[devneetmohanty07]

Really glad @Jan-HendrikLieb that it worked for you :)) Yes exactly I also faced these issues in past while sharing the code file directly in my previous organizations. Great thing is that the solution worked for you and also thanks a lot for posting your doubts here.

Keep us posted in case any other requirements come your way in the near future and hope to see you in community :))

Have a nice day ahead!​​