Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 9 Next »

What is CSV - JSON Transformation?

A CSV (Comma Separated Values) format which complies with the RFC4189 standard, could look like this:

"firstName", "lastName", "age"
"Sam", "Meyer", "48"
"Mariah", "Smith", "35"

Before you can work with such a file format, you have to convert (transform) this structure into a JSON structure. The same is true in case you would like to convert a given JSON document into a CSV structure, for example to upload to some external system

Transfrom CSV → JSON

For this, PIPEFORCE provides the transformer command transform.csv.json which expects a CSV file which complies with the RFC4189 standard in the body or as input parameter of the command and converts it to a JSON document which can then be used for further processing.

See the commands reference for details about the available parameters of this command.

Example 1: Arrays output format

If you use the transformer without any additional parameters, the JSON output will contain a nested arrays format for the rows:

pipeline:

  # Set the CSV in the body
  - set.body:
      value: |
        "firstName","lastName","age"
        "Max","Smith","38"
        "Susann","Mayr Wan","44

  - transform.csv.json

The default output will look like this:

{
  "columnsCount": 3,
  "rowsCount": 2,
  "headers": ["firstName","lastName","age"],
  "rows": [
    ["Max","Smith","38"],
    ["Susann","Mayr Wan","44"]
  ]
}

Example 2: Headers in rows

By default the column header names of the CSV will be shown in an extra field headers of the resulting JSON.

It is also possible to have these header names as part of the rows array and skip the extra headers field:

pipeline:

  # Set the CSV in the body
  - set.body:
      value: |
        "firstName","lastName","age"
        "Max","Smith","38"
        "Susann","Mayr Wan","44"

  - transform.csv.json:
      showHeadersField: false

The output will look like this:

{
  "columnsCount": 3,
  "rowsCount": 3,
  "rows": [
    ["firstName","lastName","age"],
    ["Max","Smith","38"],
    ["Susann","Mayr Wan","44"]
  ]
}

Note that the rowsCount now also counts the header line.

Example 3: Hide counter fields

You can also hide all extra fields.

Here you can see the most simple output possible:

pipeline:

  # Set the CSV in the body
  - set.body:
      value: |
        "firstName","lastName","age"
        "Max","Smith","38"
        "Susann","Mayr Wan","44"

  - transform.csv.json:
      showHeadersField: false
      showColumnsCountField: false
      showRowsCountField: false

The output will look like this:

{
  "rows": [
    ["firstName","lastName","age"],
    ["Max","Smith","38"],
    ["Susann","Mayr Wan","44"]
  ]
}

Example 4: Objects output format

In some cases it is required, to have each row output as a JSON object with the header names as key.

To do so, you need to set the parameter rowsFormat to objects, then the JSON output will contain an array of JSON objects:

pipeline:

  # Set the CSV in the body
  - set.body:
      value: |
        "firstName","lastName","age"
        "Max","Smith","38"
        "Susann","Mayr Wan","44"

  - transform.csv.json:
      rowsFormat: "objects" # Can be "objects" or "arrays" (default).

The output will look like this:

{
  "columnsCount": 3,
  "rowsCount": 2,
  "headers": ["firstName","lastName","age"],
  "rows": [
    {
      "firstName": "Max",
      "lastName": "Smith",
      "age": "38"
    },
    {
      "firstName": "Susann",
      "lastName": "Mayr Wan",
      "age": "44"
    }
  ]
}

Note that this output format creates a much bigger JSON document. So if possible, you should prefer to work with the default rows format arrays.

Example 5: Set CSV as input param

Instead of reading the CSV from the body, you can also pass it as input param to the command:

pipeline:

  - transform.csv.json:
      input: |
        "firstName","lastName","age"
        "Max","Smith","38"
        "Susann","Mayr Wan","44"

The output will look like this:

{
  "columnsCount": 3,
  "rowsCount": 2,
  "headers": ["firstName","lastName","age"],
  "rows": [
    ["Max","Smith","38"],
    ["Susann","Mayr Wan","44"]
  ]
}

Example 6: List as input

In this example you can see that it is also possible to define a simple list as input.

pipeline:

  - transform.csv.json:
      hasHeadersLine: false
      input: |
        row1
        row2
        row3

The output will look like this:

{
  "columnsCount": 1,
  "rowsCount": 3,
  "rows": [
    ["row1"],
    ["row2"],
    ["row3"]
  ]
}

Transform JSON → CSV

For transformation from a given JSON document to a CSV document, a conversion from JSON to a text structure is required. Since the conversion rules from a given JSON to CSV could be vary a lot, a template based approach is often a good idea here.

Below you can find an example to convert a given JSON to CSV using the FreeMarker Transformer command:

# The JSON to be converted (can be any structure)
body: {
        "columnsCount": 3,
        "rowsCount": 2,
        "headers": ["firstName", "lastName", "age"],
        "rows": [
            {
                "firstName": "Max",
                "lastName": "Smith",
                "age": "38"
            },
            {
                "firstName": "Susann",
                "lastName": "Mayr Wan",
                "age": "44"
            }
        ]
    }

pipeline:
    - transform.ftl:
        # The conversion rule from JSON -> CSV
        template: |
            "${body.headers[0]}", "${body.headers[1]}", "${body.headers[2]}"
            <#list body.rows as person>
            "${person.firstName}",  "${person.lastName}", "${person.age}"
            </#list>

This example will finally output a CSV like this:

"firstName", "lastName", "age"
"Max", "Smith", "38"
"Susann", "Mayr Wan", "44"

  • No labels