BACK

v11 Tutorial: Reading from CSV and Form Filling

Prerequisites

You’ll need to have Automation Anywhere Enterprise v11 workbench installed and licensed with a bot creator license to follow along with this lab.

Background

If you break down some of the most common bots that organizations across the world are creating – there is a common pattern seen over and over again: take data from one place (database, file, application, etc.) and move it into another place (database, file, application, etc.). It’s important to understand how to read data from some source, and deliver it to some target application. In this exercise – our sample company has vendors missing from its vendors list. Download the CSV from the page, read from the csv, and enter the details into the vendor registration form. Along the way, you’ll learn how to parse a CSV file, work with input fields, interact with drop downs, learn how to click radio buttons, and learn how to check checkboxes.

Exercise Page: https://www.automationanywhere.com/automationanywherelabs/intro-to-csv.html

Before we move forward, download the file from the exercise page. Open it up in Excel or your favorite text editor. Take note of the data that is included in the CSV, and think through how we might use some of that to fill out the Vendor Registration page.

Psuedocode

Let’s start with our psuedocode again. Remember, think of psuedocode as the human readable steps that our bot will need to accomplish. To get started thinking about the steps we’d need to take – look at the exercise page to understand the specifics of this exercise.

Step 1: Launch Website – The website has to be open before we can start downloading files or entering data.
Step 2: Download CSV From Webpage – We need to download the file locally so we can read from it. We also need to make sure we only ever have 1 copy of the file instead of downloading duplicates.
Step 3: Read CSV Values and Store in Variables – We need to grab all the data out of the CSV file we download and store it in a meaningful way so that we can use the data to fill our web form.
Step 4: Automate Filling the Web Form – We need to use the data from the CSV to fill the form – we’ll be working with input fields, drop downs, radio buttons, and checkboxes to correctly represent and enter the CSV data.
Step 5: Click the Submit Button – The last step for our bot is to press the Submit button for us so the site can validate if our form is filled correct.

Building the Bot

