CSV ⇄ JSON

Overview

CSV ⇄ JSON allows transforming csv to json and vice versa. This is useful when consuming or producing csv feeds.

Configuration

• Mode (mode) - select the required mode of operation (CSV to JSON or JSON to CSV)
• Target path (targetPath) - doc context node on which to place the created json / csv
• Delimiter (delimiter) - field separator, e.g. , ; or a tab. The default value is ,

CSV to JSON

• CSV (csv) - the csv to be converted, either the text itself or {{docNode}} to take it from doc
• First row is a header (header) - if true the first row names the fields and each row becomes an object, if false each row becomes an array of values. The default value is true
• Skip empty lines (skipEmptyLines) - if selected, blank lines and rows where every field is empty are dropped. The default value is true
• Trim fields (trimFields) - if selected, leading and trailing whitespace is stripped from every header and value. The default value is true
• Rebuild nested objects (expandNested) - if selected, dot-separated column names such as insured.firstName become nested objects. The default value is false

Every parsed value is a string. Use a Map or JSONata step to convert the columns that need numbers.

JSON to CSV

• JSON (json) - the object, array of objects or array of arrays to be converted to csv, either the records themselves or {{docNode}} to take them from doc
• Include header row (header) - if true the column names are written as the first row. The default value is true. Ignored for an array of arrays, which has no column names
• Expand nested objects (expandNested) - if selected, nested objects are flattened into dot-separated columns. The default value is false

Rows are separated by CRLF. Records that do not share the same keys get an empty field for the columns they are missing, and arrays of differing lengths are padded to the widest row.

Examples

Convert CSV to JSON

tags: []
sampleInputSchema: ""
sampleData:
  feed: "policy,premium\nP-01,100\nP-02,200"
description: ""
steps:
  - name: Step csv
    description: ""
    stepType: csv
    condition: ""
    config:
      mode: CSV to JSON
      csv: "{{feed}}"
      delimiter: ","
      header: true
      skipEmptyLines: true
      trimFields: true
      targetPath: rows

Execution result

{
  "doc": {
    "rows": [
      { "policy": "P-01", "premium": "100" },
      { "policy": "P-02", "premium": "200" }
    ]
  }
}

Convert JSON to CSV

tags: []
sampleInputSchema: ""
sampleData: {}
description: ""
steps:
  - name: Step csv
    description: ""
    stepType: csv
    condition: ""
    config:
      mode: JSON to CSV
      json:
        - policy: P-01
          premium: "100"
        - policy: P-02
          premium: "200"
      delimiter: ","
      header: true
      targetPath: result

Execution result

{
  "doc": {
    "result": "policy,premium\r\nP-01,100\r\nP-02,200\r\n"
  }
}

Convert nested JSON to CSV

tags: []
sampleInputSchema: ""
sampleData: {}
description: ""
steps:
  - name: Step csv
    description: ""
    stepType: csv
    condition: ""
    config:
      mode: JSON to CSV
      json:
        - policyNumber: POL-001
          insured:
            firstName: Sean
            lastName: O'Brien
      header: true
      expandNested: true
      targetPath: result

Execution result

{
  "doc": {
    "result": "policyNumber,insured.firstName,insured.lastName\r\nPOL-001,Sean,O'Brien\r\n"
  }
}

Convert an array of arrays to CSV

tags: []
sampleInputSchema: ""
sampleData: {}
description: ""
steps:
  - name: Step csv
    description: ""
    stepType: csv
    condition: ""
    config:
      mode: JSON to CSV
      json:
        - ["policy", "premium"]
        - ["P-01", "100"]
        - ["P-02", "200"]
      targetPath: result

Execution result

{
  "doc": {
    "result": "policy,premium\r\nP-01,100\r\nP-02,200\r\n"
  }
}