- 3 minutes to read

Formula - csv

The csv Formula function returns the values of one column of CSV or other delimited text, after skipping header rows and comment lines.

Syntax 

csv(headerRowsToSkip, 'delimiter', columnIndex, 'commentSymbol', content)

Parameters

Parameter Required Description
headerRowsToSkip Yes Number of rows to skip at the top, such as a header row. A number, without quotes.
'delimiter' Yes The character that separates columns, such as ',' or ';'.
columnIndex Yes The column to return, zero-based: 0 is the first column, 1 the second. A number, without quotes.
'commentSymbol' Yes Lines that start with this character are ignored, such as '#'.
content Yes The delimited text: body, context, or the result of another function (see Formula language rules).

Returns

One value per remaining row: the value in column columnIndex, with surrounding double quotes removed. Duplicates are collapsed. Rows that are skipped or start with the comment symbol return nothing.

Examples

Example 1: Return the second column of the message body

Input 

"face","suit","value"
"king","spades",13
"queen","spades2",12
#"jack","spades3",11
"ten","spades4",10
"nine","spades5",9
#"twenty","clown",10
"eight","spades6",8
"seven","spades7",7

Formula 

csv(2, ',', 1, '#', body)

Result 

spades2
spades4
spades5
spades6
spades7

Formula csv expression returning five suit values from a CSV message body in the Test Expression tab
The first two rows (face and king) are skipped, the two # lines are ignored, and column 1 (the second column) is returned.

Example 2: Highest value in a context property

Input

The Message Context key Ids has this value:

"face","suit","value"
"king","spades","13"
"queen","spades2","12"
#"jack","spades3","666"
"ten","spades4","10"

Formula 

max(csv(1, ',', 2, '#', context('Ids')))

Result 

13

Formula max of csv on the Ids context property returning 13 in the Test Expression tab
csv returns 13, 12, and 10 from the third column; the commented 666 row is ignored, so max returns 13.

Example 3: CSV embedded in a JSON field

Input 

{
    "name": "JSONFile",
    "description": "TestFile",
    "content": "\"face\",\"suit\",\"value\"\n\"jack\",\"spades3\",11\n\"ten\",\"spades4\",10\n\"nine\",\"spades5\",9\n\"eight\",\"spades6\",8\n\"seven\",\"spades7\",7"
}

Formula 

csv(4, ',', 1, '#', jsonPath('$.content', body))

Result 

spades6
spades7

Formula csv over jsonPath content returning spades6 and spades7 in the Test Expression tab
jsonPath returns the CSV text of the content field; csv skips four rows (header, jack, ten, nine) and returns the second column of the rest.

Notes

  • Rows and columns count differently: headerRowsToSkip is a count of rows, columnIndex is zero-based.
  • For fixed-width records, or when you prefer a plugin without a formula, see Choose a plugin.

Next Steps