We have a good start on our requirements, and we understand what needs to happen for this exercise to be completed successfully. We’ll be building from scratch on this one, so we’ll need to create brand new bot.

  1. Launch and login to the Automation Anywhere Client (Either Enterprise or Community – either will work).
  2. In the upper left corner of the screen, click the “New” button.
  3. An “Automate” dialogue will appear asking how you want to get started. Press the Workbench button to continue.
  4. A blank Workbench session will open. Before we get started adding commands to our Actions List, we’ll need to create some variables to store some of our data for this exercise. Variables are used to label and store data that our bot will use for processing.
    1. In Automation Anywhere, the best practice is to prefix custom variables with a lower-case v then use camel case naming with no spaces for the rest of the variable name.
  5. Before we start creating our variables, its important to understand what variables we need to create, what to call them, and why we would need them in the first place. On the exercise page for this lab – click the Download CSV button to download the file MissingVendor.csv
    1. Open this file up so we can see what the bot will eventually need to read (you can open it in Excel or any text editor).
    2. Upon reviewing the file we can see the file has a header row, and also has 1 row of real data with 10 total columns.
    3. The CSV’s header indicates that the file contains the Vendor Name, Vendor ID, Primary Contact, Street Address, City, State, Zip, Email Address, an Offers Discount column, and a Non-Disclosure on File column.
    4. Its important for us to understand how our input data will look, so we have some idea as to how we might store and use this data for use in filling out the web form.
  6. To create your first variable, click on the Variable Manager tab on the far-right side of your Workbench window. Clicking this tab will expand the Variable Manager tab.
  7. We’ll be creating quite a few variables for this exercise. Instead of going through and creating them all together, we’ll document how to do one, then show a table for the rest.
  8. With the variable manager tab expanded, click the Add button to add a new Variable. An Add Variable dialogue will appear.
    1. In the Add Variable dialogue, enter vVendorName into the Name field leaving the Variable Type as Value and the Select drop-down as Value.
    2. We’re going to leave the variables we create with a blank value for now, because we’ll let the bot take care of assigning the variables a value.
    3. Press the Save button to save your newly created variable. A pop-up will appear indicating that we are assigning a NULL value to our variable. That just means we have left the variable unassigned – which is what we want, so press Yes to dismiss this dialogue.
  9. We need to repeat those steps again to create the rest of our variables. Follow the steps we just took, create the following variables:

    Variable Name

    Select Field:

    Initial Value

    vVendorName (we just did this one together) Value (blank)
    vVendorID Value (blank)
    vPrimaryContact Value (blank)
    vStreetAddress Value (blank)
    vCity Value (blank)
    vState Value (blank)
    vZip Value (blank)
    vEmail Value (blank)
    vActiveDiscount Value (blank)
    vNDA Value (blank)

     

  10. With the all of the required variables created, we can get to our first step in the bot building process – launching our browser and navigating to the exercise page we’ll be using for our automation. From the commands tab on the left side of the screen, click and drag the Open Program/File command over on to the Actions List. As soon as you drag this Open Program/File command over, notice that it pops-up with a context menu asking for a couple parameters.
    1. The Step 1 field is the Program/File Path. Press the browse button next to this field to open a windows browser window. Here, you’ll give the specific instruction to open a particular application. In the case of this tutorial, we want to select the Google Chrome executable file. For me, that’s located in C:\Program Files (x86)\Google\Chrome\Application. Your path is likely very similar if not the same. In that folder, select the chrome.exe executable and press the Open button to close the dialog.
    2. The Step 3 field is the other field we’re concerned with in this command. In step 3 we give a parameter to be used with the application selected in Step 1. For us, that parameter will be the URL of our tutorial page: https://www.automationanywhere.com/automationanywherelabs/intro-to-csv.html
    3. Press the Save button to close the Open Program File Command.
  11. You should now see your first command showing up in the Actions List in the middle of the screen.
  12. Press the Save button with the disk icon at the top of the screen to save this new bot.
  13. When prompted, give the name WorkingWithCSV.atmx and press the Save button to close to Save Task dialogue.
  14. Once the bot has been saved, press the Play button to launch the bot.
    1. In the bottom right corner of your screen, you should see a dialog window appear which shows the progress of your bot’s execution. In our case, our bot only has 1 line, so that window will pop up quickly and close as the bot has finished its execution.
    2. As the bot runs, you should see that Google Chrome opens and automatically navigates to the page we provided in the Open Program/File command.
  15. Leave this window open as we’ll use it for the next commands we’ll be adding.
  16. We want to have the bot download the CSV file automatically. To do that, we’ll need to add an Object Clone command to click the Download CSV button. In the Commands tab on the left side of the workbench, click and drag the Object Cloning command into the Action List.
    1. The Object Cloning window will appear. From Select Window drop-down, select the window titled Automation Anywhere Labs – CSV – Google Chrome
    2. If you don’t see this window title, make sure that the Google Chrome window which launched as a result of us running our bot for the first time is still open. If it’s open, but opened as a tab behind another tab, click and drag the tab off the browser so that our target browser session is open in its own chrome window.
    3. Click and hold the Capture button. Automation Anywhere will automatically switch the screen focus to the application indicated in the Select Window drop-down – in this case our Google Chrome CSV exercise page. With the left-click on your mouse still held down, highlight over the Download CSV button until there is a red outline flashing around the outside of the button. Once the red outline has highlighted the target element, you can release the left-click.
    4. After releasing the held mouse click, Automation Anywhere Workbench should set the focus of your screen back to the Object Cloning properties window.
    5. We’ll need to set up which action we need to take on this object. In the Select Action to Perform drop-down, select Left Click. Here, we’re telling the bot that it needs to Left Click on the page element (button) that we just highlighted. We’ll see in later steps that the Actions that the bot can perform differ based on the page element selected.
    6. Press the Save button to close the Object Cloning window.
  17. Lets take a step back and think about what our bot is doing: every time it runs it will launch Google Chrome… and every time it goes to that page, it will download that CSV. Maybe you would want that, maybe you wouldn’t…it depends on the use case – in this case, let’s set it up to ONLY download the CSV if we haven’t previously downloaded it.
  18. So far, every command we’ve dealt with has been running in sequence. In the last lab, we opened the browser, we extracted some text, we converted it, we entered it, and we submitted it. All of that had to happen in order, and all of that happened every time the bot ran. That specific order of operations is referred to as the sequence of our commands. We can tell the bot to only download the file in a specific condition using another problem solving concept known as selection. A selection statement will check for a condition – If that condition is true, then the bot will execute the statement(s) inside the condition. In this lab, we’re going to focus on a new type of command – an if statement, which is a type of selection statement.
    1. If the condition is true, then the bot will execute the statement(s) inside the condition.
    2. If the condition is not true, then bot doesn’t execute the statement(s) which are listed inside the condition.
    3. In this case, our if statement might read as:
      1. If the file MissingVendor.csv does not exist on the local machine, then
        1. Click the button to download it.
    4. An if-statement, or if-then-statement (both mean the same thing) – is a type of selection statement that lets us check a condition and take action if that condition is true.
  19. Let’s try one out in our bot. In the Commands tab in the Enterprise Client Workbench, look for the block of commands called If/Else. Click the small “+” next to these commands to see all of them.
    1. Inside of the If/Else command block, you should find a command which says File Does Not Exist.
      1. Note: This can be kind of confusing because we’re checking for a true result of a negative condition. Think of it this way – we’re looking to validate the absence of a file. If it is true that said file is absent, then we need to download it.
    2. Click and drag that to the bottom of the Actions List right below our Object Cloning command.
    3. In the If window that appears, notice the If command radio button at the top is set to If and the Selection Condition is set to File does not exist.
    4. The only remaining field that needs to be set is the Select File field. If you haven’t already downloaded the CSV from the exercise page, download it now – as that will make finding the correct path easier.
    5. With the MissingVendor.csv file already downloaded locally, press the Browse button and find the file downloaded file.
      1. Most likely this is going to be in your C:\Users\<your user name>\Downloads directory selecting the file name as MissingVendor.csv.
    6. Press Save to close the If command window.
  20. Quick recap of what we just did: the If command added 3 total lines of code.
    1. The first line is the condition we’re checking for – and it kind of reads like regular English – if the file doesn’t exist, then
    2. The second line that the if command added is indented. This is to let us know that it exists inside of the selection statement, and therefore would only execute if the selection statement resolved to true.
      1. Notice that this line is also in green. That’s to indicate that this line is a comment. Comments are used by developers to write human-readable text in their code to give some explanation of what each section of code is doing. It’s a helpful practice to add comments in your code so that when you have to go back and fix something 6 months from now, you can remember what was going on with each section of code. To customize the comment line added, double click it and a comment editor will appear – not required, but something to get in the good habit of doing.
    3. The last line that the if command added is an End If statement. That lets us know that the if statement that it is paired with is now over, and normal sequential code execution will resume.
  21. Our code as it stands is out of order. Line 2 – our Object Cloning line to click the Download CSV button – is before our if statement. It should go inside of our if statement because we only want that click to occur if the selection statement we set up resolves to true.
    1. Click and drag line 2 of the code – the Object Cloning line – letting go of it after it has been dragged over the comment line and the comment line has turned blue.
    2. That blue highlighting of a line is Automation Anywhere’s way of letting you know that its going to put whatever command(s) you are dragging directly below the highlighted line.
    3. The result is that your code should look like this – with the Object Cloning line as line 4.
  22. Testing along the way is part of the bot building process. Save your bot by clicking the blue disk icon at the top of the Workbench screen and close the exercise-page tab in Google Chrome.
    1. When we do our test, its important that we test both the true and false case for our selection statement to make sure that everything is working as expected.
    2. To test the true case – delete the file MissingVendor.csv from your Downloads directory.
      1. We need to delete it, because we want to make sure the bot can detect that the file is missing from the local directory and download it on its own.
    3. Press the green Play button to run the bot.
      1. Notice that it launches the exercise page in Google Chrome and the bot once again downloads the file by clicking the Download CSV button.
    4. To test the false case – leave the MissingVendor.csv file in the Downloads directory and close the exercise-page tab in Google Chrome once again.
    5. Press the green Play button to run the bot.
      1. Notice that this time the run-time window in the bottom right hand corner of the screen disappeared much more quickly, and the bot did NOT download another copy of the file.
    6. Doing this kind of testing is a VERY important part of the bot building process as we should always know how the bot is going to react to different situations.
  23. At this point our bot should have the exercise window open and we should have the CSV downloaded locally (whether it was already there before the bot run or not). The bot needs to be able to actually read from the CSV and we need to store all of the CSV’s data in our already-created variables.
  24. In the Commands list on the left side of the screen, click the ‘+’ next to the Excel command to open up a series of Excel commands.
    1. Drag the Open Spreadsheet Excel command on to the bottom of the Actions list.
    2. The Select Excel Command should have the Open Spreadsheet radio button selected.
    3. The Session Name command can be left as Default.
      1. This field becomes especially important if you have multiple spreadsheets open at once. Each would need to have a unique Session Name so that the bot knows which spreadsheet to take action on. For our purposes, we’ll only be using a single sheet for this lab.
    4. The Spreadsheet Path field should be set to the path to the CSV file that the bot downloaded.
      1. This path is likely C:\Users\<your user name>\Downloads\MissingVendor.csv (unless you’ve changed your default download directory).
      2. Note: we wont need it for this lab, but you CAN use variables as a part of things like file paths (by pressing F2) should you need to open dynamically named files or folders.
    5. If the spreadsheet we were using had multiple sheets, we could specify the Specific Sheet Name, but we only have one, so leave this as unfilled.
    6. Our CSV does have headers, so we can check the Contains Headers checkbox.
      1. This detail is specifically used for how Excel commands will work with Automation Anywhere’s looping – something we wont be using for this lab.
    7. Press the Save button to close the Excel command window.
  25. We now need to fetch the data from the spreadsheet and fill our variables. Click and drag the Get Cells command from the Excel commands group to the bottom of the Actions List.
    1. The Excel command window will pop up again, this time with the Get Cells radio button selected in the Select Excel Command section.
    2. Select the Get Single Cell radio button in the Get Cells section as we only want to get the value of a single cell for our variable.
    3. Notice the Session Name field showing up again – leave it as Default as that’s how our Open Spreadsheet command was set.
    4. Select the radio button for Specific Cell. This is telling the bot that we care about the data in a particular location to save to our Variable.
      1. Enter A2 as the specific cell – as this is our first real data cell of the sheet.
    5. In the Select Variable drop-down, select the vVendorName variable.
    6. Press the Save button to close the Excel command configuration window.
  26. We’ll need to repeat those steps to grab the remaining values from the spreadsheet. Follow the table below to set up the remaining Get Cells commands. You should have 10 total Get Cell commands in your code.
    1. Note – while you can follow the table below, check the spreadsheet yourself and make sure you understand why we are using the Excel locations that we are. Beyond getting things to work for this lab, its important to understand how you might extract data from Excel for your future endeavors as well.

      Variable Name

      Excel Location

      vVendorName (we just did this one) A2
      vVendorID B2
      vPrimaryContact C2
      vStreetAddress D2
      vCity E2
      vState F2
      vZip G2
      vEmail H2
      vActiveDiscount I2
      vNDA J2
  27. At this point, our bot should be able to open the exercise website, download the MissingVendor.csv (if needed), open the CSV in Excel, and extract out all Vendor details. Our next step is to start mapping those values back to the exercise page to fill the form properly. Start by dragging an Object Cloning command from the command pane on the left side of the screen over to the bottom of the Actions List.

    1. Select Automation Anywhere Labs – CSV – Google Chrome in the Object Cloning Select Window drop-down.
    2. Just like we did for pressing the Download CSV button earlier, click and hold the Capture button. As Automation Anywhere switches focus to the exercise page – highlight the cursor over the form’s first field – Vendor Name – releasing once there is a red outline around the field.
    3. Automation Anywhere will change the screen’s focus back to the workbench and display the Object Cloning configuration window again.
    4. In the Select Action To Perform drop-down, select Set Text.
    5. In the Text to Set field, press F2 to bring up the Insert Variable window and select vVendorName.
    6. Press the Save button to close the Object Cloning configuration window.
  28. Looking at the exercise page, you’ll see that the Vendor ID, Primary Contact, Street Address, and City fields are all normal input fields similar to the Vendor Name field. Repeat the process of creating Object Cloning commands and selecting the appropriate input fields for the Vendor ID, Primary Contact, Street Address, and City – mapping the corresponding variable to the Text to Set for each.

    Variable Name

    Exercise Page Field Name

    vVendorID Vendor ID
    vPrimaryContact Primary Contact
    vStreetAddress Street Address
    vCity City
  29. Notice that the State field on our exercise page is a drop down, not a normal input field. You’ve probably used fields like this yourself when ordering something from an online retailer and filling in your address details. Clicking the drop-down on the exercise page, we can see that it lists each state based on the state’s 2 character abbreviation…which happens to match how the state is filled in the MissingVendor.csv.
    1. Start by dragging an Object Cloning command from the command pane on the left side of the screen over to the bottom of the Actions List.
    2. Select Automation Anywhere Labs – CSV – Google Chrome in the Object Cloning Select Window drop-down.
    3. Just like we did for pressing the Download CSV button earlier, click and hold the Capture button. As Automation Anywhere switches focus to the exercise page – highlight the cursor over the State drop-down – releasing once there is a red outline around the field.
    4. When the Object Cloning window takes the focus of the screen again, notice that the options we have within the Select Action To Perform have changed quite a bit. This is because these actions are specific to the type of control we are interacting with.
    5. Choose the option that says Select Item By Text. Once selected, that option will show one additional field where you have the ability to select from a drop down of all available options and select one.
    6. Instead of selecting from that drop down, click into the Item to be selected field and press F2 to once again display the Insert Variables window.
    7. Select vState and press OK to close the Insert Variables window.
    8. Using our variable here will work because the CSV formatting of the state details matches the formatting of the details in the drop-down. IF we had full state text in one, and abbreviation in another, we’d have to first figure out how to translate from one to another before trying to select an option. Bottom line – using variables here helps us a ton as long as the text we want to select is available in the drop down.
    9. Press Save to save the Object Cloning configuration.
  30. The next 2 fields (Zip and Email Address) are normal input fields like we worked with in the beginning of the lab. Use this as an opportunity to test yourself in setting up 2 additional Object Cloning commands setting the text of each to its respective variable. Try doing these 2 without looking at the instructions we did before – if you get stuck, you can always flip back to the step we did earlier in setting up the Vendor ID field.

    Variable Name

    Exercise Page Field Name

    vZip Zip
    vEmail Email
  31. With those 2 Object Cloning commands out of the way, we’re on to the Active Discount Offered field on the exercise page. We need to make sure that our bot is dynamic – in that its able to handle situations where active discount is and is not offered.
    1. To accomplish this, we’ll need to use another selection statement like we used earlier in the lab. The selection statement will allow us to evaluate if a condition is true, then give specific instructions to the bot on how to proceed. If the condition isn’t true, we can still give the bot specific instructions on how to proceed. Let’s write this out with some of our human-readable language then translate it to code after.
      1. If the variable vActiveDiscount = YES then
        1. Check the yes box on the form
      2. Else/Otherwise
        1. Check the no box on the form
    2. This selection statement is a bit more complex than the one we used for determining whether to download the file. In the download if statement, we only had an if statement – if that didnt resolve to true, the code just moved on. In this case, we have an else condition. With an else condition, were basically saying “if the original condition did not resolve to true, then do this”. A command is going to execute either way, we just need to determine which one to execute based on our condition.
    3. Using our human-readable If/else statement as our guide, let’s create this same selection statement in our bot.
    4. In the Commands tab in the Enterprise Client Workbench, look for the block of commands called If/Else. Click the small “+” next to these commands to see all of them.
    5. Inside of the If/Else command block, you should find a command which says Variable. Click and drag this command from the Commands pane to the bottom of the Actions List.
    6. The If command window will appear with the Variable option set within the Selection Condition.
    7. Click the Edit button for the IF Condition field so that we can set up the conditions we want to check for.
    8. In the If Variable window that appears, click into the Variable field and press F2 to bring up the Insert Variable window – here, we want to select our vActiveDiscount variable and press the Insert button.
    9. Back on the If Variable window, set the Operator field to Equal To (=).
    10. In the Value field, leave the selection on Fix and type in the word YES
      1. Note: make sure its in all caps as that’s how our CSV has these values stored.
    11. Press the Save button to close the If Variable configuration window.
  32. If the condition from the selection statement we just created is true, we need the bot to click the Yes radio button on our exercise page. In the Actions List, we now have another section with an indented green comment. Click and drag an Object Cloning command from the Commands pane, dropping the command directly on top of the green comment line created by our ActiveDiscount selection statement.

    1. Select Automation Anywhere Labs – CSV – Google Chrome in the Object Cloning Select Window drop-down.
    2. Just like we did for setting up our input fields earlier, click and hold the Capture button, releasing only once a red outline has been drawn around the exercise page’s Yes radio button.
    3. In the Select Action To Perform field, select the Select action from the drop-down and press the Save button.
  33. As our code stands, the Yes option could be set, but we currently have no way to set the No radio button. To fix that, go back to the commands called If/Else. Click the small “+” next to these commands to see all of them.
    1. Click the drag the Else command from the If/Else command parent directly on top of the Object Cloning command we just added so that the Enterprise Client will place the line directly below the Object Cloning command.
    2. You should end up with something that looks like this
  34. Just like we added the Object Cloning command for the Yes option, click and drag another Object Clone on top of the Else command so that it will end up between the Else and the End If.
    1. In this Object Cloning command, capture the No radio button from the exercise page and one again choose Select as the action from the Select Action To Perform field.
  35. At this point, our if statement should look like this. If the Active Discount field is YES, the bot checks the Yes radio button, otherwise, the bot checks the No radio button.
    1. To make sure this concept sticks:
      1. What would the bot do if the Active Discount field in the CSV is NO?
        1. The bot would check the No button
      2. What would the bot do if the Active Discount field in the CSV is set to Banana?
        1. Read the code closely – the bot would check the No button…surprised? The way our code reads, we’re essentially saying “if Yes – check yes, else (meaning any other condition) – check the no button”. We could have structured the selection statement differently to account for specific conditions, but in this case we are using an If/else command…and the else is really a catch-all. The way we have it written, its pretty encompassing of any scenario for that field. Even if that field were left blank, the bot would check no. Selection statements can be really powerful, just make sure you understand the different conditions and outcomes.
  36. So far we’ve handled input fields, drop-downs, and radio buttons in this lab. Our last fill-able field on this exercise page is a checkbox. A checkbox allows for the capture of true/false conditions – which may be by themselves (in our case) or there may be several put together which may or may not have relationships to one another. In our exercise page, there is only a single checkbox which we will fill or leave alone based on the Non-Disclosure On File field in our MissingVendor.csv.
  37. Lets think through how to handle this before adding any commands. We have a Non-Disclosure field in our CSV. If the Non-Disclosure is YES, we want to check the box. If the Non-Disclosure is not yes, we don’t want to check the box at all. That means we should be able to get away with just a single If condition statement (without an else component) and a single Object Cloning command to check the box.
    1. In the Commands tab in the Enterprise Client Workbench, look for the block of commands called If/Else. Click the small “+” next to these commands to see all of them.
    2. Inside of the If/Else command block, you should find a command which says Variable. Click and drag this command from the Commands pane to the bottom of the Actions List.
    3. The If command window will appear with the Variable option set within the Selection Condition.
    4. Click the Edit button in the IF Condition field so that we can set up the conditions we want to check for.
    5. In the If Variable window that appears, click into the Variable field and press F2 to bring up the Insert Variable window – here, we want to select our vNDA variable and press the Insert button.
    6. Back on the If Variable window, set the Operator field to Equal To (=).
    7. In the Value field, leave the selection on Fix and type in the word YES
      1. Note: Make sure its in all caps as that’s how our CSV has these values stored.
    8. Press the Save button to close the If Variable configuration window.
  38. Click and drag an Object Cloning command inside the If statement dropping the command on top of the If statement’s green, indented, comment line (so that the new Object Clone command would be dropped below the comment)
    1. Select the exercise page’s title for the Selection Window as we’ve done for our previous clones and click and hold the capture button to capture the checkbox on the exercise page.
      1. Note: you may have to scroll down on the exercise page before doing your capture. Not sure how your screen is set up, but for me, the checkbox was a bit lower on the page. Don’t worry, the bot will know how to find the object even if you had to scroll some, but you want to make sure that whatever object you plan to interact with is visible on the page before starting your capture.
    2. In the Select Action To Perform drop-down, select Check as the option so the bot knows to check the box.
    3. At this point, your If statement should look like this
  39. Our bot needs to press the Register button to submit all the data that the bot has entered.
    1. Use the Object Cloning command as we have used in previous steps – highlighting over the Register button until it has a red outline.
    2. When the Object Cloning configuration appears after selecting the object, select the Action to Perform as Left-Click.
  40. In a real production environment with bot runs happening throughout the day, it’s important that any bot not leave stuff open – a good practice when creating bots is to make sure that they clean up whatever they may have left as a mess (open applications/windows).
    1. We need to leave Google Chrome open so we can see the result/accuracy of our form submission, so that we’ll leave alone for the purposes of the exercise.
    2. The MissingVendors.csv though, doesn’t need to stay open – lets close the spreadsheet when the bot finishes so there wouldn’t be any issues with another bot executing right after this one.
    3. In the Commands list on the left side of the screen, click the ‘+’ next to the Excel command to open up a series of Excel commands.
    4. Drag the Close Spreadsheet Excel command on to the bottom of the Actions list.
    5. In the Excel command configuration window that opens, make sure that the Close Spreadsheet radio button is selected and the Session Name field – which we discussed earlier – is left as Default. The Session Name in this case lets Excel uniquely identify which spreadsheet session should be closed.
    6. Excel will, by default, save any changes made to a spreadsheet/csv – in this case, we didn’t make any, so it really doesn’t matter either way.
    7. Press the Save button to close the Excel Command configuration.
  41. Finally – time to test. Click the Save button with the blue disk icon at the top of the screen to save your bot and press the green Play button.
  42. The bot should launch Google Chrome automatically downloading the spreadsheet (if needed), open Excel to extract the CSV’s content, fill the exercise page form, and submit. Assuming the bot ran successfully, you should get a message with the accuracy percentage and the total time taken. Like mentioned in previous labs, your processing time may be faster or slower based on a variety of factors (network speed, local machine specs, etc.) and shouldn’t be something to worry about.

