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!
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 module, of course!
Let's add the RPA.Excel.Files library to the imports section:
from robocorp.tasks import task
from robocorp import browser
from RPA.HTTP import HTTP
from RPA.Excel.Files import Files
Let's change our call fill_and_submit_form() in the task section to fill_form_with_excel_data() to describe better what the robot will do:
@task
def robot_spare_bin_python():
"""Insert the sales data for the week and export it as a PDF"""
browser.configure(
slowmo=100,
)
open_the_intranet_website()
log_in()
download_excel_file()
fill_form_with_excel_data()
Then, let's add our new function, starting with its name:
def fill_form_with_excel_data():
"""Read data from excel and fill in the sales form"""
Now that we have the RPA.Excel.Files library, our robot can open the Excel file, using the open_workbook() function. We just need to pass it the file name:
def fill_form_with_excel_data():
"""Read data from excel and fill in the sales form"""
excel = Files()
excel.open_workbook("SalesData.xlsx")
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.
This might sound hard, but you can do it! Let's do it step-by-step.
First, we need to get the data from the worksheet.
We can do it with the read_worksheet_as_table() function.
We will put the data into a variable that we will call worksheet. 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() function to close it.
You can see the complete list of keywords that the library provides on its documentation page.
def fill_form_with_excel_data():
"""Read data from excel and fill in the sales form"""
excel = Files()
excel.open_workbook("SalesData.xlsx")
worksheet = excel.read_worksheet_as_table("data", header=True)
excel.close_workbook()
Now we have all the data from our datasheet in our excel file saved in the worksheet variable.
Are you still with us? Great! As you can see from the function 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:
from robocorp.tasks import task
from robocorp import browser
from RPA.HTTP import HTTP
from RPA.Excel.Files import Files
@task
def robot_spare_bin_python():
"""Insert the sales data for the week and export it as a PDF"""
browser.configure(
slowmo=100,
)
open_the_intranet_website()
log_in()
download_excel_file()
fill_form_with_excel_data()
def open_the_intranet_website():
"""Navigates to the given URL"""
browser.goto("https://robotsparebinindustries.com/")
def log_in():
"""Fills in the login form and clicks the 'Log in' button"""
page = browser.page()
page.fill("#username", "maria")
page.fill("#password", "thoushallnotpass")
page.click("button:text('Log in')")
def fill_and_submit_sales_form():
"""Fills in the sales data and click the 'Submit' button"""
page = browser.page()
page.fill("#firstname", "John")
page.fill("#lastname", "Smith")
page.fill("#salesresult", "123")
page.select_option("#salestarget", "10000")
page.click("text=Submit")
def download_excel_file():
"""Downloads excel file from the given URL"""
http = HTTP()
http.download(url="https://robotsparebinindustries.com/SalesData.xlsx", overwrite=True)
def fill_form_with_excel_data():
"""Read data from excel and fill in the sales form"""
excel = Files()
excel.open_workbook("SalesData.xlsx")
worksheet = excel.read_worksheet_as_table("data", header=True)
excel.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:
Excellent! Let's move on!
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_sales_form() function? We set it a couple of chapters ago to fill the form with hardcoded data:
def fill_and_submit_sales_form():
"""Fills in the sales data and click the 'Submit' button"""
page = browser.page()
page.fill("#firstname", "John")
page.fill("#lastname", "Smith")
page.fill("#salesresult", "123")
page.select_option("#salestarget", "10000")
page.click("text=Submit")
Instead, we now want to be able to pass to it the data we want.
Let's change our function so that it accepts an argument, sales_rep that will contain data from our excel file.
def fill_and_submit_form(sales_rep):
"""Fills in the sales data and click the 'Submit' button"""
page = browser.page()
page.fill("#firstname", "John")
page.fill("#lastname", "Smith")
page.fill("#salesresult", "123")
page.select_option("#salestarget", "10000")
page.click("text=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:
def fill_and_submit_sales_form(sales_rep):
"""Fills in the sales data and click the 'Submit' button"""
page = browser.page()
page.fill("#firstname", sales_rep["First Name"])
page.fill("#lastname", sales_rep["Last Name"])
page.select_option("#salestarget", str(sales_rep["Sales Target"]))
page.fill("#salesresult", str(sales_rep["Sales"]))
page.click("text=Submit")
Now that our fill_and_submit_sales_form() is ready, we can go back to our fill_form_with_excel_data() function:
def fill_form_with_excel_data():
"""Read data from excel and fill in the sales form"""
excel = Files()
excel.open_workbook("SalesData.xlsx")
worksheet = excel.read_worksheet_as_table("data", header=True)
excel.close_workbook()
The fill_and_submit_sales_form() function is ready to accept arguments.
We can loop over the rows of the table, and call it each time passing the individual row to it:
def fill_form_with_excel_data():
"""Read data from excel and fill in the sales form"""
excel = Files()
excel.open_workbook("SalesData.xlsx")
worksheet = excel.read_worksheet_as_table("data", header=True)
excel.close_workbook()
for row in worksheet:
fill_and_submit_sales_form(row)
Great! Our robot looks like this:
from robocorp.tasks import task
from robocorp import browser
from RPA.HTTP import HTTP
from RPA.Excel.Files import Files
@task
def robot_spare_bin_python():
"""Insert the sales data for the week and export it as a PDF"""
browser.configure(
slowmo=100,
)
open_the_intranet_website()
log_in()
download_excel_file()
fill_form_with_excel_data()
def open_the_intranet_website():
"""Navigates to the given URL"""
browser.goto("https://robotsparebinindustries.com/")
def log_in():
"""Fills in the login form and clicks the 'Log in' button"""
page = browser.page()
page.fill("#username", "maria")
page.fill("#password", "thoushallnotpass")
page.click("button:text('Log in')")
def fill_and_submit_sales_form(sales_rep):
"""Fills in the sales data and click the 'Submit' button"""
page = browser.page()
page.fill("#firstname", sales_rep["First Name"])
page.fill("#lastname", sales_rep["Last Name"])
page.select_option("#salestarget", str(sales_rep["Sales Target"]))
page.fill("#salesresult", str(sales_rep["Sales"]))
page.click("text=Submit")
def download_excel_file():
"""Downloads excel file from the given URL"""
http = HTTP()
http.download(url="https://robotsparebinindustries.com/SalesData.xlsx", overwrite=True)
def fill_form_with_excel_data():
"""Read data from excel and fill in the sales form"""
excel = Files()
excel.open_workbook("SalesData.xlsx")
worksheet = excel.read_worksheet_as_table("data", header=True)
excel.close_workbook()
for row in worksheet:
fill_and_submit_sales_form(row)
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! 👍🏻
- You can use the
RPA.Excel.Files library to work with Excel files. No Excel required!
- You can provide arguments to your functions.
- You can open Excel files with the
open_workbook() function.
- You can loop over a variable with the
for ... in syntax.
- There are many useful automation libraries available!