topic RE: Insert New Column at required position in Collection in Product Forum

Insert New Column at required position in Collection

vinodchinthakin — Fri, 03 Dec 2021 05:58:00 GMT

Hi @devneetmohanty07

I just found a code in C# on this Thread
which helps in Inserting a New Column before last column of collection. When I tried this code, the result am getting as Empty Output Collection.
As I can see by your previous threads, that you are an expert in VB.Net and collections , Could you modify this code to VB.net and make it to work as dynamic where I can Insert a Column at any required position.

DataTable Newdt = new DataTable("OutputDataTable");

for (int i=0; i<+ InputCollection.Columns.Count - 2; i++)
{
DataColumn col = new DataColumn(InputCollection.Columns.ToString(), typeof(System.String));
Newdt.Columns.Add(col);
}
DataColumn Newcol = new DataColumn(ColumnToAdd, typeof(System.String));
Newdt.Columns.Add(Newcol);
Newdt.Columns.Add(InputCollection.Columns[InputCollection.Columns.Count-1].ToString(), typeof(System.String));
OutputCollection=Newdt;

Thanks in advance.

------------------------------
vinod chinthakindi
------------------------------

RE: Insert New Column at required position in Collection

devneetmohanty07 — Fri, 03 Dec 2021 08:14:00 GMT

Hi Vinod,

Thanks for reaching out to me 🙂 As per your requirement, I have created a separate code on VB .NET only which you can easily use while extending the Utility - Collection Manipulation VBO. Please follow the below steps:

1) Create and action called 'Insert Column In Collection' with the given input parameters for the action: Collection (Collection), Field Name (Text) and Column Index (Number) along with output parameters: Message(Text), Success(Flag) and Output Collection (Collection) as shown below:

2) Add the code stage named Insert Column In Collection with the below parameters:

3) In the Code tab insert the following code:

Try

Output_Collection = Input_Collection.Copy

Dim Newcol As DataColumn = New DataColumn(Column_Name, GetType(String))
Output_Collection.Columns.Add(Newcol)
Output_Collection.Columns(Newcol.ColumnName.ToString).SetOrdinal(Column_Index)

Success = True

Catch ex As Exception

Message = ex.Message.ToString
Success = False

End Try

Test Results:

NOTE: Remember the Column Index is valid from 0 to N-1 where N is the number of columns in that collection

Hope it helps you out 🙂

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

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

RE: Insert New Column at required position in Collection

vinodchinthakin — Fri, 03 Dec 2021 13:29:00 GMT

Awesome @devneetmohanty07. It worked great. I suggest you to create a VBO on Collections and Publish on DX with new actions which are suggested by you in previous threads.
Could you help me more in providing code on following scenarios.
Scenario 1: Copy one column from collection 2 and insert into collection 1 at required position
Inputs:
Collection1
Field1 Field2 Field3
1 2 3
11 22 33

Collection2
FieldA FieldB FieldC
A B C
AA BB CC

Required Output Collection
Field1 FieldA Field2 Field3
1 A 2 3
11 AA 22 33

Scenario 2: Merge collection 2 into collection 1 at required position
Inputs:
Collection1
Field1 Field2 Field3
1 2 3
11 22 33

Collection2
FieldA FieldB FieldC
A B C
AA BB CC

Required Output Collection
Field1 FieldA FieldB FieldC Field2 Field3
1 A B C 2 3
11 AA BB CC 22 33

Thanks in advance.

------------------------------
vinod chinthakindi
------------------------------

RE: Insert New Column at required position in Collection

devneetmohanty07 — Fri, 03 Dec 2021 15:04:00 GMT

Thanks a lot @Vinod chinthakindi for your kind words and suggestion. I perhaps will look into more options on how to upload my asset on DX Exchange for sure and try to incorporate your use cases as well into it :D

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

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

RE: Insert New Column at required position in Collection

vinodchinthakin — Wed, 12 Jan 2022 06:00:00 GMT

Hi @devneetmohanty07, have you done any work around on my previous mentioned 2 Use cases. I will be glad if you had any update on them.

------------------------------
vinod chinthakindi
------------------------------

RE: Insert New Column at required position in Collection

devneetmohanty07 — Wed, 12 Jan 2022 14:00:00 GMT

Use Case - II :

Create a new action called 'Merge Collection At Position' by extending the 'Utility - Collection Manipulation' VBO. Add the following input parameters to this action:

- Input Collection One (Collection) : The input collection which needs to be merged.

- Input Collection Two (Collection) : The input collection where the merged collection needs to be added.

- Column Position (Number) : The position index of the column in the second input collection were the merged collection needs to be added. The index must start from 0 till N-1, provided that N is the total number of columns.

Add the collection named as Output Collection (Collection) and assign it to an output parameter. This contains the result of our operation.

Solution Workflow:

Add the code stage with the following parameters and code:

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

For Each dc As DataColumn In Input_Collection_One.Columns()
Input_Collection_Two.Columns.Add(dc.ColumnName)
Input_Collection_Two.Columns(dc.ColumnName).SetOrdinal(Column_Position)
Column_Position = Column_Position+1
Next

Dim rowCounter As Integer = 0

