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.Selenium
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:

Excel file

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.Selenium
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 >> Run all 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:

Excel data in the robot log

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.Selenium
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:

Robocorp Lab Log with Error

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 String keyword is part of the BuiltIn library, 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.Selenium
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:

Robot filling the forms with Excel Data

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

What we learned

You can use the RPA.Excel.Files library 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 Workbook and Close Workbook keywords.
There are different types of data. You can convert from an integer to a string using the Convert To String keyword.
You can loop over a variable with the FOR ... IN syntax.

Learn more about the libraries mentioned on this page:

RPA.Excel.Files