Extract Column values in Blueprism

Sukesh Kumaru

Active Member
Hi anjali2111993,
Can you be more specific while asking something that you are trying to explore in Blue Prism.
Okay let's get in to the picture.
According to my understanding, col1 value can be extracted from a collection by using "collectionname.column_name" and store that value in a data item(use this in expression editor of calc stage), if you use this syntax in between a Loop stage then all the col1 values are picked one after the other, and use accordingly.
If your query is not this, then please don't hesitate to reach me out.
 

anjali2111993

New Member
1529424936483.png

I have added column value in the calculation stage and sent the result to collection reverse but it is not working , if I use data item then how I can get the complete column data in that?
 

Sukesh Kumaru

Active Member
please specify the colnname.colmnname in the place of "reverse" or else it can be achieved by using a multi-calc stage
first expression is "source_collnname.06/19/18" and store result in any data item(for ex."colval") and in
next expression is: "colval" and store result in "target_collnname.colmnname".
 

anjali2111993

New Member
Hi ,
Can I use the code stage directly??
Is there a function available in VBA to add column ?? same as remove below

BP_Collection_Out = BP_Collection_In
BP_Collection_Out.Columns.Remove(Column_Name)
 

VJR

Well-Known Member
Hi anjali2111993

If you do not want to loop through the collection and would like to avoid coding then this can be achieved by doing the below

- First count the total number of rows in URL_credentials because at design time you won't be sure how many rows are there
- Then copy those many rows to a new output collection ('Result' in below diagram)
- Delete all the unwanted columns from 'Result' so that only the column you want remains in the Result collection.
The Delete Column supports deleting one column at a time.

Please post back with more details if you are looking for something else.

1529239752473.png
 

AshutoshMahale

New Member
Hi @VJR,

Your solution is perfect for less no of columns, but I have a situation where I have to delete multiple columns (>1000) and retain just 1 column in the collection. Deleting 1000 wont be that much time efficient as it would run inside a loop, instead is there any other functionality either in Collection manipulation or MS Excel VBO where I can just copy 1 column & its entire data in other collection. Or I will have to use a custom code for it as well?

TIA!
 

VJR

Well-Known Member
Hi AshutoshMahale,

While getting the data from Excel you could use 'Get Worksheet Range as collection' on that 1 column which will then retrieve a collection of only 1 column.
 

Hema Arul

New Member
Hi VJR,

Could you pls explain us with screenshot on how to retrieve only one column from collection.

Thanks in advance.
 

Emil.Tophoej

New Member
Hi @VJR,

What can be done if I have to extract just 2 or 3 columns in a excel and get it into a single collection?

You can use that (Get Worksheet Range as collection) multiple times and then use "Collection manipulation - Merge Collection" (multiple times?) to form a single colleciton with the desired rows.
 

VJR

Well-Known Member
Hi @VJR,

What can be done if I have to extract just 2 or 3 columns in a excel and get it into a single collection?
This will depend in how your columns are - sequential or non-sequential.
  • If sequential (eg; A, B, C) then you can directly use the 'Get Worksheet Range as collection' as "A1:C100" into a single collection.
  • If non-sequential (eg; A, C, E), then there are two options-
a. Use 'Copy and Paste Worksheet Range' for each of these columns to another (temporary) sheet. Pretty simple if you correctly give the source and destination parameters.
Once you have the columns one after the other, then using a 'Get Worksheet as collection' will give all three columns in a single collection.
Then you can clear off the sheet or delete it.

b. Another option is to get the data from individual columns of the sheet and then merge the collections as advised by Emil.Tophoej.
 
Is there are way like instead of deleting single column at a time, deleting multiple column or
to get a single or desired number of columns from a collection..

Example : I have collection of 8 columns and I need only one column based on the header ?

BP_Collection_Out = BP_Collection_In
BP_Collection_Out.Columns.Remove(Column_Name)

Is there a function something like instead of Remove I can get one column ?
 

Sukesh Kumaru

Active Member
Hello,

Instead of deleting all those unnecessary columns from collection before getting the data from excel (I guess), if the data is in excel file then you can follow a easy approach to get the required amount of data from bulk, i.e. by using "Data-OLEDB" VBO write a simple SQL Query and bring the required data from it to collection.

Good Luck.
 
Thanks Sukesh, Data-OLEDB is a easy approach but we have been restricted from using Data-OLEDB in production Env, Hence I am looping around Delete column action
 
Top