You can compose comma separated values (CSV) output within the node for an action by using data returned from a previous event or action in a flow. Although you can use this output for any purpose, a more common use would be for creating CSV files in file management systems such as Box or SFTP, or in CRM applications such as Salesforce, which support the creation of attachments. The CSV output is constructed “in memory” and gets written to the target application when you run the flow.

To construct the CSV output, you provide a sample of the required CSV structure and then define which line break character to use, whether to add a header, and whether to wrap each value in double quotation marks. You also need to specify which data should be used to populate the fields in each column. The resulting file or output displays plain-text data in a tabular format, with an optional line of column headers as the first row. The values in each row are delimited by a comma (,) and each row of data (other than the last row) is delimited by a line break.

Defining the structure and content of your CSV output

You can define the structure and content of CSV output from any string field (identified by the abc notation) in an action node.

Select the CSV option

As a simple example, let’s suppose you want to extract the daily sales data recorded in a database to a CSV file in Box for further use. In the flow, you’d first need to add an IBM Db2 “retrieve records” action for the data to be retrieved, and then add a Box action to create the CSV file in a specific folder and with a .csv file extension. Within the Box node, you can define a structure and format for the CSV content (for example, the number of columns, column headers if required, the line break character), and specify the content for each column.

  • Example: Database table and columns to retrieve data from

    The following example shows the four columns in the SALES database table that data is to be retrieved from.

    Image of the four columns in the SALES database table

  1. In the field where you want to compose your CSV content, click abc and then select CSV.

    Select the CSV option

  2. From the “Generate CSV schema from sample CSV data” panel, enter one or more rows of data in CSV format within the Sample CSV data field. This data should depict sample content for the proposed CSV file.

    Points to note:

    • Only the first row of sample data is used to define the CSV structure of the schema.
    • The first row of a CSV file typically contains column headers, so if you’d like your CSV file to contain specific column headers, add a comma-separated list of those column titles as the first row, and then ensure that the First row of the CSV data is a header check box is selected. (You’ll need to also ensure that your CSV output settings are configured to include a header row, as described in a later step.)
    • If you do not require specific column headers in your CSV file, clear the First row of the CSV data is a header check box.

    The following image shows sample column headers that are representative of the database table columns to extract data from.

    Sample CSV data to use for generating a schema

  3. Click Generate schema to display a field-based representation of the CSV structure. If you selected the First row of the CSV data is a header check box, you’ll see the column headers from the sample data as field names. If you cleared the check box, you’ll see a generic set of field names labelled field1, field2, …, fieldN for the N columns of sample data that you specified.

    Array representation of CSV fields

  4. Use the Items parent field and its child fields to define the CSV data content.
    1. In the Items field, specify a mapping that identifies an array of objects that were returned by a previous node, and which should form the content of your CSV file.

      For example, to map to an array of Sales records that was returned by the previous IBM Db2 retrieve action, click within the Items field and click the Insert a reference icon Insert a reference icon. Then from the list of available inputs, expand IBM Db2 / Retrieve SALES records and select DB2ADMIN2_Sales. This value resolves to an array of of comma-separated key/value pairs that are surrounded by curly braces {}. The keys are defined as strings, and their values can be of different data types such as string, number, or null. Example:
      {""key1"":""value1"",""key2"":""value2"",""key3"":""value3"",...,""keyN"":valueN}.

      Selecting a value for the Items parent field in the CSV array

    2. In the child fields, map to the data for each of the columns in the CSV file. For these entries, you can map to individual fields in the Parent mapping item section within the list of available inputs.

      Selecting values for the child fields in the CSV array

      Here’s an example of the completed mappings.

      Completed CSV array fields

  5. Now that you’ve specified the content of the CSV file, you can define settings for the CSV output by clicking Edit CSV output settings to open the “CSV output settings” panel.
    1. Specify which end of record character should depict the end of a row. Valid options to support various operating systems are Line feed (the default), Carriage return, and Carriage return and line feed.

      CSV output settings

    2. If you’d like to use your specified sample headers (from the “Generate CSV schema from sample CSV data” panel) as the column headers in the file, ensure that the Include header row check box is selected. To omit headers from the CSV file, clear this check box.

      If you didn’t set the first row of your sample as a header, but then select the Include header row check box, the column headers in the CSV file will be assigned the following generic labels: field1, field2, ..., fieldN.

    3. To always enclose the data values for each column within double quotation marks, select the Always enclose fields in double quotes check box. If left clear, a data value is enclosed in double quotation marks by default only if that value contains a double quotation character or comma.
    4. Click Apply to save your changes.
  6. To change the structure or header labels of the CSV, click Regenerate schema. (Note that you will lose any mappings that are currently defined.) In the “Generate CSV schema from sample CSV data” panel, specify new sample CSV data and then generate the new schema.
  7. If you are sending the CSV output to a file, be sure to indicate that the file type should be text (rather than binary).

    Completed fields for creating a CSV file

When you run the flow, you should obtain a CSV file with the specified structure and format. The following example shows a CSV file with the default output settings for LF and no enclosing quotation marks.

Generated CSV file

This example shows a CSV file with output settings for CRLF and double quotation marks for each value.

Generated CSV file with output settings for CRLF and double quotation marks

The following example shows a CSV file which contains values that include a comma. Although the Always enclose fields in double quotes check box was left clear in the “CSV output settings” panel, the data values that include a comma are automatically enclosed in double quotation marks to prevent the comma from being interpreted as a delimiter.

Generated CSV file with output settings for LF and default quotation marks for data values with a comma

Example

For a use-case example of the CSV compose function, see the blog post Constructing CSV files in IBM App Connect.

Join The Discussion

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