A comma-separated values file or CSV file is a text file which stores records separated by line breaks, with individual fields in each record separated by commas. An optional header row gives field names. Example content of a CSV file might be:
With IBM App Connect you can construct a CSV-formatted string, using data from an event or action which App Connect performed. In this case, an example string would be:
"Name,Email\nSamuel Cross,firstname.lastname@example.org\nDiana Davenport,email@example.com\nShannon Smith,firstname.lastname@example.org"
When constructed, the string can then be used for any purpose, but would most commonly be output as the content of a real CSV file; for example in Google Drive, Dropbox or Box.
Constructing a CSV
For the purposes of this walkthrough, let’s say we want to set up a daily job to retrieve a list of leads from Salesforce and then output them as a CSV file to Box, to a directory named “Daily leads”. (Be sure to create that directory before we begin!)
Here’s the flow we’ll be starting with:
There are three flow nodes, which are described below.
1. Scheduler event
The Scheduler event is configured to run once per day at midnight UTC. We’ll also set it to run once immediately when the flow starts, so that we can see it in action:
2. Salesforce connector
The Salesforce connector is configured to retrieve a list of leads. For the sake of simplicity, let’s just take ten leads, with no special filtering or ordering:
3. Box connector
The Box connector is where we’ll construct our CSV file. Let’s choose the action “Create file”:
First, we’ll choose our output directory, “Daily leads”. We’ll set the file’s name to be the current date in the format YYYY-MM-DD, followed by the CSV file extension; for example, “2018-08-21.csv”. We’ll also set the type of the file to be “text”, not “binary”, since a CSV file is always a text file, not a binary file:
Now for the file’s content. Click on the format icon and select “CSV” from the menu which appears:
We see a dialog box. Here, we generate the schema for our output CSV by first pasting the content of an example CSV file. In this case we’ll paste the CSV example from the start of this blog post. We also select the option to indicate that the first row is a CSV header.
Then click “Generate schema”, and we’ll see the field structure for the generated schema:
Now that we’ve given the output CSV some structure, we can tell App Connect how to populate that structure. First, we need to specify an array of records which App Connect can iterate over. Each record will correspond to a single row in the output CSV file. In our case, the array of records will be the array of leads which we just retrieved from Salesforce.
Next we need to tell App Connect which part of each record should be used to populate each column in the row. We’ll pick the lead’s full name for the “Name” column, and the lead’s email address for the “Email” column.
Now our flow is complete, and we can start it.
What we see when we run the flow
Here’s our empty output directory on Box:
After a short while, the new CSV file appears:
We can download the CSV file:
And when we open it, the file’s text content looks like we expected:
Here we have configured our text editor to show whitespace characters, including line endings. We see that the line ending character used is a single U+0010 LINE FEED. This and other settings are configurable, as we’ll demonstrate now.
CSV output settings
To adjust CSV output settings, make sure the flow is stopped, then select the Box connector and click “Edit settings”:
Here we can see that we can configure:
- what kind of line ending is used – LF, CR or CRLF
- whether a header row is included or not
- whether to always wrap fields in double quotes or not. By default, fields will be wrapped in double quotes only when it is strictly necessary, which happens if the field contains a double quote character or comma. Turn this setting on to always use double quotes regardless.