Recap

Awesome accomplishment on completing this lab! Seriously – that was a lot of code that we added, so if you hung in there and got it done, be proud of what you just created. We used some new commands we hadn’t used before and learned to integrate selection statements into our automation. Let’s walk through a couple highlights of what our bot is doing as a brief recap to solidify some of the topics covered.

Problem Solving Concept – Sequence
Sequence in programming is the specific order that operations are executed. Sequence was very important to this lab. If our bot had tried to fill out the form before extracting the values from the CSV, it wouldn’t know what data to fill in. Likewise, if we tried to use the Get Cells commands before we opened the spreadsheet – it wouldn’t have worked as the Get Cell command is dependent on a spreadsheet being opened already. As you’re building bots in the future, make sure to think through how the sequence of operations may impact the performance and outcome of your bots.

Problem Solving Concept – Selection
A selection statement will check for a condition, if that condition is true, then the bot will execute the statement(s) inside the condition. We used these if statements to download a file only if it wasn’t already downloaded, check a radio button based on text we read from a CSV, and to check a checkbox only if the NDA field was YES. We use selection statements in pretty much everything we do in our daily lives even without really realizing it – if its raining outside, then I will open my umbrella. In this case, the umbrella would only be opened if it’s raining – if its sunny we, wouldn’t open it. Selection statements can even be nested – meaning we can do selection statements inside of other selection statements. These if statements are very powerful ways of telling our bots exactly what to do when certain conditions arise.

