CSV Objekt
CSV Transformation
The data format “Comma Separated Values” (CSV) is a bedrock of data formatting. Nevertheless, it is still in use, because the pragmatic way of formatting data records in text form is still easy to process and compatible with many systems. The legacy systems alone, which have CSV interfaces for many years, make it necessary to be able to read and write data using CSV.
The format itself is independent of the storage in files, whereas in most applications the CSV data is exchanged by file.
The integrated CSV transfer object of the OPC Router is able to process and create different CSV dialects. Via the different plug-ins CSV data can be sent from and to different sources. The file objects of the OPC Router can be used to read and write files for the CSV data.
You can find a dedicated video about this topic by visiting our tutorial stream.
Value transfer from CSV file to SQL database
That’s how easy it is.
Connecting Systems
Transfer-Objects
The CVS transfer object maps the CSV structure of the records and allows the configuration of the CSV format variants.
Structure
The structure of the CSV data is divided into columns. Each column gets a name which is understood as header of the CSV format and which is offered as field for connection in the OPC router connection. For the columns a type default is optionally possible. This allows the OPC Router to check the type at runtime.
Format
The CSV format is not subject to a generally valid specification, so that there are several variants in real use. In the transfer object the appropriate settings can be made for this. Among other things these: Separator, fixed or variable field length, character string identification, date format, header usage etc.
Trigger
The CSV object does not have its own trigger. Normally the CSV object is used with the file trigger integrated in the OPC router. The file trigger initiates connections when new files appear or when data changes in existing files. Processed files can be moved or deleted by the trigger.
Browsing
The browsing of CSV data is limited to the columns of the data named as fields. In the OPC Router connection these are available for connections with the fields of other plug-ins.
Application examples for data transformation with CSV
Ongoing CSV data import
Some systems continuously create CVS files with process data during operation. According to internal logics, new files are created regularly (e.g. hourly or daily). These files are then available for other systems to take over the process data. With the OPC Router, the files can be read in sequentially via file trigger and evaluated via CSV object and transferred to a database, for example. In this way a continuous and automatic import is realized.
CSV upload via FTP
Systems with CSV interface partially provide FTP (File Transfer Protocol) access for the access. FTP serves as a simple, but defined access to the files. And this also across network boundaries. With the Network File Access Plug-in of the OPC Router the access to these files can be realized and then combined with the CSV object. A system integration of CSV based systems is thus possible network wide.
Video: Easy transfer of CSV files to a database
Our video tutorial shows how to extract records from a CSV file and write the CSV column values to a relational database table. Using the plug-ins of the OPC Router data can be read out and written to a corresponding table very easily via drag & drop.