Using Excel data to fill in the form

Our robot will learn some serious skills in this step. This chapter might be a bit more complicated than what we have seen so far. As always, we will get over it step by step, so don't worry!

Let's recap where we are. So far, our robot knows how to:

• log into the intranet
• fill the form once using hardcoded data
• download the remote Excel file locally

Now, we need to read the sales data from the Excel file and then fill the form for each line in it.

Let's start!

Reading the Excel file

At this point in her weekly painful task, Maria would fire up Microsoft Excel on her computer and start copy-pasting from the application to the form until her eyes couldn't take it, trying to think happy thoughts... 💩

Our robot will not do any copy-pasting; it does not even need the Excel application at all. We can teach it to open and read the Excel file directly.

Now, create a new user keyword called Fill the form using the data from the Excel file, which will do more than the old Fill And Submit The Form did.

Then, replace the old user keyword with the new one in the task Insert The Sales Data For The Week And Export It As A Pdf. You can also do this change in the Code view, if you prefer.

Next, let's read data from the Excel file in the Fill the form using... keyword. First open the Excel file, using the Open Workbook keyword and from the sidebar, click on the folder icon next to the Path field to select the downloaded Excel file. This should set the value of the Path field to %{ROBOT_ROOT}${/}SalesData.xlsx — the same as the path we definied in the download step. Great! 😅

Let's have a look at the Excel file again:

The first row contains the headers for the table (First Name, Last Name, Sales and Sales Target). Then we have rows for each of the sales representatives. Our robot needs to read this data in a format that it understands. We can do it with the Read Worksheet As Table keyword.

This might sound hard, but you can do it! Let's do it step-by-step.

We will put the data into a variable that we will call sales_reps. Think of a variable as a container for stuff. Such as sales representatives! Also, because our Excel contains headers on the first row, we will pass the header parameter as True.

After this, we don't need to keep the Excel file open anymore. We can use the Close Workbook keyword to close it.

You can see the complete list of keywords that the library provides on its documentation page.

Add Close Workbook to the Fill the form using... keyword:

Variables allow you to communicate values from one keyword to another. They can also make your code cleaner and simpler. Variables are color-coded in Automation Studio with a blue bar next to them. To learn more about variables in Robot Framework, check out Robot Framework: Variables.

Are you still with us? Great! The robot only reads the Excel data. It does not do anything with the data yet. We are taking small steps!

Run the robot.

No visible difference from before, but the clicking the Read Worksheet As Table keyword in the run view shows that the robot now has the data that was in the Excel file:

Excellent! Let's move on!

Filling the form for each of the rows in the Excel file

Now that we have loaded the Excel data, the robot needs to fill the form once for each row in the table. Remember our Fill and submit the form keyword? We set it a couple of chapters ago to fill the form with hardcoded data.

Instead, we now want to be able to pass to it the data we want. Let's change our keyword so that it accepts an argument. Also, let's rename it to reflect better what it does: Fill and submit the form for one person.

To rename the keyword, you can use the menu next the keyword name in the left sidebar and select Rename. Alternatively, you can use the code view to change the name of the keyword.

Open up the renamed keyword and click "Add input argument" to add an input argument for the keyword. This argument gets passed to the keyword when you call it and provides in this case data for one person that will be filled in the form.

Set the name of the input argument to be "sales_rep" and leave the default value empty.

Next, click on the input parameter for the first name with the value "John" and select sales_rep from the list.

To add variables to fields, you can either click on a field and select the variable from the drop-down, or drag the variable from where it has been defined by grabbing the blue bar next to it, and dropping it into the field where you wish to use it . If you are familiar with Robot Framework, then you can also use variables by typing in the variable name, such as ${address} in a text field.

We expect the sales_rep argument to be a list of named items. If, for example, we want to access the First Name property of our variable, we would write in code: ${sales_rep}[First Name]. Luckily Automation Studio handles some of this syntax for us, let's see below how to access the named list items in the Automation Studio editor!

Let's adjust the text to enter into the Firstname field. First hover on the variable name to show the arrow next to the sales_rep value:

And then in the empty field type in the name of the field to read, in this case First Name:

Do the same for the other fields so that the keyword looks like the one below. Note that for the Sales Target, you need to click on it and select the value from the sidebar on the right:

Filling the form for each row

Now that our Fill and submit the form for one person is ready, we can go back to our Fill the form using the data from the Excel file keyword.

The Fill and submit the form for one person keyword is ready to accept arguments. We can loop over the rows of the table (the sales_reps variable), and call it each time passing the individual row to it.

First, search and drag For loop from the keyword palette on the left and select sales_reps from the drop-down for the second field in the for loop (see image below). You can also drag the sales_reps from the previous row on to the field.

Next, give a name for the variable we use to refer to the current item while looping. Let's call it sales_rep:

Then, drag the user keyword Fill and submit the form for one person on top of the For loop.

And finally, pass the current person information, that is sales_rep, to the keyword. Again, you can select it from the list or drag it from the for-loop.

Great! Now the keyword looks like this:

Admire the robot doing the heavy lifting!

Whew! That was a lot! But now our robot should be all ready to go:

Our robot now knows how to open the Excel file, get the data out, loop over it, and fill the form for us! Let's run it and see it do its magic.

🕺 Maria is free from copy-paste hell! She will be so happy about this! 👍🏻

What we learned

• You can use the Excel library to work with Excel files. No Excel required!
• You can provide arguments to your keywords.
• You can open and close Excel files with the Open Workbook and Close Workbook keywords.
• You can loop over a variable with the For loop.
• There are many useful automation keywords available!