Example of reading data from a spreadsheet

Build a bot that reads data from one Excel worksheet and writes it to another worksheet.

Prerequisites

To use the Excel advanced package, Microsoft Excel must be installed on the device where you run the sample bot.

Suppose an Order Processing team receives order data in a spreadsheet as part of the Order Fulfillment process. The team automates the process of creating a priority shipping list from the spreadsheet and sharing it with the Picking department. The sample bot in this tutorial performs the following tasks:

  • Reads spreadsheet data that contains a list of orders using the Excel advanced package.
  • Creates a new spreadsheet containing a list of orders that need to be shipped as a priority.

For this tutorial, you can download and use a sample Excel file from here.

Procedure

  1. Create a new bot.
    1. On the left panel, click Automation.
    2. Click Create new > Task Bot.
    3. In the Create Task Bot window, enter Priority List in Name.
    4. Click Create and edit.
  2. To read data from the spreadsheet that contains the list of orders, open the Excel sheet.
    1. From the Actions panel, find and add the Excel advanced > Open action to the Bot editor.
    2. Click File and select the sample Excel file you downloaded.
    3. Select Sheet contains a header.
    4. Select Specific sheet name and enter order_list.
    5. In Create Excel session, click Local session, and enter orderlist as the session name.
  3. For the list of priority orders, create a new spreadsheet.
    1. From the Actions panel, find and add the Excel advanced > Create workbook action to the Bot editor.
    2. In File path, specify a file path for the new file. For example, C:\\bots\priority-orders.xlsx .
    3. In Sheet name, enter priority orders.
    4. In Create Excel session, select Local session and enter prioritylist as the session name.
    5. Save the changes.
  4. Copy the header from the source spreadsheet to the new spreadsheet.
    1. From the Actions panel, find and add the Excel advanced > Get multiple cells action to the Bot editor.
    2. In Select range of cells to be returned, select Specific rows, and then enter 1 in both the From row and to row fields.
    3. In Session name, enter orderlist.
    4. To store the copied values to a variable, create and assign a variable named HeaderData in Assign value to the variable. The values are stored in a data table variable.
    5. Save the changes.
  5. Write the copied header to the new sheet.
    1. Find and add the Excel advanced > Write from data table action to the Bot editor.
    2. In Enter data table variable, select HeaderData.
    3. Click Specific worksheet and enter priority orders.
    4. In Specify the first cell, enter A1.
    5. In Session name, enter prioritylist.
    6. Save the changes.
  6. Before you write the data to the new spreadsheet, make sure that the A2 cell is selected on the new worksheet. To do so:
    1. From the Actions panel, find and add the Excel advanced > Go to cell action to the Bot editor.
    2. In Specific cell, enter A2.
    3. In Session name, enter prioritylist.
    4. Save the changes.
  7. Loop through the list of orders in the source spreadsheet.
    1. From the Actions panel, find and add the Loop action to the Bot editor.
    2. From Iterator, select For each row in worksheet for Excel advanced.
    3. In Loop through, select All rows.
    4. In Session name, enter orderlist.
    5. In Assign the current value to this variable, select Record, and then create a variable named order-record.
    6. Save changes.
  8. Select the records where ShippingType equals Priority.
    1. a. From the Actions panel, find and add the If action within the Loop action.
    2. In Condition, select String condition.
    3. In Source value, enter $order_record[4]$ to select the fifth column in the current record.
    4. In Operator, select Equal to(=).
    5. In Target value, enter Priority.
    6. Save the changes.
  9. If a matching record is found, write the record to the new spreadsheet. You can achieve this by using the Set cell action as many times as the number of columns in the source data. However, this example uses the Loop action to achieve the same result.
    1. From the Actions panel, find and add the Loop action within the If action.
    2. From Iterator, select For each value in record.
    3. In Record variable, select order-record.
    4. In Assign the current value to this variable, select create a variable named RecordValue.
    5. Save changes.
    6. On the Variables panel, create a variable with the following details:
      • In Name, enter RecordValueCounter
      • In Type, select Number.
      • In Default value, enter 0.
    7. From the Actions panel, find and add the Excel advanced > Set cell action.
    8. Make sure Active cell is selected.
    9. In Cell value, enter $order-record[$recordValueCounter$]$.
    10. In Session name, enter prioritylist.
    11. To move the cursor to the next cell in the row, find and add the Excel advanced > Go to cell action, and then select One cell to the right in Active cell.
    12. In Session name, enter prioritylist.
    13. To increment the recordValueCounter variable, find and add the Number > Increment action. Select recordValueCounter in both Enter number and Assign the output value to a variable fields. In Enter increment value, enter 1.
    14. Save the changes.
  10. Reset the recordValueCounter variable to zero.
    1. Find and add the Number > Decrement action after the inner loop but within the If action.
    2. Select recordValueCounter in both Enter number and Assign the output value to a variable fields.
    3. In Enter increment value, enter 7.
    4. Save the changes.
  11. Move the cursor to the next row before the next iteration.
    1. Find and add the Excel advanced > Go to cell action within the first Loop action (after the Decrement action).
    2. Click Active cell and select Beginning of the row.
    3. In Session name, enter prioritylist.
    4. Find and add the Excel advanced > Go to cell action.
    5. Click Active cell and select One cell below.
    6. In Session name, enter prioritylist.
    7. Save the changes.
  12. Run the bot.