Skip to content

Reading a CSV Message

CSV data typically arrives from a DB Query source, a File source with CSV format, or a mapping script that called qie.parseCSVString on an inbound string. Once parsed, QIE exposes both node-path and row/column APIs for reading the data.

The APIs at a glance

Call Returns
message.getNode('LastName') The value of column LastName in the first data row.
message.getNode('LastName', 2) The value of column LastName in row 2 (1-based; data rows only when the CSV has a header).
message.getNode('3') The value of column 3 in the first data row (columns are 1-based).
message.getRowCount() Number of data rows (excludes the header row when First Row Is Header is enabled).
message.getColCount() Number of columns.
message.getHeaders() A List<String> of the header column names (empty when the CSV has no header row).
message.getRows() A List<List<String>> of every data row.

The full node-path syntax (column-name, column-index, /, *, [N], predicates like [Name=Smith]) is documented in CSV Node Path Syntax.

Iterate every row

Loop over getRowCount() and read columns by name with the instance argument:

var count = message.getRowCount();
for (var row = 1; row <= count; row++) {
    var mrn      = message.getNode('MRN', row);
    var lastName = message.getNode('LastName', row);
    qie.debug('row ' + row + ': ' + mrn + ' / ' + lastName);
    // …do something with the row
}

Build a JS object per row

For code that needs each row as a plain JS object keyed by header column name, combine getHeaders() and getRows():

var headers = message.getHeaders();
var rows    = message.getRows();
var records = [];
for (var i = 0; i < rows.size(); i++) {
    var record = {};
    for (var j = 0; j < headers.size(); j++) {
        record[headers.get(j)] = rows.get(i).get(j);
    }
    records.push(record);
}
// records is now [ { MRN: '...', LastName: '...', ... }, ... ]

Look up a value from a CSV System Variable

For static reference tables (unit conversions, code cross-references, small allow/deny lists), a CSV System Variable is faster to write and cheaper to query than parsing a CSV message each time. qie.doTableLookup(value, notFoundValue, tableName, sourceColumn, targetColumn) returns the value from targetColumn in the first row whose sourceColumn matches value, or notFoundValue when no match is found.

// Look up an age from a table variable called `myVariableTable` with columns name/age/state
var jaimesAge = qie.doTableLookup(
    'Jaime',           // value to search for
    '',                // default when no match
    'myVariableTable', // the CSV System Variable name
    'name',            // source column
    'age'              // target column
);

Multi-column matches are supported by passing arrays: qie.doTableLookup(['Tom', 'NY'], '', 'myVariableTable', ['name', 'state'], 'age'). See the Code Wizard for the full signature and the System Variables page for defining the variable.