topic RE: Get only specific columns from Excel worksheet to Collection using Code stage in Product Forum

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

SufiyaS — Mon, 06 Apr 2020 16:18:00 GMT

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

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

jorge.barajas — Wed, 15 Apr 2020 16:28:00 GMT

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

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

NicholasZejdlik — Tue, 21 Apr 2020 20:47:00 GMT

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

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

SHaikh — Wed, 22 Apr 2020 06:10:00 GMT

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

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

NicholasZejdlik — Wed, 22 Apr 2020 13:00:00 GMT

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

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

SHaikh — Fri, 24 Apr 2020 05:52:00 GMT

Thank you Nicholas for your help !!!! 🙂

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

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

BoluwatifeEfuwa — Tue, 09 Mar 2021 12:24:00 GMT

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

MarinaDutta — Sat, 29 Jan 2022 17:20:00 GMT

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

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

PvD_SE — Mon, 31 Jan 2022 09:01:00 GMT

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