Example of transferring data from CSV file to Excel worksheet

In this example, you build a bot to update the product inventory in an Excel worksheet with new product names from a CSV file. Use actions from the CSV/TXT, Excel advanced, IF/ELSE, and Loop packages.

1. Open a new bot:
   1. On the left panel, click Automation.
   2. Click Create new > Bot.
   3. In the Create Task Bot window, enter a bot name.
   4. Accept the default folder location \Bots\.
      To change where your bot is stored, 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