CSV file

This function block records measured values into a CSV file. The file is stored in the project directory of the controller and can be sent by email automatically.

The block does not record on its own. A line is only written when input TR changes from 0 to a value other than 0. The point in time is therefore determined by the program, for example by a clock pulse or a time switch. The line contains the values present at the inputs at that moment; nothing is averaged and nothing is made up for if a trigger does not occur. So that all values of a line belong together, the line is written about half a second after the edge. During this time the inputs may still change, they are then recorded with their latest value. Two triggers should therefore be at least one second apart.

The file is opened for every line, extended and closed again. Nothing is buffered, so a line is on the storage medium immediately after the trigger. A power failure therefore costs at most the line that is currently being written. The price for this is that every line is a separate write access to the memory card. How often the recording takes place therefore determines the service life of the card, see storage requirement and write accesses.

The file keeps growing without limit, it is neither limited nor deleted automatically. There are two ways to control this: rotation via input RO, or a file name with placeholders for the date which creates a new file every month, for example. Without one of these measures the storage medium will fill up eventually - after that not only the recording fails, but also the transfer of projects and the creation of backups.

The block is intended for meter readings, consumption values and similar recordings in a range from minutes to hours whose result is needed as a finished file. If values are to be recorded at short intervals over long periods and shown as a chart in the visualisation, the statistic is better suited.

Important: See the notes on using the counter blocks.

Inputs
TR	Trigger	A positive edge at the input writes a new line to the CSV file.
RO	Rotation	A positive edge at the input starts a new file. The current file is renamed to "filename.csv.0", ".1" etc.
01, 02 ...	Input 1, 2 ...	The values to be recorded. Every input results in one column, the order of the columns is the order of the inputs. The number is set with the parameter "Number of inputs".
Parameters
	Number of inputs	Number of inputs and therefore of value columns, 1 to 126.
	File name	File name under which the data is saved. A unique name is preset when the block is created, this should be replaced by a meaningful name. The file name can contain placeholders for the date, see below. In this way "consumption_%Y_%m.csv" creates a separate file for every month, for example. The file is always stored in the project directory, a path cannot be specified. The characters / \ : * ? " < > \| are not allowed in a file name and are replaced by an underscore. Note: If the file name contains placeholders, the email attachment is the file the pattern matches at the moment of sending. If the file changes exactly when sending takes place - a monthly file with monthly sending, for example - a negative offset has to be set at "Rename attachment time offset" so that the file of the period that has ended is still sent.
	Rotation	Number of rotations. With a positive edge at input RO the file is renamed to "filename.csv.0", ".1" etc.; once the maximum number is reached, the oldest one is deleted. The next line starts a new file which again begins with the column headers. One file more than set here is therefore stored on the medium, which has to be taken into account for the storage requirement.
Columns: The columns are always in this order: comment, date, time, then the values of the inputs. Every column is terminated by a semicolon, so a line also ends with a semicolon.
	Comment	With this option the text from the general option Comment is inserted as the first column. This can be used, for example, to insert a meter number as a column.
	Date column	Insert a column with the current date, in the format DD.MM.YYYY.
	Time column	Insert a column with the current time, in the format HH:MM:SS.
	Column headers	Column headers can be defined here. The separator is the semicolon ";". The headers are only written into a new, still empty file. If the number of inputs is changed later, the headers of an already existing file no longer match - in this case delete the file or start a new one via input RO.
	Format	Number format of the value columns, see also number formatting. With "%d" the recording has no decimal places, the value is truncated and not rounded. The default "%f" always writes six decimal places and therefore needs unnecessarily much space, for meter readings "%.2f" or "%.3f" are usually the better choice.
	Decimal character	Decimal separator of the value columns, comma or point. Which one is correct depends on the language setting of the spreadsheet program used for further processing.
