i4connected Knowledgebase

The CSV Adapter

Abstract

Learn more about the i4connected CSV Adapter allowing you to load measurements from CSV files and assign them to Signal values.

This article describes the required XML structure and UI settings of the CSV Adapter configuration file. The CSV adapter is able to load measurements from CSV files located on disk storage or FTP/HTTP storage. The measurements are loaded and assigned to signal values using the adapter settings which contain a series of sources, parses and column mappings.

By adding a new CSV Device, the user is required to fill in the CSV Adapter specific settings.

Ad_CSV_Device_panel.jpg

The Add CSV Device panel

By clicking on the field Add CSV adapter name the Select CSV Source panel is opened. In this view the user can select an existing CSV source or add a new one.

Select_CSV_Source_panel.jpg

The Select CSV Source panel

When the user clicks on the Add button the Add CSV Source panel is opened. In order to add a new CSV source the user needs to fill in the following settings:

add_CSV_source.jpg

The Add CSV source panel

  • Name - The name of the CSV Adapter source.

  • Address - The location of the directory where the CSV data is collected.

  • Poll interval (ms) - Duration in milliseconds between subsequent polls.

  • User name - Optional for FTP / HTTP connections.

  • Password - Optional for FTP / HTTP connections.

  • Use file name pattern for sorting - If enabled, the file name pattern will be used for sorting.

  • File date/time pattern - If specified, the parsers will read the date/time from the file name, otherwise date / time will be retrieved from the file’s last modified timestamp; this is used for sorting the CSV files correctly before reading the data.

  • Search sub-folders - If enabled, the sub-folders will be searched for CSV data

  • Time zone - The time zone used by the new CSV Adapter. By default, the Time Zone is predefined for the currently logged in user.

  • Trace level - Trace levels determine which events the trace provider generates. The user can select the desired trace level from a drop-down list where the following predefined value are available: Trace, Debug, Info, Warn, Error, Fatal and Off.

  • Enabled - If the setting is turned on, the Adapter is enabled and functional.

The CSV Adapter configuration file

The configuration must follow the simple but logical structure, detailed below.

XML configuration example structure:

<folder>
	<measurementParser>
		<column/>
		<column/>
	</measurementParser>
	<measurementParser>
		<column/>
	</measurementParser>
</folder>
The <column> element

The <column> element defines how the values inside the CSV file will be parsed. The column element defines the mappings used for linking the CSV values with the i4connected signal values.

This element is highly configurable using its attributes and must be configured based on the structure of the CSV data.

The following attributes define the <column> element:

Element

Data Type

Description

device

string

The unique name of the device to which the data belongs to. Specified as an exception from the <measurementParser> device attribute. If not specified, the <measurementParser> device attribute will be used.

name

string

The name of the device values column needed to be parsed. Excludes the index attribute.

index

int

The index of the device values column needed to be parsed. The index number always starts at 0. Excludes the name attribute.

signal

string

The name of the signal from the device. By default, the <column> name is used as signal name. If the <column> index is used, the signal attribute muse be specified.

type

string

The type of value. All the available value types can be found in the link below:

TypeCode Enumeration

Note

The attributes emphasised using bold characters are required. The other attributes are optional or required only in specific situations. If a non-required attribute is not defined, the parser will fall back to its default value.

Example:

<folder 
	address="ftp://myserver/myfiles" 
	userName="root" 
	password="12345" 
	pollInterval="600000">

	<measurementParser 
		dateColumn="Date" 
		dateFormat="ddMMyy" 
		decimalSeparator="," 
		thousandsSeparator=" " 
		device="Building 1.Device 1" 
		separator="||" 
		searchString="Temp*.csv" >

		<column 
			device="Building 1.Device 2" 
			index="2" 
			signal="Temperature"
			type="Double" />

	</measurementParser>

</folder>
The <folder> element

The root element <folder> is used to define the directory where the CSV data is located. The <folder> element is unique per configuration XML.

The following attributes define the <folder> element:

Element

Data Type

Description

address

string

The location of the directory where the CSV data is collected. The directory can be accessed using one of the following methods:

  • FTP: ftp://MyFTPServer/MyFolder/MyData/

  • HTTP: http://MyServer/MyFolder/MyData/

  • Local (complete): c:\MyFolder\MyData\

  • Local (relative): \\MyFolder\MyData\

username

string

If required, only when using FTP or Local address. The user name required to access the directory.

password

string

If required, only when using FTP or Local address. The password required to access the directory.

pollInterval

double

The interval of time, defined in milliseconds, for scanning the folder for new CSV data.

fileTimePattern

date/time

Specified if the date/time columns form the CSV file cannot be used. Represents the date/time format of the date or/and time from the name of the CSV file. If specified, the parser will try to recognize the pattern in the name of the CSV file.

Examples: yyMMddHHmmss, yy-MM-dd-HH-mm-ss.

searchSubFolders

boolean

If enabled, the sub-folders from the root folder will be searched for CSV data. Default value: false.

Note

The attributes emphasised using bold characters are required. The other attributes are optional or required only in specific situations. If a non-required attribute is not defined, the parser will fall back to its default value.

