When creating event-driven or API flows in IBM App Connect, you can use comma-separated values (CSV) that are stored in a file or within some other content to provide input to an action in the flow. The CSV content 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 (“”).

To enable you to map CSV data returned by an event or action to a subsequent action in a flow, App Connect provides a built-in CSV parser, which transforms a CSV data string into a JSON object. 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. Once generated, these keys will be included in the list of fields that are available for mapping when you click Insert a reference Insert a reference 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.

Box Retrieve file action fields

To make the CSV data available for mapping in a subsequent action, you’ll need to insert a CSV parser node before adding the action, and then use the 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 (+), go to the Toolbox tab, and then click CSV parser.

    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 reference Insert a reference 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.

    CSV Input field

  3. Expand the CSV Parsing Options and Data Schema section and 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.

    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.

    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": {
            "type": "string",
            "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

SFTP, CSV parser, and IBM Db2 flow image

Create an event driven flow that creates records in an IBM Db2 database by using the parsed contents of a new CSV file in an SFTP server

Learn how to use App Connect Designer to create an event-driven flow that parses the contents of a new comma-separated values (CSV) file in an SFTP server, creates records in an IBM Db2 database from the parsed contents, and then renames the file to indicate it’s been processed.



Image of a CSV parser video

Watch a demo video that illustrates how to use the CSV parser

Learn how to create an API flow in App Connect that inserts contacts into Salesforce from a CSV file.

Join The Discussion

Your email address will not be published. Required fields are marked *