Formula - CSV

Use the Nodinite Formula CSV-method on Content like a comma-, or semicolon-separated messages (e.g. CSV-files).

The Content usually has the following characteristics:

• Is string based
• Has one or more lines
• Has a specific character to separate values (a delimiter).
• ';', ',', ... You can use any single character
• Each line of the file is a data record. Each record consists of one or more fields, separated by the delimiter
• Can include commented lines (to be skipped)

Info

Use this plugin to extract values from messages with a small payload.

Quick example

Let's get you started with the following example: For other and more advanced examples, please scroll down on this page to the Examples section.

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

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

Features

• Read and extract data from CSV.
• Extracts single value only from Content (Ignore duplicate).
• Can be applied to any valid Content that returns the delimited data; review the Formula user guide for the complete list of functions.

1. SkipRowsCount - Specify the number of rows to skip at the beginning of the content. Use it to remove the header row and/or skip the first n rows (User-defined).

2. Delimiter - The character to use separating the fields

• ColumnIndex ','Delimiter','ColumnIndex','Quote',body)`

3. From Message Context: csv('SkipRowsCount','Delimiter','ColumnIndex','Quote', Context('id'))

4. Quote - (a character, usually '"' or "#')

   The quote character used to escape fields

5. Result from previous Formula operation csv('SkipRowsCount', 'Delimiter', 'ColumnIndex','Quote', SomeOtherNestedFormulaFunction(Content))

Important

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

How to use

To use the CSV function, the Content parameter must contain valid delimited text-based structure (otherwise, it will fail, and you will end up with a garbage result). But first and foremost, you have to configure the Formula plugin:

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

This function has the following five parameters:

Examples

Below you will find some common usage examples.

Body as Content

In this example;

• Start reading from row number "3" (1st parameter "2")
• The body content has a comma delimiter ',' (2nd parameter)
• Read Field number "1" (3rd parameter - Zero based field index)
• Skip rows with lines that starts with the "#" sign

The result is:
spades2,spades4,spades5,spades6,spades7.

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 csv content(!), with a comma "," delimiter where the key is "Ids", by using provided expression, the result is 13.

Message Context value Body

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

Expression

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

Formula Function as Content

The message body is json structure, by using the provided expression, the result is spades6, spades7:

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

Related

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?