Excel Controlled Loop
You can read data from an Excel table with the Excel Controlled Loop element. It works in a similar way to the File Controlled Loop and also forms the framework for other Action Steps. The wizard provides you with numerous setting options for determining the start (Read mode) and the end (Terminate loop) of the iteration.
You can only use the Excel Controlled Loop Action Step within an Excel session. You enter the source file in the Excel Session.
Properties
Excel Operation Settings
-
Column The Excel sheet is read from this column.
-
Empty rows count for break Specifies the number of consecutive empty rows that the loop is to be terminated after.
-
End column The sheet is read up to this column.
-
End row Here, you can specify exactly the row that the sheet is to be read to.
-
Has a header Indicates whether you have selected the option. If you select this, the first row of the source file is used as the column header.
-
Maximum number of rows Specifies the maximum number of rows that are to be read.
-
Only read a maximum number of rows If you select this, you can restrict the number of rows to be read.
-
Read mode Here, you can see the selected read mode.
-
Regex condition for break Shows the regular expression that the iteration is to be stopped from.
-
Regex condition for first cell Shows the regular expression that the Excel sheet is to be read from.
-
Row Specifies the row that the Excel sheet is to be read from.
-
Sheet name Name of the spreadsheet. In the properties, you can only enter the name directly. In the wizard, however, you can select the spreadsheet directly or via the pin variable.
-
Stop (loop break) criterion Here, you can see the selected termination criterion (Terminate Loop)
Inbound Variables
-
Sheet name Name of the spreadsheet. In the properties, you can only enter the name directly. In the wizard, however, you can select the spreadsheet directly or via the pin variable.
-
Column The Excel sheet is read from this column.
-
Row Specifies the row that the Excel sheet is to be read from.
-
Regex to match
Outbound Variables
The contents of each column are returned as variables within the Excel Controlled Loop Action Step.
Iteration Count Number of loop cycles.
RowNumber Shows you the number of lines that will be read.
Wizard
Two settings are particularly important, Read mode and Terminate loop.
In Read mode, the read area is defined, i.e. the area which the data from a spreadsheet is to be read in. Everything outside of this area is not read and not analyzed.
The following options are provided:
-
Read entire sheet (default) The entire spreadsheet is read
-
Start reading from row and column Here, you specify the exact column and row that reading is to begin from.
-
Read part of sheet In contrast, here you can not only specify where reading is to begin from but also the point that reading should terminate at.
-
Read to row Here, you can specify the row that the spreadsheet is to be read to.
-
Read to column Here, you can specify the column that the spreadsheet is to be read to.
-
Start reading when condition matches You also have the option of determining the read area dynamically by using regular expressions here. If, for example, you enter ^PLZ (= Regex), the Action Step reads the spreadsheet from the cell in which the content starts with PLZ.
In Terminate loop, the terminate conditions are defined, i.e. here you have additional options for terminating the read operation.
-
On reaching the end of sheet area (default) The read operation is automatically terminated at the end of the Excel sheet.
-
On reaching empty rows The operation is terminated as soon as a particular number of consecutive empty rows has been reached.
-
On Regex match in the cell value Here, the end is determined dynamically by defining a regular expression. In this example, the read area ends as soon as the content of a cell begins with PLZ.
-
Only read a maximum number of rows Here, you can specify the maximum number of rows to be read.
If you have already specified a read area in Read mode, or used the Only read a maximum number of rows option to stipulate a maximum number of rows, and have simultaneously defined a termination condition under Terminate loop, the event that occurs first is executed.
If nothing else has been selected, the column headers are generated automatically (Column_1, 2, 3 etc.).
If you have selected the Has a header option in the wizard, the first row is used as the column header.
The data type of each column is recognized automatically.