Formula - CSV

csv('SkippedHeaderRow','Delimiter','ColumnIndex','CommentSymbol',Content) Formula function is extremely advance function for CSV structure, used to read and extract data from CSV.

Quick example

Let's get you started with the following simple demonstration example valid for this Formula plugin. For other and more advanced examples please scroll down on this page to the Examples section.

Input Expression Result

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


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

spades2
spades4
Text Data csv expression Unique values

Features

  • Read and extract data from csv.
  • Extracts single value only from Content (Ignore duplicate).
  • Can be applied on any valid Content that return csv structure data; review Formula user guide for complete list of functions.

This plugin loads the entire message into RAM, so make sure to apply this function on smaller messages only

How to use

In order to use the csv function the Content parameter should contain valid csv structure (otherwise it will fail and you will end up with garbage result). But first and foremost we have to configure the Formula plugin:

  • Select Formula as expression type plugin.
  • Write down the expression to use in the 'Expression' text area.
  • Input a proper Content as parameter to the function.

This function accepts five parameters as you can notice in syntax:

  • SkippedHeaderRow: Specify how many row to skip at the beginning of the file, when reading the csv. This can be actual header row or you like to skip the first 5 rows(User preference).

  • delimiter: Used to separate the csv data item.

  • ColumnIndex: Used to read a certain column of the csv.

  • CommentSymbol: Used to assign a certain row as comment row.

  • Content:This represents the file content itself.

Syntax

Use the csv function with any of the following parameters:

  • From Message Body: csv('SkippedHeaderRow','Delimiter','ColumnIndex','CommentSymbol',body)

  • From Message Context: csv('SkippedHeaderRow','Delimiter','ColumnIndex','CommentSymbol',context('id'))

  • Result from previous Formula operation csv('SkippedHeaderRow','Delimiter','ColumnIndex','CommentSymbol',SomeOtherNestedFormulaFunction(Content))

Examples

Below you will find different common examples for how to use the csv function.

Body as Content

The body content is normal csv content with a comma delimiter',' as its specify in the 2nd parameter ,start reading from row number "3" since the skippedHeaderRow value is "2", read column number "1" according to 3rd parameter(Zero based column index) and to skip row number 4 we use "#" as a comment symbol., by using the provided expression the result will be spades2,spades4,spades5,spades6,spades7.

csv_MessageBody

Message Body


"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"

Expression


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


Context as Content

The Message context value is normal csv content, with semicolon ";" delimiter where the key is "id", by using provided expression the result will be spades6,spades7.

csv_MessageContext

Message Context value Body


"face";"suit";"value"
"jack";"spades3";11
"ten";"spades4";10
"nine";"spades5";9
"eight";"spades6";"8"
"seven";"spades7";7

Expression


csv(4,';',1,'#',context('id'))


Formula Function as Content

The message body is json structure, by using the provided expression the result will be Hello,

csv_MessageBody

Message Body

{
	"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"
}

Expression


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


Next Step

How to Add or manage Search Fields
How to Add or manage Log Views

Expression Type Plugins are used in Search Fields
What are Search Fields?
What are Search Field Expressions?
What are Message Types?
What are Log Views?