Example of transferring data from CSV file to Excel worksheet

Build a bot that reads data from a CSV file and updates the data in an Excel worksheet, using actions from the CSV/TXT, Excel advanced, IF/ELSE, and Loop packages.

Prerequisites

Before you start building the bot, create the following data sets on your desktop in the specified file formats:

Data set 1: ProductInventory.xlsx
Item number Name Count Category Unit price Taxable
A0001 Milk 15 Grocery 3 N
A0002 Eggs 6 Grocery 4 N
A0003 Flower 3 Garden 10 Y
A0004 Table 1 Home 50 Y
A0005 Towel 4 Home 10 Y
A0006 Dog Food 16 Pet 22 N
A0007 Paint 43 Home 12 Y
Data set 2: NewProductNames.csv
Item number Name
A0005 Hand Towel
A0002 Chicken Eggs
A0003 Sunflower
A0004 Coffee Table
A0006 Dog Food - Small Dogs
A0007 Paint - Dark Blue
A0001 2% Milk

In this example, you will build a bot to update the product inventory in an Excel worksheet with new product names from a CSV file. The Excel worksheet has old product names and the CSV file has new product names. You will relate the data in the Excel worksheet and the CSV file with item number and update the Excel worksheet with the new item name corresponding to the item number. Use actions from the CSV/TXT, Excel advanced, IF/ELSE, and Loop packages

Procedure

  1. 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.
  2. Open the NewProductNames.csv file that you just created.
    1. Double-click or drag the CSV/TXT > Open action.
    2. In the Session name field, enter session 1.
    3. Provide the file path to NewProductNames.csv.
    4. Select the Contains header option.
  3. Open the ProductInventory.xlsx file that you just created.
    1. Double-click or drag the Excel advanced > Open action.
    2. In the Session name field, enter session 2.
    3. Provide the file path to ProductInventory.xlsx.
    4. Choose to open the file in Read-write mode.
    5. Select the Contains header option.
  4. Use the Go to cell action to indicate the first cell in which to update the product names.
    1. Double-click or drag the Excel advanced > Go to cell action.
    2. In the Session name field, enter session 2.
    3. Select the Specific cell option and enter B2.
  5. Use a Loop action to retrieve the cell values in each row from ProductInventory.xlsx.
    1. Double-click or drag the Loop action.
    2. Select the Excel Advanced > For each row in worksheet iterator.
    3. In the Session name field, enter session 2.
    4. In the Loop through field, select All rows.
    5. In the Assign current value to this variable field, create a rInventory variable.
  6. Use a Loop action to retrieve the cell values in each row from NewProductNames.csv.
    1. Drag the Loop action into the For each row in worksheet Loop container.
    2. Select the For each row in CSV/TXT iterator.
    3. In the Session name field, enter session 1.
    4. In the Assign current value to this variable field, create a rNewProduct variable.
  7. Use an If action to compare the item number from ProductInventory.xlsx to the item number from NewProductNames.csv to ensure they are the same before moving on to the next action.
    1. Double-click or drag the If action into the For each row in csv/txt Loop container.
    2. Select the String condition.
    3. In the Source value field, input rInventory[0].
    4. Select the Equals to (=) operator.
    5. In the Target value field, input rNewProduct[0].
  8. Use the Set cell and Go to cell actions to update the product name and move to the cell below.
    1. Double-click or drag the Excel Advanced > Set cell action.
    2. In the Session name field, enter session 2.
    3. Select the Active cell option.
    4. In the Cell value field, input rNewProduct{Name}
    5. Double-click or drag the Excel Advanced > Go to cell action.
    6. In the Session name field, enter session 2.
    7. From the Active cell drop-down list, select One cell below.
  9. Insert an alternative to the If action: if the item numbers are not the same, the bot continues to the next row in NewProductNames.csv.
    1. Drag the If > Else action.
    2. Drag the Loop > Continue action.
  10. Close the files.
    1. Double-click or drag the Excel advanced > Close action.
    2. In the Session name field, enter session 2.
    3. Select the Save changes option.
    4. Double-click or drag the CSV/TXT > Close action.
    5. In the Session name field, enter session 1.
  11. Click Save.
  12. Run the bot.
    The bot updates the ProductInventory.xlsx file to look like the following table:
    Data set 3: ProductInventory.xlsx
    Item number Name Count Category Unit price Taxable
    A0001 2% Milk 15 Grocery 3 N
    A0002 Chicken Eggs 6 Grocery 4 N
    A0003 Sunflower 3 Garden 10 Y
    A0004 Coffee Table 1 Home 50 Y
    A0005 Hand Towel 4 Home 10 Y
    A0006 Dog Food - Small Dogs 16 Pet 22 N
    A0007 Paint - Dark Blue 43 Home 12 Y