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
-
Create a new bot:
-
On the left panel, click Automation.
-
Click .
-
In the Create Task Bot window, enter the bot name.
-
Accept the default folder location: \Bots\
To change the default bot storage location, click
Choose and follow the prompts.
-
Click Create and edit.
-
Open the NewProductNames.csv file that you just
created.
-
Double-click or drag the
action.
-
In the Session name field, enter
session 1.
-
Provide the file path to
NewProductNames.csv.
-
Select the Contains header option.
-
Open the ProductInventory.xlsx file that you just
created.
-
Double-click or drag the
action.
-
In the Session name field, enter
session
2.
-
Provide the file path to
ProductInventory.xlsx.
-
Choose to open the file in Read-write
mode.
-
Select the Contains header option.
-
Use the Go to cell
action to indicate the first cell in which to update the product
names.
-
Double-click or drag the
action.
-
In the Session name field, enter
session
2.
-
Select the Specific cell option and enter
B2.
-
Use a Loop
action to retrieve the cell values in each row from
ProductInventory.xlsx.
-
Double-click or drag the Loop
action.
-
Select the iterator.
-
In the Session name field, enter
session
2.
-
In the Loop through field, select All
rows.
-
In the Assign current value to this variable
field, create a rInventory variable.
-
Use a Loop
action to retrieve the cell values in each row from
NewProductNames.csv.
-
Drag the Loop
action into the For each row in worksheet
Loop container.
-
Select the For each row in CSV/TXT
iterator.
-
In the Session name field, enter
session 1.
-
In the Assign current value to this variable
field, create a rNewProduct variable.
-
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.
-
Double-click or drag the If
action into the For each row in csv/txt
Loop container.
-
Select the String condition.
-
In the Source value field, input
rInventory[0].
-
Select the Equals to (=) operator.
-
In the Target value field, input
rNewProduct[0].
-
Use the Set cell and Go to cell
actions to update the product name and move to the cell
below.
-
Double-click or drag the
action.
-
In the Session name field, enter
session
2.
-
Select the Active cell option.
-
In the Cell value field, input
rNewProduct{Name}
-
Double-click or drag the
action.
-
In the Session name field, enter
session
2.
-
From the Active cell drop-down list, select
One cell below.
-
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.
-
Drag the
action.
-
Drag the
action.
-
Close the files.
-
Double-click or drag the
action.
-
In the Session name field, enter
session
2.
-
Select the Save changes option.
-
Double-click or drag the
action.
-
In the Session name field, enter
session 1.
-
Click Save.
-
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 |