Composing CSV output for an action in a flow

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.

Figure 1. Select the CSV option
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.

  • 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.
    Figure 2. Select the CSV option
    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.
    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.

    Figure 3. Sample CSV data to use for generating a schema
    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.
    Figure 4. Array representation of CSV fields
    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 mapping 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}
      Figure 5. Selecting a value for the Items parent field in the CSV array
      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.
      Figure 6. Selecting values for the child fields in the CSV array
      Selecting values for the child fields in the CSV array

      Here's an example of the completed mappings.

      Figure 7. Completed CSV array fields
      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.
      Figure 8. CSV output settings
      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 Always enclose fields in double quotes. If you leave this option unselected, 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).
    Figure 9. Completed fields for creating a CSV file
    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.

Figure 10. Generated CSV file
Generated CSV file

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

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

The following example shows a CSV file that contains values that include a comma. The Always enclose fields in double quotes was not selected on the CSV output settings panel, but the data values that include a comma are automatically enclosed in double quotation marks to prevent the comma from being interpreted as a delimiter.

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