Example:

<folder 
	address="ftp://myserver/myfiles" 
	userName="root" 
	password="12345" 
	pollInterval="600000">
	
</folder>
The <measurementParser> element

The <measurementParser> element defines how the CSV data will be parsed. This element is highly configurable using its attributes and must be configured based on the structure of the CSV data. The measurement parsers are defined at the adapter level after defining a source. Parsers describe the files that contain the measurements and how they must be interpreted.

The following attributes define the <measurementParser> element:

Element

Data Type

Description

dateColumn

string

The name of the Date column from the CSV file. Excludes dateIndex. dateColumn or dateIndex are required.

dateIndex

int

The index number of the Date column from the CSV file. The index number always starts at 0. Excludes dateColumn. dateIndex or dateColumn are required.

dateFormat

date/time

The format of the date to be parsed. By default it uses the system format. Check the link below for additional date formats:

Custom Date and Time Format Strings

timeColumn

string

The name of the Time column from the CSV file. Excludes the timeIndex. Not required if the time is written in the Date column (date and time).

timeIndex

int

The index number of the Time column from the CSV file. The index number always starts at 0. Excludes the timeColumn. Not required if the time is written in the Date column (date and time).

timeFormat

date/time

The format of the time to be parsed. Uses the TimeSpan format.

The .NET Framework does not define any grammar for separators in time intervals. Thus, the separators between days and hours, hours and minutes, etc, must be treated as character literals inside a format string.

When using the TimeSpan format, any unescaped character is interpreted as a custom format specifier, which, in most cases, throws a FormatException. In order to avoid this when including literal characters in a format string, one of the following solutions must be taken into consideration:

  1. Enclosing the literal character in single quotation marks ( ' single quotation marks are literal string delimiters) . For example: 13:15 would be obtained from hh':'mm.

  2. Preceding the literal character with a backslash (\). The backslash is interpreted as an escape character. Note that the backslash also needs to be escaped to obtain the correct custom format, so double backslashes are needed. For example: 13:15 would be obtained from hh\\:mm.

  3. Using standard TimeSpan format strings. For example: 13:15 would be obtained from g (general short format).

Check the link below for additional information on custom and standard TimeSpan format strings:

decimalSeparator

char

The decimal separator. By default, the dot (.) is used. Any other custom decimal separator must be specified here.

thousandsSeparator

char

The thousands separator. By default, the comma (,) is used. Any other custom thousands separator must be specified here.

delimiter

char

The character used to delimit the values. By default, the single quote (') is used.

device

string

The unique name of the device. If more devices must be specified, this attribute can act like a general rule with the exceptions mentioned in the <column> device attribute.

This attribute can be left out if the <column> device attribute is specified for each column.

hasHeaders

bool

The Boolean value that indicates if the CSV table has headers or not. By default, the value is true. The header represents the first row of the table.

separator

string

Separates the columns. By default, the comma (,) is used.

searchString

string

Allows the user to find specific files in the directory. Uses asterisk to define "any". By default, the searchString is set to *.*.

Example: To parse all the CSV files with the name starting with Temp, the searchString would be Temp*.csv.

fileTimePattern

date/time

Specified if the date/time columns form the CSV file cannot be used. Represents the date/time format of the date or/and time from the name of the CSV file. If specified, the parser will try to recognize the pattern in the name of the CSV file.

Examples: yyMMddHHmmss, yy-MM-dd-HH-mm-ss.

Encoding

int

Code page identifier representing the file encoding. The available values can be found here: MSDN: Code Page Identifiers. The default value is 1252 - ANSI Latin 1; Western European (Windows).

Note

The attributes emphasised using bold characters are required. The other attributes are optional or required only in specific situations. If a non-required attribute is not defined, the parser will fall back to its default value.

Example:

<folder 
	address="ftp://myserver/myfiles" 
	userName="root" 
	password="12345" 
	pollInterval="600000">

	<measurementParser 
		dateColumn="Date" 
		dateFormat="ddMMyy" 
		decimalSeparator="," 
		thousandsSeparator=" " 
		device="Building 1.Device 1" 
		separator="||" 
		searchString="Temp*.csv" >

	</measurementParser>
	
</folder>
The <parser> element

The <parser> element defined the parsers used for defining the CSV data. This element is highly configurable using its attributes and must be configured based on the structure of the CSV data.

The following attributes defined the <parser> element:

Element

Data Type

Description

DateIndex

Int.

Gets or sets the index of the date.

TimeIndex

Int.

Gets or sets the index of the time.

Delimiter

Char.

Gets or sets the delimiter.

Separator

String

Gets or sets the separator.

SearchString

String

Gets or sets the search string.

HasHeaders

Bool.

Gets or sets a value indicating whether this instance has headers.

Encoding

Int.

Gets or sets the encoding.

protected Parser()
        {
            DateIndex = DefaultIndex;
            TimeIndex = DefaultIndex;
            Delimiter = DefaultDelimiter;
            Separator = DefaultSeparator;
            SearchString = DefaultSearchString;
            HasHeaders = DefaultHasHeaders;
            Encoding = DefaultEncoding;
        }