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. In the flow, you can add a CSV parser node to transform the CSV data string into a JSON object, which you can use to map the CSV data to subsequent actions in the flow.

What should I consider first?

The CSV data to be returned by an event or action 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/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 will be included in the list of fields that are available for mapping when you click Insert a mapping Insert a mapping icon in a subsequent action.

Defining the data to be parsed and generating 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.

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

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

You can insert and configure the CSV parser node as follows:

  1. Click the (+) icon, go to the Toolbox tab, and then click CSV parser.
    Figure 2. Selecting the CSV parser from the Toolbox tab
    Selecting the CSV parser from the Toolbox tab
  2. Use the CSV Input field to indicate which data from the previous action or event should be parsed. You can map to fields that are shown in the list of available inputs (generally accessible by clicking Insert a mapping Insert a mapping icon). Typically, this would be the contents of a CSV file or some other CSV content in a body of text. In the following example, we are mapping to the contents of a file in Box.
    Figure 3. CSV Input field
    CSV Input field
  3. 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), and which contains API event data that is associated with one of the Analytics dashboards. Let's assume that you want to parse this data so you can use Gmail or Slack to send pertinent analytics information about your APIs or apps to your executives.

    Figure 4. Example CSV field
    Example CSV field
    Considerations for completing the Example CSV field:
    • You can select and copy one or more rows from your CSV content, and paste them into this 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. Note that 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.
  4. If you've included a CSV header in the sample CSV, select the First row of the CSV data is a header check box to indicate that the column headers should be parsed as keys in the generated schema.
    Figure 5. First row of the CSV data is a header check box
    First row of the CSV data is a header check box
  5. To convert the sample CSV into a JSON object, click Generate Schema. You can see the result in the JSON Schema field.
    Flow with a CSV Parser node: generated JSON schema
    • If you selected the First row of the CSV data is a header check box, the defined column headers are parsed as keys that you can map to later.
    • If you did not select this check box, a generic series of keys labelled field1, field2, ..., fieldN are generated for use. You can overwrite these generic keys to specify alternative header strings if required.

    You can alternatively bypass the schema generation step and simply enter 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'd typically include a For each node after the CSV parser node to iterate through the array, or you could manipulate the array data by using JSONata. To see an example of how to use a CSV parser node with a For each loop, see the Examples section below.

Examples