Get only specific columns from Excel worksheet to Collection using Code stage

[SufiyaS]

Hi All,

I need to extend the MS Excel VBO action "Get worksheet as collection"to get only specific columns , which can be specified either as comma separated string or collection. Can you please assist me with the code stage for the same?

Regards

------------------------------
Sufiya S
------------------------------

[jorge.barajas]

Hi Sufiya, 

You can try reading the entire worksheet to a collection with the MS Excel VBO and then split the collection as an alternative. This mechanism already exists in the 'Split Collection' action of the 'Utility - Collection Manipulation' VBO and works quite well in these cases. This may save you some code writing!

Cheers!

------------------------------
Jorge Barajas
Blue Prism
Senior Product Consultant
Austin, Texas
------------------------------

[NicholasZejdlik]

In case it's still useful, here's some code I created a while back for this particular issue. After reading data from Excel, you can call this code to select only specific columns.

Inputs:
Collection In (Collection)
Column Names (Collection)

Outputs:
Collection Out (Collection)

For Each Row As DataRow In Column_Names.Rows
	If Not Collection_In.Columns.Contains(Row(0)) Then
		Throw New Exception("Field '" & Row(0) & "' does not exist in the collection.")
	Else
		Collection_Out.Columns.Add(Collection_In.Columns(Row(0)).ColumnName, Collection_In.Columns(Row(0)).DataType)
	End If
Next

For Each Row As DataRow In Collection_In.Rows
	Dim NewRow As DataRow = Collection_Out.NewRow
	For Each Column As DataColumn In Collection_Out.Columns
		NewRow(Column.ColumnName) = Row(Column.ColumnName)
	Next
	Collection_Out.Rows.Add(NewRow)
Next​

Whatever column names are specified in the Column Names collection will be selected.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

[SHaikh]

Hi Nicholas,

Thanks a lot for your help!!

I am currently facing some issue with the instance handle  though so will fix that and try this out , just wanted to confirm how are the Column names are being specified in the Column Names Collection . It is a single column in Column Names Collection and cell values specify the required columns right?

A screenshot for how Column Names Collection looks would help .

Regards

------------------------------
SHaikh
------------------------------

[NicholasZejdlik]

> It is a single column in Column Names Collection and cell values specify the required columns right?

You got it! Since the code uses For Each over the Column Names collection, and refers to the values using Row(0), it'll grab whichever column names are in the first column of the collection, and each row would correspond to a different column name:

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

[SHaikh]

Thank you Nicholas for your help !!!!  🙂

------------------------------
SHaikh
------------------------------

[MarinaDutta]

Hi Nicolas,

Kindly can you help me in how to select the columns B,C,D,E,F after filtering AC column with @ symbol.

​

------------------------------
Marina Dutta
------------------------------

[PvD_SE]

Hi Marina,

There seem to be two things going on:

• Filtering a collection for a given value:
  This can be easily done by using BP out of the box actions
• Selecting only a few columns:
  For the selection of only a few columns I refer to previous threads in this community that handle exactly that and even provide an excellent solution in the form of a Code stage. Obviously, this can also be done by using native BP actions.

Note that a few examples of related content are kindly provided by BP in the bottom of this thread.

#BPTechTips
As searching this community for me rarely provides what I am looking for, I can recommend searching for whatever you are looking for via your favorite search engine (google in my case), as this is more likely to point to answers related to your question. Just a thought...

------------------------------
Happy coding!
Paul
Sweden
------------------------------