Using the CSV parser to parse CSV text into a JSON object

In a flow, an event or action can return comma-separated values (CSV) from a file or some other content. Add a CSV parser node to a flow to transform the CSV data string into a JSON object. You can then use the JSON object to map the CSV data to subsequent actions in the flow.

What to consider first

The CSV data that an event or action returns must be in plain text and can contain one or more data records in a tabular format. A comma (,) is the only supported delimiter for the data fields. As such, if the value in a field includes a comma, that value must be enclosed in double quotation marks ("").

The schema that the CSV parser generates is formatted as a standard set of comma-separated key and value pairs that are surrounded by curly braces {}. The keys are defined as strings, and their values can be one of the following valid JSON data types: string, number, object, array, Boolean, or null. When generated, these keys are included in the list of fields that are available for mapping when you click Insert a mapping in a subsequent action.

Defining the data to be parsed and generating a valid JSON schema

The following example depicts a section of a multi-node flow that uses a Box Retrieve file action to specify the identifier of a CSV file whose contents you want to retrieve and parse.

Box Retrieve file action fields — Figure 1. Retrieve file action fields

To make the CSV data available for mapping in a subsequent action, insert a CSV parser node before the action, then use the CSV parser node to define the data that you want to parse, and generate a valid JSON schema.

To insert and configure the CSV parser node, complete the following steps.

Click the (+) icon, go to the Toolbox tab, and then click CSV parser.
Figure 2. Selecting the CSV parser from the Toolbox tab
Use the CSV Input field to indicate which data from the previous action or event is parsed. You can map to fields that are shown in the list of available inputs (which are accessible by clicking Insert a mapping ). Typically, this data is the contents of a CSV file or some other CSV content in a body of text. The following example shows how to map data to the contents of a file in Box.
Figure 3. CSV Input field
Expand the CSV Parsing Options and Data Schema section and then use the Example CSV field to specify plain-text data that represents a sample of your CSV content.
The following example shows sample data from a CSV file that was exported from an IBM API Connect® Management server to Box. The CSV file contains API event data that is associated with one of the Analytics dashboards. In this example, you want to parse this data so that you can use Gmail or Slack to send pertinent analytics information about your APIs or applications to your executives.

Figure 4. Example CSV field

You can select and copy one or more rows from your CSV content, and paste them into the Example CSV field. If the CSV content contains column headers that you want to define as keys in the JSON schema that App Connect generates, be sure to include the header row in your selection. Only the first row is used to analyze the CSV format of the schema. The Example CSV field accepts a maximum of 16,000 characters.
If you included a CSV header in the sample CSV, select the First row of the CSV data is a header checkbox to indicate that the column headers must be parsed as keys in the generated schema.
Figure 5. The First row of the CSV data is a header checkbox
To convert the sample CSV into a JSON object, click Generate Schema. You can see the result in the JSON Schema field.
- If you selected the First row of the CSV data is a header checkbox, the defined column headers are parsed as keys that you can map to later.
- If you did not select this checkbox, a generic series of keys that are labeled field1, field2, ..., fieldN are generated for use. You can overwrite these generic keys to specify alternative header strings if necessary.
You can alternatively bypass the schema generation step and enter a valid schema directly into the JSON Schema field. You can also modify the data types of the keys by updating the values of the type attributes for the properties. For example, you can change "type": "string" (shown in the following example) to "type": "number".
```
    ...
    "properties": {
      "bytes_received": {
        <strong>"type": "string"</strong>,
        "minLength": 1
      },
    ...
```

Ways of processing your parsed data

The output from a CSV parser node is an array. So, to use this output in a subsequent node in the flow, you typically include a For each node after the CSV parser node to iterate through the array. Alternatively, you can manipulate the array data by using JSONata.