Skip to content

Converting a CSV Message to JSON with a Template

When a CSV row needs to be reshaped into JSON with a fixed structure, a template is the most readable option: hold the JSON body in a System Variable (or inline in the script), embed node tags for each column, and render it with qie.evaluateTemplate. QIE resolves each {ColumnName} tag against the source CSV message and returns the rendered JSON as a string.

The field-by-field alternative, which iterates rows in a mapping script and uses setNode per column, is better when downstream logic needs each row as a JavaScript object, and is also the recommended approach when the output is a JSON array with one element per CSV row.

Storing the template in a System Variable

Create a Text-type System Variable named patientTemplate with this value:

{
    "mrn":       "{MRN}",
    "name": {
        "family":  "{LastName}",
        "given":   "{FirstName}"
    },
    "birthDate": "{DOB}",
    "gender":    "{Gender}"
}

Each tag names a column from the CSV source. Unindexed tags resolve to the first data row (row 1). The header row itself is never targeted by node paths (see CSV Node Path Syntax). Use {ColumnName[N]} if you need a specific row.

In a mapping node's Custom script, evaluate the template and assign the result to the outbound message:

var rendered = qie.evaluateTemplate(qie.getVariable('patientTemplate'));
message.setNode('/', rendered);

Inline template

For a one-off shape that does not need to be reused, keep the template inside the script itself:

var template =
    '{' +
    '  "mrn":     "{MRN}",' +
    '  "name": {' +
    '    "family": "{LastName}",' +
    '    "given":  "{FirstName}"' +
    '  }' +
    '}';

message.setNode('/', qie.evaluateTemplate(template));

System-variable storage is preferable when the same template will be reused, exported with a package, or edited by someone who does not want to touch the script.

One JSON element per CSV row

qie.evaluateTemplate renders a single result and does not iterate. To emit one JSON object per CSV row, iterate the rows in the script and either evaluate a template per row (rebuilding each iteration's tags with the row index) or build the JSON directly with the field-by-field approach in Reading a CSV Message.

Escaping literal braces

The { and } characters are reserved node-tag delimiters wherever qie.evaluateTemplate runs. A JSON body that legitimately contains a brace not tied to a substitution needs its brace written as an HTML entity. See Escaping Literal Curly Braces.