Sending by email: The CSV file can be sent by email; for this, several commonly used points in time can be selected. If the file is to be sent at a specific point in time, this can also be achieved with the function block Mail dispatch. The remaining fields are only shown once a point in time is selected. The complete file is always sent. Sending neither deletes the file nor starts a new one - without rotation every email therefore also contains all earlier lines. If an email is to contain only the new period, a positive edge has to be given at input RO shortly after sending.
	Point in time	Point in time of sending: no email, minute, hourly (on every full hour), daily (at 0:00h), weekly (Monday at 0:00h) or monthly (on the first day of the month at 0:00h).
	Address	Email address
	Subject	Subject
	Text	Email text
	Waiting time	Waiting time for sending the email in seconds. If, for example, Day is selected as the point in time, the file is sent at 0:00h plus this time. This is, for example, useful when one more line is to be recorded at 0:00h.
	Rename attachment	With this option the file can be renamed, for example to append a date. Example: log_%d_%m_%Y.csv becomes log_10_11_2023.csv. For renaming, a copy of the file is created which is deleted again after sending. The CSV file itself is kept. If the pattern results in the original file name, the CSV file is attached directly.
	Rename attachment time offset	Time offset in seconds for renaming the file. For example -3600 means minus 1 hour. In this way a file that is sent at 0:00h carries the date of the day that has just ended. The offset also applies to placeholders in the file name itself: if it contains a date, the file of the point in time shifted by the offset is sent.

The following placeholders are available for the file name and for renaming the attachment:

%a	Abbreviated name of the weekday	Sun
%A	Full name of the weekday	Sunday
%b	Abbreviated month name	Mar
%B	Full month name	March
%c	Time+date	Sun Aug 19 02:56:02 2012
%d	Day of the month (01-31)	19
%H	Hour in 24-hour format (00-23)	14
%I	Hour in 12h format (01-12)	05
%j	Day of the year (001-366)	231
%m	Month as a decimal number (01-12)	08
%M	Minute (00-59)	55
%p	Designation AM (0-12) or PM (12-0)	PM
%S	Second (00-61)	02
%U	Week number with the first Sunday as the first day of the first week (00-53)	33
%w	Weekday as a decimal number with Sunday as 0 (0-6)	4
%W	Week number with the first Monday as the first day of the first week (00-53)	34
%x	Date	08/19/12
%X	Time	02:50:06
%y	Year, last two digits (00-99)	01
%Y	Year	2012
%Z	Name or abbreviation of the time zone	CDT
%%	Percent sign	%

Storage requirement and write accesses

The CSV file is stored on the same medium as the project, on most controllers this is a memory card. It is therefore subject to two limits: the free space and the limited number of write accesses a memory card can take. Both should be estimated before commissioning, because a recording usually runs for years.

Storage requirement: A line consists of plain text. Date and time together need about 20 characters, every value about 6 to 12 characters depending on the format. As a rough guide, 30 bytes can be assumed for a line with one value and about 110 bytes for a line with ten values. This results in:

Recording	Lines per day	per day	per year
hourly, 1 value	24	less than 1 KB	about 0.3 MB
every minute, 1 value	1,440	about 42 KB	about 16 MB
every minute, 10 values	1,440	about 155 KB	about 55 MB
every 10 seconds, 10 values	8,640	about 0.9 MB	about 330 MB
every second, 10 values	86,400	about 9 MB	about 3.4 GB

If rotation is used, the requirement multiplies according to the number set. Several blocks add up, as do the recordings of the statistic.

Write accesses: For every line the file is opened, the text is appended, the file is closed and the write access is immediately forced onto the storage medium. A memory card never writes just the few bytes of the line, it always writes a complete block, and in addition it has to update the management data of the file. A line of 30 bytes therefore costs a multiple of that in actual write load. It is thus not the size of the file that determines the service life of the card, but the number of triggers.

Practical recommendations:

Do not record faster than necessary. For meter readings and consumption values one value per quarter of an hour or per hour is usually enough. A trigger every second is not intended for this block.
Prefer a few blocks with many inputs over many blocks with one input each: every block writes into its own file and causes its own write accesses.
Choose the format as compact as possible. "%.2f" instead of the default "%f" saves about half of the space with ten values per line.
Use rotation or a file name with a date to make sure the files do not grow without limit. Very large files can neither be sent conveniently nor opened in a spreadsheet program, the common programs display about one million lines.
Always leave enough free space on the storage medium. If it is full, not only the recording fails - transferring a project and creating the automatic backups is then no longer possible either.
For many values at short intervals use the statistic. It collects the values in a database, writes to the storage medium far less often and can show the data directly as a chart in the visualisation.
If large amounts of data are recorded permanently, use a memory card rated for continuous operation and include it in the regular data backup.

If an error occurs while writing, for example because the storage medium is full or write protected, this is noted in the log of the controller. The block itself has no output that reports such an error.

Important: See the notes on using the counter blocks.

See also the general parameters of all function blocks.

CSV file

Inputs

Parameters

Storage requirement and write accesses