Drive data with Excel

Watch this video to learn everything about the Read Excel building block.

What you learn in this lesson

  • How to use Excel as a data source for your cases.
  • How to wire up the individual columns in an Excel sheet to other building blocks.
  • Use a single row of data, a random row or iterate all data rows in an Excel sheet.

Lesson Overview

Watch this video to learn everything about the Read Excel building block.

What you learn in this lesson

  • How to use Excel as a data source for your cases.
  • How to wire up the individual columns in an Excel sheet to other building blocks.
  • Use a single row of data, a random row or iterate all data rows in an Excel sheet.

In this video I will talk about the Read Excel building block.

When you do automation in general, it is very important that you can control the input to you processes and cases. One very common tool for storing, maintaining and distributing data is Microsoft Excel, and we have built a building block in Leaptest that is specialized for Excel.

To demonstrate the Excel integration I’m gonna add a Read Excel building block.

  • Add Read Excel.

When the building block is added you can click on the browse button to select a file.

  • Select a Excel file.

I’ll select a customer file, I have prepared in advance.

When the file is selected, you can see the file name in the “Excel file” field.

It’s important to understand that the excel file is now copied to the controller and is not pointing to the local file. This means changes to the local file are not synchronized with the building block. In order to get the changes into the case you have to select the local file once again.

After selecting the file I can press “define”. This will open up a view into the excel file, allowing me to specify the rows and columns I want to use as data input. I’m selecting all 4 columns and 9 data rows. I have added column headers in my excel sheet, so I check the “Use first row as header” and click save. The columns in the excel file are now added as properties to the building block, allowing us to use them in the workflow.

To illustrate the usage of the data, I’ll add a “Log message” building block that will write lines of text to the activity log, when we run the case.

  • Add Log Message

I’m gonna add the properties from the excel file as fields on the Log Message block. I can do this by simply dragging the blue wire from the Read Excel block to the “Add field” button.

When all properties are connected I can use them in the Message field, by right-clicking in the field and selecting “Insert token”. Let me put in some text here.

  • Add text using the fields.

The Method property on the excel block is set to “First Row” which means it will only read the first data row in the excel file.

Let’s run the case.

  • Run case.

As you can see the First Row of data was printed to the log.

If the Method property is set to “Row index”, it allows us to specify exactly which row of data we would like to use. We simply input a number. This also gives us the opportunity to select a random row. For this purpose we can use a Generate Number building block that will generate a random number.

I’ll just add the block.

  • Add Generate Number

I’ll specify 1 as the minimum and 9 as the maximum because we have 9 rows of data in the excel file.

I’ll drag a blue connector from the “Result Value” to the “Row index” property.

Running the case a couple of times we should see different data rows being written to the log.

  • Run case
  • Run case
  • Run case

This was an example of using the row index to generate some sort of random data.

The last setting in the Method field is “Iterate”. This will run through all the data rows in the excel file, and for each data row it will trigger the green output connector at the top. In this case it means the log message is called once for each data row, but you can easily build much more complex workflows for each data row.

Once all data rows have been iterated, the building block will trigger the “Completed” output connector. The workflow can then move on, for instance by adding a Pass building block.

  • Remove Generate Number
  • Add Pass

Let’s run the case.

  • Run case

As we can see all data rows are printed to the activity log and the case is set to Pass.

This almost concludes this talk about the integration with Excel. If you need to read data dynamically from an excel file, you can do this by using the Database building block, and define an ODBC connection to an excel file on a shared location. This will require a little more setup and insight into ODBC but it can be done.

With this the video has reached the end.