ConvertToCsv@1
Node ConvertToCsv@1 turns an array of records into one CSV document. It writes a header row, quotes values the way RFC 4180 describes, formats numbers and dates per column (with rounding, a factor, a time zone and a culture), and can add a UTF-8 byte-order mark or hand the document over as Base64, so that a following node can store it as a file or attach it to an e-mail.
The node is generic. For fixed layouts without any quoting use RenderDelimitedText@1.
Adapter Prerequisites
Node Configuration
For fields path, targetPath, targetValueWriteMode, and targetValueKind, see Overview. path selects the source array; every element must be an object.
transformations:
- type: ConvertToCsv@1
path: $.rows
targetPath: $.csv
delimiter: ";"
lineEnding: Lf
quoting: Text
trailingDelimiter: true
byteOrderMark: true
outputAsBase64: true
contentLengthTargetPath: $.csvLength
columns:
- header: Datum
valuePath: $.from
dataType: DateTime
format: dd.MM.yyyy HH:mm
timeZone: Europe/Vienna
- header: kWh
valuePath: $.kwh
decimals: 3
format: "0.###"
- header: kW
valuePath: $.kwh
factor: 4
decimals: 3
format: "0.###"
- header: Status
value: VALID
| Property | Type | Required | Default | Description |
|---|---|---|---|---|
path | string | Yes | - | JSON path of the source array |
targetPath | string | Yes | - | Path where the document is written (text, or Base64 with outputAsBase64) |
delimiter | string | No | ; | Exactly one character between cells; not a line break and not " |
lineEnding | enum | No | CrLf | CrLf or Lf; never taken from the operating system |
includeHeader | bool | No | true | Write a header row first |
quoting | enum | No | Minimal | Minimal, Text or All, see Quoting |
trailingDelimiter | bool | No | false | Every row, the header included, ends with the delimiter |
trailingNewLine | bool | No | true | A line end after the last row |
byteOrderMark | bool | No | false | UTF-8 byte-order mark in front of the document (Excel needs it to read the file as UTF-8) |
outputAsBase64 | bool | No | false | Write the Base64 of the UTF-8 bytes (byte-order mark included) instead of text |
contentLengthTargetPath | string | No | - | Path where the byte length of the UTF-8 document is written (a long) |
culture | string | No | invariant | Culture of all numbers and dates, for example de-AT; an empty string is the invariant culture |
columns | array | No | all scalar properties | Output columns in order, see Columns; an empty list is an error |
Columns
Without columns the node takes every scalar property of the records in the order of their first appearance; the property name is the header. Nested objects and arrays are left out.
| Property | Type | Description |
|---|---|---|
header | string | Header text; defaults to the last name of valuePath |
valuePath | string | JSON path relative to the record that selects one value (no wildcard, filter or recursive descent). Exclusive with value |
value | string | Constant text written in every row. A constant cannot carry format, decimals, factor, timeZone or culture |
dataType | enum | Auto (default), String, Number, Boolean or DateTime |
format | string | .NET format string for numbers and dates, for example 0.###, 0.000 or dd.MM.yyyy HH:mm. Not allowed on String and Boolean |
decimals | int | 0 to 28. The number is rounded to this many decimals, half away from zero, before it is formatted |
factor | number | The number is multiplied by this factor before it is rounded and formatted (unit conversion, for example 4) |
timeZone | string | IANA id the date is converted into (for example Europe/Vienna); DateTime columns only, UTC when unset |
culture | string | Overrides the culture of the node for this column |
required | bool | An empty cell fails the node instead of being written |
The type of a column is configured with dataType, not type: inside a pipeline definition type is taken by the serializer and never reaches the column.
Values and formatting
- Numbers keep their raw JSON token when no
format,decimals,factoror culture applies, so0.50stays0.50. Otherwise the value is multiplied byfactor, rounded todecimalsand formatted withformat.0.###writes14and0.546,0.000writes14.000. - Dates are read as ISO 8601 strings. A value without an offset is read as UTC, the way the stream-data nodes read it. The date is converted into
timeZoneand written withformat(yyyy-MM-ddTHH:mm:sswhen unset). - Booleans are written as
trueandfalse; an absent or null value is an empty cell. An object or array at avaluePathis refused. - Culture: the invariant culture writes a decimal point,
de-ATa decimal comma and German day and month names. A thousands separator only appears when the format asks for one (#,##0.00). Text that is read from a string is always read invariant:"1234.5"is 1234.5 whatever the output culture is. An unknown culture fails the node. With a decimal comma choose a delimiter other than,.
Quoting
| Value | Quoted |
|---|---|
Minimal | Only a value that contains the delimiter, a double quote, CR or LF; a double quote inside is doubled |
Text | Strings, formatted dates and the header cells; numbers and booleans stay bare, empty cells stay empty. A value that needs it is quoted as well |
All | Every non-empty cell |
Behavior
- An empty array writes a header-only document when
columnsare configured and an empty string without them. The value is always written, also when it is empty. - A
paththat is not an array, or a record that is not an object, fails the node. - Configuration mistakes fail the node before anything is written: blank paths, a delimiter that is not exactly one character, an unknown enum value, an empty
columnslist, a column with bothvalueandvaluePath,format,decimals,factorortimeZoneon a type they do not apply to, an unknown time zone or culture, and value paths that do not parse or select a set. - With
outputAsBase64the result is the content thatCreateFileSystemUpdate@1stores as a file-system item (the length fromcontentLengthTargetPath).SendEMail@2then attaches the stored item by its binary id; it cannot attach Base64 directly.