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.

Sections in this page:

Defining the structure and content of your CSV output
Example

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.

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.

In the field where you want to compose your CSV content, click abc and then select CSV.
Figure 2. Select the CSV option
(Click image to view full size.)
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
(Click image to view full size.)
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
(Click image to view full size.)
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 . 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
  (Click image to view full size.)
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
  (Click image to view full size.)
  
  Here's an example of the completed mappings.
  
  Figure 7. Completed CSV array fields
  (Click image to view full size.)
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
  (Click image to view full size.)
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.
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.
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
(Click image to view full size.)

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.

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

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.

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

Examples

At regular intervals extract Salesforce lead details and upload Box file with CSV output

Use this template to create an event-driven flow that gathers 10 leads from Salesforce, uses their names and email addresses to build a CSV file, then outputs the file to Box. The flow triggers once per day at 00:00 UTC.

In Designer, open the Templates gallery and search for the template title or csv. To refer to the template instructions while editing the flow or without using Designer, open the github page (opens in a new window).

Welcome guide setup 1 - Add products to Salesforce

Use this template in conjuction with the templates for 'Welcome guide part 3 - Create a CSV file in Box of the products in Salesforce' and 'Welcome guide part 4 - Flower order API' to set up Salesforce products for the welcome guide templates.

In Designer, open the Templates gallery and search for the template title or CSV. To refer to the template instructions while editing the flow or without using Designer, open the github page (opens in a new window).

To read more about the welcome guide, or to watch the videos associated with welcome guide templates, see Introduction: Using templates to create flows from the welcome guide.

Welcome guide part 3 - Create a CSV file in Box of the products in Salesforce

This template contains the flow created in the 'Welcome to IBM App Connect [Part 3 of 4]: Visually map and transform your data' video. Use it to retrieve a list of products for a particular product family from Salesforce, and use the products to generate a CSV file in Box on a schedule.

To read more about the welcome guide, or to watch the videos associated with welcome guide templates, see Introduction: Using templates to create flows from the welcome guide.