For Each dr As DataRow In Input_Collection_One.Rows()
For Each dc As DataColumn In Input_Collection_One.Columns()

        Input_Collection_Two.Rows(rowCounter).Item(dc.ColumnName) = Input_Collection_One.Rows(rowCounter).Item(dc.ColumnName)

Next
rowCounter += 1
Next

Output_Collection = Input_Collection_Two

Test Scenario:

I provided the input to this action as follows:

Upon executing the code, the results are as follows:

Let us know if this resolves your query.

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

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

RE: Insert New Column at required position in Collection

devneetmohanty07 — Wed, 12 Jan 2022 14:10:00 GMT

Hi Vinod,

Apologies since I missed your query as it was the part of the same post. From next time, if possible please post separate threads as it becomes easier for me to keep a track of all the queries on the Community Forum.

I am posting the solution for use case one in this post and the use case two has been posted in a separate thread message due to character limitations.

Use Case - I :

Create a new action called 'Copy Columns Among Collection' by extending the 'Utility - Collection Manipulation' VBO. Add the following input parameters to this action:

- Input Collection One (Collection) : The input collection from where the column data needs to be copied.

- Input Collection Two (Collection) : The input collection where the column data needs to be added.

- Column Name (Text) : The name of the column in the first input collection from where the column needs to be copied.

- Column Position (Number) : The position index of the column in the second input collection were the column data needs to be added. The index must start from 0 till N-1, provided that N is the total number of columns.

Add the collection named as Output Collection (Collection) and assign it to an output parameter. This contains the result of our operation.

Solution Workflow:

Add the code stage with the following parameters and code:

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

Test Scenario:

I provided the input to this action as follows:

Upon executing the code, the results are as follows:

Let us know if this resolves your query.

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

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

RE: Insert New Column at required position in Collection

vinodchinthakin — Thu, 13 Jan 2022 07:13:00 GMT

Thanks Devneet for providing the solutions for above Two Use cases. Both the solutions are working great.!

I would like to provide you a small suggestion,
Use Case - I : Copy Columns Among Collection Your code helps in copying a single column among collection. If we able to use a For Each loop within code stage its helps in copying more than one column and this results into a new action.

Apologies for including the different threads in same post. From next time, will try to post separate threads. I hope there should be an option to separate the different threads in a post for a owner(who creates actual thread).

Regards
Vinod

------------------------------
vinod chinthakindi
------------------------------

RE: Insert New Column at required position in Collection

devneetmohanty07 — Thu, 13 Jan 2022 07:36:00 GMT

Hey Vinod,

Totally agree with your suggestion, in fact I would have done that if you would not have specifically asked me to copy one column. If you carefully observe the use case II, this is actually what your suggestion is, the only difference being is I am taking all columns instead of few specific columns.

No worries for the different threads part, I actually missed on my own end as well. However, I am glad your queries got resolved finally.

Thanks for posting these interesting use cases for everyone in the community 🙂

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

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

RE: Insert New Column at required position in Collection

ErjolaMema — Thu, 31 Mar 2022 15:39:00 GMT

Hi Devneet ,

I tried this code and the problem is in the output .

The flag is shown true but the output collection is empty.
Where can be the problem ?

Thank ,

Erjola

------------------------------
Erjola Mema
------------------------------

RE: Insert New Column at required position in Collection

PabloSarabia — Thu, 31 Mar 2022 17:57:00 GMT

Hi @ErjolaMema

You must to put the same Collection as output from the input.

To summarise the code (C# language)

dt.Columns.Add("Col1", typeof(string)).SetOrdinal(0);
dtOut = dt;

Change 0 for your preferer position.

Hope this helps you.

See you in the community, bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
------------------------------

RE: Insert New Column at required position in Collection

ErjolaMema — Fri, 01 Apr 2022 08:30:00 GMT

Hi Pablo ,

Thanks for the reply ,but still isn't working

------------------------------
Erjola Mema
------------------------------

RE: Insert New Column at required position in Collection

AmolJangam — Fri, 01 Apr 2022 10:24:00 GMT

Hi Erjola,

Please try the following Digital Exchange asset, which is free and can perform the required task:

https://digitalexchange.blueprism.com/dx/entry/78038/solution/spgmi--utility-collection-booster

Hope it helps.

Thanks,
Amol Ashok Jangam.

------------------------------
amol jangam
test lead
infosys
Asia/Dili
------------------------------

RE: Insert New Column at required position in Collection

ErjolaMema — Fri, 01 Apr 2022 12:37:00 GMT

oki 😄

On Fri, Apr 1, 2022 at 12:26 PM amol jangam via Blue Prism Community <Mail@blueprism.email> wrote:

Hi Erjola, Please try the following Digital Exchange asset, which is free and can perform the required task: digitalexchange.blueprism.com/dx... -posted to the "Blue Prism Product" community

Blue Prism Product

Post New Message Online

Re: Insert New Column at required position in Collection

Reply to Group Online Reply to Group

Apr 1, 2022 11:24 AM

amol jangam

Hi Erjola,

Please try the following Digital Exchange asset, which is free and can perform the required task:

digitalexchange.blueprism.com/dx/entry/78038/solution/...

Hope it helps.

Thanks,
Amol Ashok Jangam.

------------------------------
amol jangam
test lead
infosys
Asia/Dili
------------------------------

Reply to Group Online Reply to Group via Email View Thread Recommend Flag as Inappropriate