Iterate File Lines – importing a text, CSV or Excel file row by row¶
The command "Iterate File Lines" runs its inner commands once for every line of a text or CSV file, or for every row of an Excel workbook. This page is for administrators who build imports.
1. At a glance¶
| Purpose | Loop over the lines or rows of a file and hand each one to the inner commands |
| Typical use | Import a CSV or Excel file into records; process a text file line by line |
| Where in the editor | Toolbar menu "Files" → "Iterate File Lines" |
| Needs a user interface | No |
| Related | Get File Data, Iterate Files, Iterate JSON |
The file can be a file on the application server, or content the action already has in a variable – for example a file read with Get File Data or the body of an HTTP Request.
2. Settings¶
The settings are on the tab "Settings"; the tab "Column Mappings" appears for CSV and Excel. "Display Name", "Description" and "Execution Condition" are on the tab "General" (see Actions). The inner commands are placed in the lane of the command on the canvas.
| Setting | Meaning | Default / Required |
|---|---|---|
| "File Path or Content" | Expression for the path of a file on the application server – or, with "Is File Content" on, the content itself | Required |
| "Data Type" | "Plain Text", "CSV" or "Excel (XLSX)" | "Plain Text" |
| "Is File Content" | The expression returns the content, not a path | Off |
| "Is Base64" | The content is base64 encoded and is decoded first. Can only be switched on together with "Is File Content". | Off |
| "Skip First Line" | The first line or row is a header and is not iterated | Off |
| "Custom Line Delimiter" | Plain text and CSV: the text that separates lines | Empty: line breaks (CR LF, CR or LF) |
| "Iteration Variable" | Plain text only: the variable that receives the current line | Required for plain text |
| "CSV Column Delimiter" | CSV only: the character between columns, for example ; |
Empty: comma |
| "CSV Text Qualifier" | CSV only: the character that encloses a value containing the delimiter. Doubled inside a value, it stands for itself. | Empty: double quote |
| "Column Mappings" | CSV and Excel: one entry per column – "Variable Id" (the variable) and "File Column Id" (the column) | Required for CSV and Excel |
Column mappings¶
"File Column Id" names the column in one of two ways:
| Value | Meaning |
|---|---|
| A number | The position of the column, counted from 0. 0 is the first column. |
| Anything else | The name of the column, compared with the values of the first row without regard to case |
As soon as one mapping names its column, the first row is treated as the header and is not iterated – even if "Skip First Line" is off. With positional mappings only and "Skip First Line" off, every row is data.
A column name that is not found, or a position beyond the end of the row, leaves the variable unchanged for that row.
3. Behaviour¶
- The expression is evaluated. If it is empty, or the file cannot be read, the command is skipped. Content that is not valid base64 (with "Is Base64" on) also skips the command.
- Text is read as UTF-8; a byte order mark of another Unicode encoding is recognised.
- A line break at the very end does not create an extra, empty line. Empty lines in between are iterated.
- For each line or row, the variables are set and the inner commands run. They work on the same record as the command and share the variables of the action.
- If an inner command ends the action or fails, the loop ends as well.
Excel workbooks¶
Only the first worksheet is read, and the cell values are read as text. The rows and columns
are taken from the area of the sheet that holds data: a table that starts in cell B3 is read from
there, and its first column is column 0 of the mappings. Content passed with "Is File Content"
must be base64 – switch on "Is Base64".
4. Examples¶
Import a semicolon separated price list with a header row.
File content:
Article;Description;Price
A-100;Bolt M8;0.12
A-101;Nut M8;0.08
- "Data Type"
CSV, "CSV Column Delimiter"; - "Column Mappings":
ArticleNo←Article,Price←Price - Inside: a command that creates or updates the article record with the two variables.
Because the mappings name their columns, the header row is not imported.
Read an Excel file uploaded into a files block. Get File Data writes the file
into Upload. "Iterate File Lines" with "File Path or Content" Upload, "Is File Content"
and "Is Base64" on, "Data Type" Excel (XLSX), "Column Mappings" CustomerNo ← 0,
Name ← 1, "Skip First Line" on.
5. Limitations¶
- A line break inside a quoted CSV value is not supported: every line break starts a new row.
- Only the first worksheet of a workbook is read; formats such as
.xlsor.odsare not supported. - Values arrive as text. Convert numbers and dates with expressions where needed.
- The whole file is read into memory before the loop starts.