Skip to main content

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
PropertyTypeRequiredDefaultDescription
pathstringYes-JSON path of the source array
targetPathstringYes-Path where the document is written (text, or Base64 with outputAsBase64)
delimiterstringNo;Exactly one character between cells; not a line break and not "
lineEndingenumNoCrLfCrLf or Lf; never taken from the operating system
includeHeaderboolNotrueWrite a header row first
quotingenumNoMinimalMinimal, Text or All, see Quoting
trailingDelimiterboolNofalseEvery row, the header included, ends with the delimiter
trailingNewLineboolNotrueA line end after the last row
byteOrderMarkboolNofalseUTF-8 byte-order mark in front of the document (Excel needs it to read the file as UTF-8)
outputAsBase64boolNofalseWrite the Base64 of the UTF-8 bytes (byte-order mark included) instead of text
contentLengthTargetPathstringNo-Path where the byte length of the UTF-8 document is written (a long)
culturestringNoinvariantCulture of all numbers and dates, for example de-AT; an empty string is the invariant culture
columnsarrayNoall scalar propertiesOutput 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.

PropertyTypeDescription
headerstringHeader text; defaults to the last name of valuePath
valuePathstringJSON path relative to the record that selects one value (no wildcard, filter or recursive descent). Exclusive with value
valuestringConstant text written in every row. A constant cannot carry format, decimals, factor, timeZone or culture
dataTypeenumAuto (default), String, Number, Boolean or DateTime
formatstring.NET format string for numbers and dates, for example 0.###, 0.000 or dd.MM.yyyy HH:mm. Not allowed on String and Boolean
decimalsint0 to 28. The number is rounded to this many decimals, half away from zero, before it is formatted
factornumberThe number is multiplied by this factor before it is rounded and formatted (unit conversion, for example 4)
timeZonestringIANA id the date is converted into (for example Europe/Vienna); DateTime columns only, UTC when unset
culturestringOverrides the culture of the node for this column
requiredboolAn empty cell fails the node instead of being written
note

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, factor or culture applies, so 0.50 stays 0.50. Otherwise the value is multiplied by factor, rounded to decimals and formatted with format. 0.### writes 14 and 0.546, 0.000 writes 14.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 timeZone and written with format (yyyy-MM-ddTHH:mm:ss when unset).
  • Booleans are written as true and false; an absent or null value is an empty cell. An object or array at a valuePath is refused.
  • Culture: the invariant culture writes a decimal point, de-AT a 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​

ValueQuoted
MinimalOnly a value that contains the delimiter, a double quote, CR or LF; a double quote inside is doubled
TextStrings, formatted dates and the header cells; numbers and booleans stay bare, empty cells stay empty. A value that needs it is quoted as well
AllEvery non-empty cell

Behavior​

  • An empty array writes a header-only document when columns are configured and an empty string without them. The value is always written, also when it is empty.
  • A path that 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 columns list, a column with both value and valuePath, format, decimals, factor or timeZone on 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 outputAsBase64 the result is the content that CreateFileSystemUpdate@1 stores as a file-system item (the length from contentLengthTargetPath). SendEMail@2 then attaches the stored item by its binary id; it cannot attach Base64 directly.