New Commands
Excel – We used the Excel commands to open the spreadsheet, read values, and eventually we closed the spreadsheet. In this lab, we were typing in the specific cell of exactly where our data should come from. Know that the Excel commands can be used to dynamically extract data (meaning when I don’t really know how many rows there are ahead of time) and even be used to update/insert values into a spreadsheet as well. We’ll get into some of those topics in future labs.
Object Cloning – We used Object Cloning commands in a new way for this lab. We saw that they can interact with regular text-based input fields, drop downs, radio buttons, html buttons, and checkboxes. We used different actions for each of these input types as different options were made available to us.

Next Steps

You’re doing great so far. Our next challenge will introduce reading from HTML tables and performing currency conversion to update fields in those same HTML tables. We’ll also look at a new problem solving concept called Repetition.

Want some more practice on this page?

  • Try to modify your local copy of the MissingVendors.csv to see how the bot reacts when the NDA field is NO or the Active Discount Offered field isn’t set to YES. The page’s built in scoring/result wont work in that case, but you can at least play around with how the bot reacts to those selection statements.
  • Instead of using Excel commands, one alternative way to completing this lab would be using the Read from CSV/Text command. Try replacing all of the Excel commands with the Read from CSV/Text approach. Hint – you’ll have to use the Variable command to assign your values from the CSV to the variables we created.