Example of entering data into a web form from a spreadsheet

Build a bot that reads multiple rows of data from an XLSX sheet and enters it into a web form. Use actions from the Excel advanced, Loop, and Recorder packages to build the bot.

Prerequisites

Before you start building the bot, you can create the following data sets on your desktop in .xlsx format as below.


Employee name	Age	Claim amount	Claim date	Claim type
Mike	30	469	22/3/2022	Medical
Sarah	34	766	28/3/2022	Dental
Chris	41	428	13/05/2022	Medical
Samantha	45	1000	09/05/2022	Medical

To read values from an Excel file and input them into a web form, do the following:

Procedure

Create a new bot:
1. On the left panel, click Automation.
2. Click Create new > Bot.
3. In the Create Task Bot window, enter the bot name.
4. Accept the default folder location: \Bots\
  To change the default bot storage location, click Choose and follow the prompts.
5. Click Create and edit.
Open the Excel file.
1. Double-click or drag the Excel advanced > Open action.
2. Enter a session name.
3. Select the Excel file.
4. Mark the Sheet contains a header option.
By marking the Sheet contains a header option, you enable the bot to search for the column by the header name during run time.
Launch the website.
1. Double-click or drag the Browser > Open action.
2. Enter the website URL which consists the webform to update the data.
Retrieve the worksheet values and store them in a Table variable.
1. Double-click or drag the Excel advanced > Get multiple cells action.
2. Enter the same session name you used in the Excel advanced > Open action.
3. Select All rows from the drop-down list.
4. Create a Table variable using the icon to the right of the Assign to variable drop-down list.
Instruct the bot to process the data row by row.
1. Double-click or drag the Loop action.
2. Select the For each row in table iterator.
3. Select the same Table variable that you used in Get multiple cells.
4. Create a Record variable using the icon to the right of the Assign to variable drop-down list.
The Record variable holds all of the values for one row. With each iteration of the Loop, the bot retrieves the values of the next row and stores them in the Record variable, overwriting the values from the previous row.
Map the first column header to the web form textbox.
1. Double-click or drag the Recorder > Capture action.
2. Select the same window you opened with the Open action.
3. Click Capture object.
4. Hover over the textbox until a red outline appears.
5. Click the textbox.
6. Return to the Control Room.
7. Verify that the Control Type value is TEXTBOX.
8. Select Set text from the Action drop-down list.
9. In the Keystrokes field, insert the same Record variable that you used in the Loop.
10. Select the By name option and copy-paste the first column header into the field.
Repeat the sub-steps in step 5 to map the other columns, with the following differences:
1. Instead of searching for the browser window title, insert the Window variable generated by the Recorder.
2. When inserting the Record variable in the Keystrokes field, copy-paste the subsequent column header into the "By name" field.
Capture the Submit button.
1. Double-click or drag the Recorder > Capture action.
2. Insert the Window variable generated by the Recorder.
3. Click Capture object.
4. Verify that the Control Type value is BUTTON.
5. Select the Click action.
Click Save.

Automation 360

Example of entering data into a web form from a spreadsheet