Column Reading

[SeanQuirke]

Hello everyone,
Im new to Blue Prism and I have a project where I need to read a certain column from Excel and I have no idea how to do that.
Ex: I need to read column D with header Email. The sheet varies in length everyday and I just need that one column and no other rows or columns, but they have data in them.

Thank you!

[devneetmohanty07 · Accepted solution]

Hi Sean,

You can use the 'MS Excel - Extended' VBO business object which is also available in the following DX Exchange URL: Function for MS Excel VBO - Extended - 2.3.3

Once, you've imported this business object, you can create a sample workflow as shown below:



So let me quickly explain the workflow for you.

Step I: I created an excel instance using the 'Create Instance' action from 'MS Excel - Extended' business object which will return a number type data item called as 'handle'

Step II: I used a multi-calculation stage to set the data items for file path and sheet name where I want to perform this operation.

Step III: I used the 'Show' action from 'MS Excel - Extended' business object where I passed the 'handle' data item in order to make the excel application visible on my machine. This is an optional step and you can skip it if you want the operation to be carried out in background mode.

Step IV: I opened up my excel file using the 'Open Workbook' action from 'MS Excel - Extended' business object by passing the 'handle' and 'file name' data item returned from step I and step II which will return a text type data item called as 'workbook name' that indicates the name of the opened excel workbook file.

Step V: I activated the current worksheet where my data is located using the 'Activate Worksheet' action from 'MS Excel - Extended' business object by passing the 'handle' and 'workbook name' data item returned from step I and step IV.

Step VI: I fetched the existing number of records from my current worksheet by using the 'Get Number Of Rows' action from 'MS Excel - Extended' business object by passing the 'handle', 'workbook name' and 'sheet name' data item returned from step I and step II which will return a number type data item called as 'Number of rows' that indicates the total number of rows occupied by the existing dataset in that worksheet.

Step VII: I retrieved the total range of data from my current worksheet by using the 'Get Worksheet Range As Collection' action from 'MS Excel - Extended' business object by passing the 'handle', 'workbook name', 'sheet name' data item returned from step I and step II along with the expression for range of cells parameter as: "C1:C & " [Number of rows]   where the 'Number of rows' data item was retrieved from step VI. This action returns me all the values present within the 'C' column in my dataset in form of an output collection called as 'Data'.

NOTE: At this step you can provide any other column name instead of 'C' in the expression to retrieve values for that specific column.




Step VIII: I closed the excel instance using the 'Close Instance' action from 'MS Excel - Extended' business object.


------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

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