Today we will be demonstrating RPA use case; as to how to search for data or information within an Excel file by making use of SAP Intelligent Robotic Process Automation. The demonstrated use case is beneficial in various scenarios, like any product data located in various Excel files and need to be retrieved or searched for, by the user, or for example if information is required to be migrated from excel file to another, then this simple use of RPA bot will come in handy thereby saving time and user efforts.
- Search Value like “Prod_ID”)
- The column of the search value – Column ID (for each Excel file, “B” ,“D” and “E”)
- Excel workbook path – the link of excel file from which the data is to be retrieved or searched
- Target Value - field in this this data to be stored (e.g. “PRICE”, “Color”)
- The column of the target value Column ID (for each Excel file)
Example of the simplified structure of two Excel files for the extraction of information by the usage of Bots:
Process flow of the BOT creation - activities and controls (please refer to the image below):
- Index
- Get Value (from Excel)
- Open Message Dialog
- Close Excel
- Release Excel Instance
- Open Input Dialog
- Open Excel Instance
- Open Workbook
- Forever (loop)
- Condition
Steps to automate the process is explained in vivid detail with a graphical documentation. Usage of the 12 months Free Trial of SAP Intelligent RPA will help to execute the RPA.
The search value must be provided as an input to the bot, use the “Open Input Dialog” activity. To handle more complex integrated process in automating, then the parameter transfer need to be managed in a different way.
The Prod_ID manually entered by a human is stored as “inputValue”.
The second task is to add – “Open Excel Instance“. Next step is to use the “Open Workbook“ task. Enter the specific workbook path (here it is: U:\Product_Information_2021.xlsx).
To execute the process by the Bot till it retrieves the desired information or the desired assumption (conditions) is fulfilled, the “Forever Loop” is created. In this example there are two conditions the bot has to certify or confirm before it loops back to the start of the program.
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, this will be the case in all rounds except of the first one. Use the “set index“ functionality to change the index value. Mandatory condition - without this condition, the index would be „0“ in the first run, which would lead to subsequent errors, therefore please do note, that the task is used to set the index to “1“.
Search in Cells from column (B)
Next task - as the bot will be reading a single cell from column (B), therefore the “Get Value” functionality (irpa_excel SDK) is used. The range definition is set in dependence of the index.
Note: 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”.
Next stage, the bot should verify and confirm whether the value picked (“CurrentProductID”) is equal to our original input parameter (“inputValue” from Step1). Use the second condition for this verification.
Add the second condition (C)
The bot checks within the condition for each loop iteration, whether the current cell value matches or is equal to the Prod_ID which we are searching for in the excel.
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.
End loop
If the condition is fulfilled, the associated price is taken for the Prod_ID 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.
In the last step before the loop ends, the “Open Message Dialog” activity is placed to display the determined price.
‘The Price for the searched Product ID ‘ + Step1.inputValue + ‘ is ‘ + Step9.Price + ‘€.’
In the end, only the Excel file must be closed and released.
Depending on the scenario to be implemented, this RPA bot is very useful. Please do note that the performance can suffer if an Excel file has a lot of rows as the bot checks them one by one for the search criterion.
We have seen here basic interaction between an excel cell and RPA, in next post we will take it to the next level to bring the concept of context and reading and writing multiple rows of data. Feel free to provide your feedback!