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 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"
Add Comment