How to gather/read data from a random row within an Excel sheet using Leapwork?
Overview:
While performing automation, we might come across a scenario where we need to fetch/read any random value from an Excel sheet and provide the same to an application. These values can come from within the defined range of the Read Excel building block. With the help of this block, we can read the value for any random row from the available rows of an Excel sheet.
In this scenario, we will have two Excel files containing some random names in a column. We will now try to compare the two names from both the sheets.
How to gather/read data from a random row within an Excel sheet using Leapwork?
A sample test case for reading the value from a random row of an Excel sheet is shown below by creating a custom building block (also called sub-flows in Leapwork) so that it can be reused later on. We will start building our flow with a start block. We would then click to add a Read Excel Block inside the sub-flow.
Typical view of a "Randomization Concept" Flow within Leapwork:
Expanded view of a Randomization Concept flow as shown below:
In the above image, we are reading the FirstName value for a random row from the available rows of an Excel sheet.
Under the hood:
- Read Excel 1 block has a method set as Iterate so that it will execute for each row of the sheet.
- Set Variable block is used to store the count for available rows as a variable ( Row Count) in the block.
- Get Variable block is used to fetch the maximum count for available rows stored as a variable ( Row Count) in the Set Variable block.
- Generate Number block is used to generate a random value from the available rows.
- Read Excel 2 block has method as Row index so that it will execute for a particular row of the sheet that is given as input from Result value from the Generate Number block.
- Log message block will show the FirstName value written in that random row of the Excel sheet.
Example of a successfully run flow showing the data of a random row of the excel sheet with the respective Studio Logs:
The highlighted area in the above image shows the comparison of the value from the same rows of the first and second Excel files.
Note:- We can always use a .csv file to perform the same task as above.
Quick Start:
1. Download the attached Randomization Concept - Reading any row data randomly.zip file.
2. Import the attached use case, Randomization Concept - Reading any row data randomly.zip , in the Leapwork Studio.
3. Open the use case with the title, "Randomization Concept - Reading any row data randomly"
4. We can always wrap the building blocks to create a sub-flow as shown above in the typical view screenshot, but here we have used individual blocks instead.
Configuration and Use:
After the flow has been imported, you can move the flow to any specific folder and then execute the same to see the behavior.
Support:
For any clarification, please contact our Priority Support.