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 saw so far, but as always, we will get over it step by step, so don't worry!
Let's recap where we are now first. 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 can't take it, trying to think happy thoughts... 💩
Our robot will not do any copy-pasting; it does not even need Excel at all. We can teach it to open and read the Excel file directly. How? By adding a new library, of course!
Let's add the RPA.Excel.Files library to the *** Settings *** section:
*** Settings ***
Documentation Robot to enter weekly sales data into the RobotSpareBin Industries Intranet.
Library RPA.Browser
Library RPA.HTTP
Library RPA.Excel.Files
Now let's change our Fill And Submit The Form step in the *** Tasks *** cell to Fill The Form Using The Data From The Excel File to better reflect what the robot will do:
*** Tasks ***
Insert the sales data for the week and export it as a PDF
Open The Intranet Website
Log In
Download The Excel File
Fill The Form Using The Data From The Excel File
Then, let's add that as a new keyword cell (as always, it needs to go before the tasks cell):
*** Keywords ***
Fill The Form Using The Data From The Excel File
Now that we have the RPA.Excel.Files library, our robot can open the Excel file, using the Open Workbook keyword. We just need to pass it the file name:
*** Keywords ***
Fill The Form Using The Data From The Excel File
Open Workbook SalesData.xlsx
Let's have a look at the Excel file again. Here's how it looks like:
We have the first row with the headers for the table (First Name, Last Name, Sales and Sales Target), and then the data rows for each of the sales representative. Our robot needs to read this data in a format that it understands. We can do it with the Read Worksheet As Table keyword. 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, so we can use the Close Workbook keyword to close it.
Friendly reminder: you can see the full list of keywords that the library provides on its documentation page.
At this point, our keyword looks like this:
*** Keywords ***
Fill The Form Using The Data From The Excel File
Open Workbook SalesData.xlsx
${sales_reps}= Read Worksheet As Table header=True
Close Workbook
Are you still with us? Great! As you can see from the keyword implementation, the robot only reads the Excel data. It does not do anything with the data yet. We are taking small steps!
Here is how our robot looks like at the moment:
*** Settings ***
Documentation Robot to enter weekly sales data into the RobotSpareBin Industries Intranet.
Library RPA.Browser
Library RPA.HTTP
Library RPA.Excel.Files
*** Keywords ***
Open The Intranet Website
Open Available Browser https://robotsparebinindustries.com/
*** Keywords ***
Log In
Input Text id:username maria
Input Password password thoushallnotpass
Submit Form
Wait Until Page Contains Element id:sales-form
*** Keywords ***
Fill And Submit The Form
Input Text firstname John
Input Text lastname Smith
Input Text salesresult 123
Select From List By Value salestarget 10000
Click Button Submit
*** Keywords ***
Download The Excel file
Download https://robotsparebinindustries.com/SalesData.xlsx overwrite=True
*** Keywords ***
Fill The Form Using The Data From The Excel File
Open Workbook SalesData.xlsx
${sales_reps}= Read Worksheet As Table header=True
Close Workbook
*** Tasks ***
Insert the sales data for the week and export it as a PDF
Open The Intranet Website
Log In
Download The Excel File
Fill The Form Using The Data From The Excel File
We have not run our robot in a while, time to give it a go! Run the robot by clicking on the >> button.
No visible difference from before, but if we look in the log, we can see 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, we need to set up things so that the robot will fill the form once for each of the rows 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:
*** Keywords ***
Fill And Submit The Form
Input Text firstname John
Input Text lastname Smith
Input Text salesresult 123
Select From List By Value salestarget 10000
Click Button Submit
Instead, we now want to be able to pass to it the data we want. So let's change our keyword so that it accepts an argument, and also let's rename it to better reflect what it does: Fill And Submit The Form For One Person:
*** Keywords ***
Fill And Submit The Form For One Person
[Arguments] ${sales_rep}
Input Text firstname John
Input Text lastname Smith
Input Text salesresult 123
Select From List By Value salestarget 10000
Click Button Submit
We expect the ${sales_rep} argument to be a list of named items. This means that if, for example, we want to access the First Name property of our variable, we would write: ${sales_rep}[First Name].
So we can say farewell to our friend John Smith, and replace our hardcoded values like this:
*** Keywords ***
Fill And Submit The Form For One Person
[Arguments] ${sales_rep}
Input Text firstname ${sales_rep}[First Name]
Input Text lastname ${sales_rep}[Last Name]
Input Text salesresult ${sales_rep}[Sales]
Select From List By Value salestarget ${sales_rep}[Sales Target]
Click Button Submit
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:
*** Keywords ***
Fill The Form Using The Data From The Excel File
Open Workbook SalesData.xlsx
${sales_reps}= Read Worksheet As Table header=True
Close Workbook
Now that 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:
*** Keywords ***
Fill The Form Using The Data From The Excel File
Open Workbook SalesData.xlsx
${sales_reps}= Read Worksheet As Table header=True
Close Workbook
FOR ${sales_rep} IN @{sales_reps}
Fill And Submit The Form For One Person ${sales_rep}
END
Great! Our robot now looks like this:
*** Settings ***
Documentation Robot to enter weekly sales data into the RobotSpareBin Industries Intranet.
Library RPA.Browser
Library RPA.HTTP
Library RPA.Excel.Files
*** Keywords ***
Open The Intranet Website
Open Available Browser https://robotsparebinindustries.com/
*** Keywords ***
Log In
Input Text id:username maria
Input Password password thoushallnotpass
Submit Form
Wait Until Page Contains Element id:sales-form
*** Keywords ***
Fill And Submit The Form For One Person
[Arguments] ${sales_rep}
Input Text firstname ${sales_rep}[First Name]
Input Text lastname ${sales_rep}[Last Name]
Input Text salesresult ${sales_rep}[Sales]
Select From List By Value salestarget ${sales_rep}[Sales Target]
Click Button Submit
*** Keywords ***
Download The Excel file
Download https://robotsparebinindustries.com/SalesData.xlsx overwrite=True
*** Keywords ***
Fill The Form Using The Data From The Excel File
Open Workbook SalesData.xlsx
${sales_reps}= Read Worksheet As Table header=True
Close Workbook
FOR ${sales_rep} IN @{sales_reps}
Fill And Submit The Form For One Person ${sales_rep}
END
*** Tasks ***
Insert the sales data for the week and export it as a PDF
Open The Intranet Website
Log In
Download The Excel File
Fill The Form Using The Data From The Excel File
We run the robot again and...
Oh no! The robot gets stuck while filling the first form! What's going on?
Hitting a roadblock 😬
No panic! Let's look at the log:
The log is telling us that the Select From List By Value keyword in the Fill And Submit The Form For One Person keyword was expecting a string (str, basically a sequence of characters, such as "dinosaur") value, and instead, it got a number (int, for "integer"). How can we fix it?
We need to convert the value into a string before we use it, and there is a keyword for that!
Let's change the Fill And Submit The Form For One Person keyword so it reads like this:
*** Keywords ***
Fill And Submit The Form For One Person
[Arguments] ${sales_rep}
Input Text firstname ${sales_rep}[First Name]
Input Text lastname ${sales_rep}[Last Name]
Input Text salesresult ${sales_rep}[Sales]
${target_as_string}= Convert To String ${sales_rep}[Sales Target]
Select From List By Value salestarget ${target_as_string}
Click Button Submit
Now we are creating a variable (remember, a container for stuff) ${target_as_string}, and we are assigning to it the Sales Target converted to a string. Then we are using this new variable to choose the correct value from our HTML select element.
The
Convert To Stringkeyword is part of theBuiltInlibrary, which is included by default.
Admire the robot doing the heavy lifting!
Whew! That was a lot! But now our robot should be all ready to go:
*** Settings ***
Documentation Robot to enter weekly sales data into the RobotSpareBin Industries Intranet.
Library RPA.Browser
Library RPA.HTTP
Library RPA.Excel.Files
*** Keywords ***
Open The Intranet Website
Open Available Browser https://robotsparebinindustries.com/
*** Keywords ***
Log In
Input Text id:username maria
Input Password password thoushallnotpass
Submit Form
Wait Until Page Contains Element id:sales-form
*** Keywords ***
Fill And Submit The Form For One Person
[Arguments] ${sales_rep}
Input Text firstname ${sales_rep}[First Name]
Input Text lastname ${sales_rep}[Last Name]
Input Text salesresult ${sales_rep}[Sales]
${target_as_string}= Convert To String ${sales_rep}[Sales Target]
Select From List By Value salestarget ${target_as_string}
Click Button Submit
*** Keywords ***
Download The Excel file
Download https://robotsparebinindustries.com/SalesData.xlsx overwrite=True
*** Keywords ***
Fill The Form Using The Data From The Excel File
Open Workbook SalesData.xlsx
${sales_reps}= Read Worksheet As Table header=True
Close Workbook
FOR ${sales_rep} IN @{sales_reps}
Fill And Submit The Form For One Person ${sales_rep}
END
*** Tasks ***
Insert the sales data for the week and export it as a PDF
Open The Intranet Website
Log In
Download The Excel File
Fill The Form Using The Data From The Excel File
Our robot now knows how to open the 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
RPA.Excel.Fileslibrary to work with Excel files, no Excel required! - You can enable your keywords to accept arguments.
- You can open and close Excel files with the
Open WorkbookandClose Workbookkeywords. - There are different types of data. You can convert from an integer to a string using the
Convert To Stringkeyword. - You can loop over a variable with the
FOR ... INsyntax.