Skip to content

Example 1

Example 1

Introduction

The goal of this example is to read an excel file to a data-list, then repeat over the data-list and for each data-item select the ID column text and perform a check on the 7th digit in order to determine if the gender of the person is Male of Female. The gender is then added to the data-list and written to a new excel file.

Read excel file to data-list

Excel can be automated with the use of the Read Excel file to data-list command to read an excel files data into a data-list.

Read Excel Command

The Read Excel file to data-list command settings can be edited to have the Header options, Fields to import, Filepath, Worksheet name, and Delay filled out manually or have the appropriate data types linked.

Read Excel Settings
Read Excel Settings
Read Excel Settings

Repeat over data-list

The data-list can now be repeated over to access each individual data-item with the Repeat over data-list command and then use Select data-field from data-item command to select the ID field for each repeat.

Read Excel Settings
Read Excel Settings

Select characters from text

The Select characters from text command will then be used to extract the 7th character of the ID text value.

Select Characters
Select Characters

Convert text to number

When number is

The ID text is then converted into number with the Convert text to number command and then use the When number is command to determine if the number is less than or equals to 4.

Select Characters
Select Characters

If the number is not less then or equals to 4, then another When number is command is used to determine if the number is less than or equals to 9.

Select Characters
Select Characters

Set text value

If the number is less then or equals to 4 text is set to Female, if the number is less than or equals to 9 text is set to Male.

Set Text
Set Text
Set Text

Update data-item

The new text is then updated into the Gender column of that data-item with the use of the Update data-item command.

Update Data-item
Update Data-item

Add data-item to data-list

The data-item is then added to a new data-list via an Internal Variable and the Append data-item to data-list.

Append to data-list

Set data-list value

After the loop is over the Set data-list value command is used to set the previously compiled data-list with all of the Gender values.

Set data-list

Write data-list to Excel file

The data-list is then written to a new excel file with the Write data-list to Excel file

Set data-list
Set data-list