Filter Columns

[MarinaDutta]

Dear All,

I have an automation scenario below. Can anyone help me to find an solution

1) Filter column based on @ symbol.
2) Slect those rows corresponding to @ symbol and their corrsponding to column  
3) Copy and paste the data of those columns into another Excel Sheet
First Select column AC

​Then select columns H,I,J,U,X,Z,AA,AB and paste their data into another Excel sheet.

Thanks and Regards,
Marina​​

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

[devneetmohanty07]

Hi Marina,

There are many ways to achieve what you're looking for, however I will point two methods either of which you can use as you feel like.

First method is by using the 'Filter Collection' action from the 'Utility - Collection Manipulation' VBO. As per this method once you successfully read your table data into a collection use the mentioned action with the following filter query: "[INPUT] = '@'"

Here the word [INPUT] signifies the column name which you want to use your filter upon and we are equating it against the string '@'. Using this filter you can easily get the data in the output collection. Find the below screenshots for more details:

Inputs:



Action Parameters:





Outputs:



The second way I would prefer to use and it is overall a better solution. In this approach, try reading your excel file as a database using 'Utility - OLEDB' action which will read your excel file as a database using the ODBC drivers. In order to properly work with this VBO, you can refer to the following training LMS as well: Blue Prism Guide To OLEDB

NOTE: In case the Microsoft Runtime Engine is not working, the you can download the setup from the following drive location: Access Database Engine Installation

DISCLAIMER : Please download at your own risk as it is not being provided or promoted by me.

- For this VBO to work, you need to install the Microsoft Access Database Engine which you can get from Microsoft's website as well which is given under the training resources. Once you get the DLL installed. You can use the action "Set Connection" of the "Data - OLEDB" VBO to first establish a successful connection with your excel file by providing a valid connection string, for example, Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<FILE_PATH>;Extended Properties="Excel 12.0;HDR=YES";

You can try replacing the highlighted text with the actual file path consisting of the file name and extension along with the full file path. For example: "C:\Test\Test.xlsx"

NOTE: Many times the connection string may not work depending on the excel version and Extended Properties. In case it does not work you can refer to the following website where you can find all the alternatives to your connection string to try upon: Connection Strings

- After this action you can use the "Open" action of the "Data - OLEDB" VBO to open the connection to the excel file and then use "Get Collection" of the "Data - OLEDB" VBO action to execute the query which you want to send in order to fetch the data. The query format is more or less like your SQL queries which you can use like SELECT * FROM <Sheet_Name>$ WHERE [Column Name] = '@'

You need to replace the highlighted text with the sheet name where your data resides and the column name where you need to apply the filter in that excel file. This action will provide you the entire result set of this query into a collection.

NOTE: In case your table starts from somewhere middle in the sheet, for example let say from A5 cell till column E then the query will modify a bit like this: SELECT * FROM <Sheet_Name>$A5:E WHERE [Column Name] = '@'

- Post this you can now close the connection by using "Close" action of the "Data - OLEDB" VBO.

The benefit of using this action is that you don't need unnecessarily load your entire data into collection and then apply filters which can result in memory issues.

I have attached a sample workflow for your reference as well:




Once you have the data with you in the collection using either of the mentioned approach, you can use 'Split Collection' action from 'Utility - Collection Manipulation' VBO and get the desired columns from your extracted data into a separate collection which you can later on write to any given excel file using 'Write Collection' action from 'MS Excel' VBO.

NOTE: In order to know how to correctly use the action, 'Split Collection' a recent thread has been posted where @ewilson has demonstrated how you can use the action with help of screenshots that will help you. You can find the thread here

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

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

[MarinaDutta]

Dear Devneet,

Thank You so much for your help. After filtering with @ symbol I need to select columns H,I,J,U,X,Z,AA,AB and paste their data into another Excel sheet. Corresponding to @ symbol need to select columns H,I,J,U,X,Z,AA,AB  and paste the data of those columns into other Excel Sheet. How to proceed with the second part that is selecting columns H,I,J,U,X,Z,AA,AB  and paste data to other excel sheet.

Thanks once again for youe help.​​

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

[devneetmohanty07 · Accepted solution]

Hi Marina,

I also told you about the second part in my last paragraph in the above post. Let me try to elaborate that part here as well. For achieving this as well, you can go by two approaches. The first approach is basically what I have suggested as it is the in-house Blue Prism solution whereas the second approach is related to create your own custom business object using a code stage which will require some knowledge of VB .NET which might a bit difficult to understand if you are new to the tool but it is a can also be considered as a good solution. I will explain the first approach only here since it can work well in your case.

Coming to the first method, what you can do is let say I have read the entire data from excel in form of a collection and also have filtered the collection where the defined value had the string '@'. As per your use case, you only need selected columns and not the entire range if I understand correctly. In order to achieve it let us take an example and understand the same.

In my case I am taking a small collection named 'Input Collection' with 6 columns having some data as shown below. This you can consider to the filtered collection that you have got till what my post says so far:



Let us assume that I only need to fetch the columns: 'Field3', 'Field5' and 'Field6', leaving aside the rest of the columns which will be written in the excel file as well. This is similar to what you mean by "selecting the columns H,I,J,U,X,Z,AA,AB", you just need to know the header names in your collection that correspond to the column names in excel . In order to achieve this I would first need to create two templates which denote each half of my collection once I split it vertically. So technically I need to create two collections with a single blank row among which one will consist of the columns 'Field1', 'Field2' and 'Field4' and another one will have the columns 'Field3', 'Field5' and 'Field6' as we desire those columns as our target. In my case I name these collections as 'Collection Template I' and 'Collection Template II' as you can see below:



As a good practice I also check the highlighted button while preparing the template collections as they will automatically add a single row for me.

After this you would need to create two blank collections which will be used to store the output results once the split operation has been performed. You need not create any field definition for these you have done for the template collections as the field will added automatically with the data during runtime.

In my case I name these output collections as 'Output Collection I' and 'Output Collection II' which will constitute of their own individual halves of data as shown below:



Now you can use the 'Split Collection' action from the 'Utility - Collection Manipulation' VBO with the below shown parameters:


Upon running the action, you can see the results as follows:



Here you can see that I am able to fetch the result into 'Output Collection II' which I can use now to write into excel.

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

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