Insert New Column at required position in Collection

[vinodchinthakin]

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

[devneetmohanty07 · Accepted solution]

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

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

[vinodchinthakin]

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

[devneetmohanty07]

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

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

[vinodchinthakin]

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

[devneetmohanty07]

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

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

[devneetmohanty07]

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

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

[vinodchinthakin]

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

[devneetmohanty07]

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

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

[ErjolaMema]

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