Excel Controlled Loop
Use Excel Controlled Loop to read an Excel file and make the data available as variables. This action step works in a similar way to the File Controlled Loop and also forms the framework for other action steps. The wizard provides setting options for determining the start (Read mode) and the end (Terminate loop) of the iteration.
The Excel Controlled Loop action step works only within an Excel Session, in which you specify the file to read.
Excel Control Loop automatically recognizes the data type of each column. For Date types, the action step uses the system’s locale settings to read and display the dates to ensure that the format remains unchanged.
Properties
Excel Operation Settings
-
Column
The starting column in the Excel sheet to read data from.
-
Empty rows count for break
The number of consecutive empty rows after which the loop terminates.
-
End column
The ending column in the Excel sheet to read data from.
-
End row
The last row to read and terminate the loop.
-
Has a header
Select this option to use the first row of the source file as the column header.
-
Maximum number of rows
The maximum number of rows to read.
-
Only read a maximum number of rows
Select this option to restrict the number of rows to read.
-
Read mode
The selected read mode.
-
Regex condition for break
The regular expression at which the iteration stops.
-
Regex condition for first cell
The regular expression for the cell to start reading from in the Excel sheet.
-
Row
The row to start reading from in the Excel sheet.
-
Sheet name
Name of the spreadsheet. Enter the name manually using this property or use the wizard to select the spreadsheet directly or via the pin variable.
-
Stop (loop break) criterion
The selected termination criterion (Terminate Loop).
Troubleshooting settings
-
Disable recalculation of date formulas
Disables the recalculation of cell values formatted as DateTime to prevent RPA Builder from becoming unresponsive while it finishes calculating formulas that contain many dependencies.
Inbound Variables
-
Sheet name
Name of the spreadsheet. Enter the name manually using this property or use the wizard to select the spreadsheet directly or via the pin variable.
-
Column
The starting column in the Excel sheet to read data from.
-
Row
The row to start reading from in the Excel sheet.
-
Regex to match
Outbound Variables
Excel Controlled Loop returns the contents of each column as variables.
-
Iteration Count
Number of loop cycles.
-
RowNumber
The number of lines to read.
Wizard
Use Read mode to define the read area. Excel Controlled Loop does not read nor analyze data that is outside of the defined area.
The following read modes are available:
-
Read entire sheet (default)
-
Start reading from row and column
Select this mode to specify the column and row to start reading from.
-
Read part of sheet
Select this mode to specify both a starting and an ending point to read from.
-
Read to row
Select this option to specify the row in the spreadsheet to start reading from.
-
Read to column
Select this option to specify the column in the spreadsheet to start reading from.
-
Start reading when condition matches
Select this option to determine the read area dynamically by using regular expressions. For example, if you specify
^PLZ
, the action step reads the spreadsheet from the cell in which the content starts withPLZ
.
Use Terminate loop to specify the terminate conditions for the read operation.
-
On reaching the end of sheet area (default)
-
On reaching empty rows
The operation ends after reading the specified number of consecutive empty rows.
-
On Regex match in the cell value
The operation ends dynamically based on the specified regular expression. For example, if you specify
^PLZ
, the read operation ends when the content of a cell begins withPLZ
. -
Only read a maximum number of rows
The operation ends after reading the specified number of rows.
If you specify a read area in Read mode or use the Only read a maximum number of rows option and you also define a termination condition under Terminate loop, the read operation finishes when the first of these conditions is met.
Click Preview to display the contents of the selected read area. The preview shows a maximum of 20 rows.
To use the first row as the column header, select Has a header. If you don’t specify headers, the action step automatically creates headers as Column_1, Column_2, etc.
To change the data type of each column, use the Type: dropdown menus in the preview.