CSV - JSON Transformation
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
Transform 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.jsonThe 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:
The output will look like this:
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:
The output will look like this:
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:
The output will look like this:
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:
The output will look like this:
Example 6: List as input
In this example you can see that it is also possible to define a simple list as input.
The output will look like this:
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 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:
This example will finally output a CSV like this: