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:

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:

Then, let's add our new keyword, starting with its name:

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:

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.

Our keyword looks like this:

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:

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!

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:

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:

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:

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:

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!