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. How? By adding a new library, of course!

Let's add the RPA.Excel.Files library to the *** Settings *** section:

*** Settings ***
Documentation     Insert the sales data for the week and export it as a PDF.
Library           RPA.Browser.Selenium    auto_close=${FALSE}
Library           RPA.HTTP
Library           RPA.Excel.Files

Let's change our Fill and submit the form step in the *** Tasks *** section to Fill the form using the data from the Excel file to describe better 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 our new keyword, starting with its name:

*** 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:

Excel file

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

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     Insert the sales data for the week and export it as a PDF.
Library           RPA.Browser.Selenium    auto_close=${FALSE}
Library           RPA.HTTP
Library           RPA.Excel.Files

*** 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

*** Keywords ***
Open the intranet website
    Open Available Browser    https://robotsparebinindustries.com/

Log in
    Input Text    username    maria
    Input Password    password    thoushallnotpass
    Submit Form
    Wait Until Page Contains Element    id:sales-form

Download the Excel file
    Download    https://robotsparebinindustries.com/SalesData.xlsx    overwrite=True

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

Fill the form using the data from the Excel file
    Open Workbook    SalesData.xlsx
    ${sales_reps}=    Read Worksheet As Table    header=True
    Close Workbook

Run the robot from the Command Palette.

No visible difference from before, but the log shows 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, 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:

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

*** 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. If, for example, we want to access the First Name property of our variable, we would write: ${sales_rep}[First Name].

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

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

The auto-completion can help you with writing the for loop syntax. Type for and select FOR IN from the suggestion list. This will create a placeholder for loop syntax for you. Then you can go and replace the values as shown in the snippet. No need to spend more time here, but check out the for loop article to learn more about looping when you feel like it.

Great! Our robot looks like this:

*** Settings ***
Documentation     Insert the sales data for the week and export it as a PDF.
Library           RPA.Browser.Selenium    auto_close=${FALSE}
Library           RPA.HTTP
Library           RPA.Excel.Files

*** 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

*** Keywords ***
Open the intranet website
    Open Available Browser    https://robotsparebinindustries.com/

Log in
    Input Text    username    maria
    Input Password    password    thoushallnotpass
    Submit Form
    Wait Until Page Contains Element    id:sales-form

Download the Excel file
    Download    https://robotsparebinindustries.com/SalesData.xlsx    overwrite=True

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

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

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 RPA.Excel.Files 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 ... IN syntax.
There are many useful automation libraries available!