Intelligent RPA 2.0 – Step-by-step instruction – Search value in Excel

[kar_ljo]

Introduction:

Hello everybody,

in this guide I will show you how to search for information within an Excel document using SAP Intelligent Robotic Process Automation. The content of the shown use case is useful for various scenarios. For example, if product information is stored in different Excel files and has to be determined by an employee or if data needs to be migrated, the use of RPA can save time and effort. Just use a bot to gather the required information!

All that needs to be known to build the bot:

• Search Value (e.g. “ProductID”)


• The column of the search value (for each Excel file)


• Excel workbook path


• Target Value (e.g. “PRICE”, “Color”)


• The column of the target value (for each Excel file)

Here is an exemplary and simplified structure of two Excel files from which the bot could be supposed to extract information:

 

By reading this guide you will learn how to deal with the following activities and controls (as you can see in the picture bellow):

• Open Input Dialog


• Open Excel Instance


• Open Workbook


• Forever (loop)


• Condition


• Index


• Get Value (from Excel)


• Open Message Dialog


• Close Excel


• Release Excel Instance

Every single step to automate the process is described in vivid detail and explained by a graphical documentation. Thus, the automation can easily be executed by everyone using the 12 months Free Trial of SAP Intelligent RPA.

If any questions arise, feel free to contact me or share your opinion about the process automation using SAP Intelligent RPA 2.0 in the comment section.

Video of the showcase:

Step-by-step instruction:

1 - Open Input Dialog (Search Value)

First of all, the search value must be provided to the bot. This can be done with the help of the "Open Input Dialog" activity. If the automation is integrated into a more complex process, the transfer of the parameter can, of course, also be managed in a different way. The ProductID manually entered by a human is stored as "inputValue".

2 - Open Excel Workbook

The next activity you have to add is „Open Excel Instance“. Afterwards use the „Open Workbook“ activity. Enter the specific workbook path (here it is: U:\Product_Information_2021.xlsx).

3 – Add a „Forever“ Loop

The Forever Loop is used to let the bot execute a process until a specific conclusion is fulfilled. In this example we have two conditions the bot has to check before it loops back to the start.

3a – Add the first condition

In the first condition we make sure that the default value “0” of the Step4.index is set to “1”. The bot checks if the index of the forever loop is not „0“. (Step4.index != 0).

Only if the index is not „0“ the bot will follow the right path, which will be the case in all rounds except of the first one. To change the index value the activity „set index“ is used. Without this condition, the index would be „0“ in the first run, which would lead to subsequent errors. Therefore, the activity is used to set the index to „1“.

3b – Search in Cells from column B

In the next step the bot will read a single cell from column (B). Therefore the “Get Value” activity (irpa_excel SDK) is used. The range definition is set in dependence of the index.

For this reason, it is important that the index is never “0”. In this case, an error message would appear because cell B0 does not exist. The value of the cell that is read is passed to the output parameter "CurrentProductID".

In the next step, the bot should check whether the value picked ("CurrentProductID") is equal to our original input parameter (“inputValue” from Step1). The second condition is used for this verification.

3c – Add the second condition

For each loop iteration, the bot checks within the condition whether the value of the current cell is equal to the ProductID we are looking for or not.

The following condition takes account for the verification:

Step7.CurrenProductID == Step1.inputValue

If the condition is not met, the bot runs through the Forever loop again and hopes to find the search value in the next row of the given column.

4 - End loop

If the condition is fulfilled, the associated price is taken for the ProductID searched for. A simple “Get Value” activity can be used for this:

In the last step before the loop ends, the “Open Message Dialog” activity is placed to display the determined price.

The dialogue parameters can be set as required. I choose the following text for this automation:

'The Price for the searched Product ID ' + Step1.inputValue + ' is ' + Step9.Price + '€.'

5 - Close and release Excel

In the end, only the Excel file must be closed and released.

Conclusion

This automation can be useful depending on the scenario that needs to be implemented. Of course, it should be mentioned that performance can suffer if an Excel file has a lot of rows because the bot checks them one by one for the search criterion.

Let me know if you enjoyed the blog post and feel free to share your Cloud Studio development experiences!

Best Regards,

Karl