CSV – The established file format in the industry
The CSV file format describes the structure of a text file for storing plain structured data. The format has been used in the industry for many years and is probably one of the best-known worldwide. In this article, you will learn everything about the advantages and disadvantages and the use of CSV in the industry.
1. What is CSV?
CSV – popular and widely used
The CSV file format stands for comma-separated values. It describes the layout of structural data in a text file, for storage and exchange. These files are identified by the file extension csv. A CSV file is a simple text file organized in rows and columns with each column separated by a comma. CSV is a popular and widespread format used in many applications.
History
CSV is a file format used in the early days of computers and has a long history. The CSV was developed to store records easily and quickly. Even then, these were separated with a comma. In the course of time, the CSV standard was created, which was used more and more over many years and thus also established itself in the industry.
2. Structure of a CSV file
For the structure of a CSV file, no fixed standard exists. Different characters are available to separate data in a CSV file. Semicolons, tabulators, spaces or commas are the most common separators used in practice. Nevertheless, other separators are also conceivable and can easily be implemented with CSV.
The representation of a table as a CSV file has the following structure:
Table
| Hall | Factory | Machine |
|---|---|---|
| Hall 1 | Factory 1 | Machine 1 |
| Hall 1 | Factory 1 | Machine 2 |
CSV file
Hall;Factory;Machine
Hall 1;Factory 1;Machine 1
Hall 1;Factory 1;Machine 2
With CSV, you can save databases or tables in a file and export them. The data records are separated with a semicolon, as described in the example. A CSV file is openable and editable by all common text editors.
3. Industrial use
CSV files are mainly used for saving and exporting databases or tables. Entire data sets can thus be displayed in a CSV file and edited afterwards. The representation can be individually designed and opened by all editors. In this way, you can export databases or tables and store them in the cloud, for example.
Machine data, data from scales or laboratory data can also be saved and provided in a CSV file. Each data record can be stored in a separate file or the data record can be added to an existing file. With the help of middleware, data records are read from a CSV file and made available for use by other systems in the correct location. A middleware can additionally store data in a CSV file and return it to the field level or other systems.
A CSV file is the solution for older machines in terms of connecting to a digital system landscape.
Another benefit of CSV files is the transfer of master data to an ERP system. Thereby master data can be imported as a CSV file and added to the system. In addition, a CSV file exports a master data table and makes it available for other systems.
4. CSV advantages
The CSV file format has been around for many years and is well-established in the industry. One advantage of the data format is that large data packages can be summarized and stored via CSV. These packages can be read and edited by all common text editors. The CSV format is readable by humans and the data packages are customizable in a text editor allowing data modification. Thus, complex data packages get extracted from a database, matching data sets and then re-importing them. CSV provides the basis for this.
CSV is convincing in that it provides human and machine-readable text files for a variety of different programs and systems.
5. CSV disadvantages
CSV has some advantages. Nevertheless, some aspects are disadvantageous about a CSV file.
Changing datasets manually
One advantage of CSV is that records can be modified directly in the CSV file. That also brings a disadvantage at the same time. Exported datasets can be changed afterwards, although a change is not allowed or intended. Thus, it is possible to manipulate important datasets retrospectively without having a proof for this.
Multi User Access
Once multiple users are in a CSV file simultaneously and edit it, the records and the data package become non-functional. There is no protection against multi-user access. Changes are therefore not applied nor are adjustments made by a third party who is simultaneously adjusting data in the file deleted.
Data handling
Handling large data packages in a CSV file is also cumbersome and complicated. Important information from a complex CSV file containing much different data is challenging to find. A CSV file does not have the function of a query, such as a database. Therefore, the data sets are laborious to find, adjust or supplement, especially in the case of large amounts of data.
6. Alternatives to CSV
Over time, some alternatives to CSV have become established in the industry. Some of the most important alternatives, we have listed here.
XML
XML stands for “Extensible Markup Language” and is a language for representing structured data in a text file. XML files are readable by humans and machines. One advantage of an XML file is that XML is easier to read. In addition, XML is less error-prone, for example, when transferring special characters in data records. CSV files can understand some special characters as separators and falsify the data set.
JSON
JSON is a lightweight and easily readable data format in the industry. JSON stands for “JavaScript Object Notation”. Unlike CSV, JSON allows an easy transfer of files into the JSON format, the so-called data parsing.
SQL
SQL stands for “Structured Query Language” and is a database language for defining structures in databases. SQL is used to build and manage large databases. Compared to CSV, SQL works more effectively in databases and gives users more options regarding queries or adjustments to the data sets.
Excel
Microsoft Excel is a spreadsheet software from Microsoft created for building and editing spreadsheets. Also, Excel offers a possibility for queries and editing of data sets and is, therefore, more user-friendly than a CSV file.
OPC
OPC stands for “Open Platform Communications” and is an important communication standard for the industry. OPC focuses on access to machines, devices and systems at the field level (OT) and standardizes communication. That creates a manufacturer-independent data exchange.
7. CSV in the industry
In industry, a CSV file is often used for creating data packages on the machine. With the help of middleware, machine data can be easily and uncomplicatedly saved in a CSV file without depending on the structures or specifications of the data format. With little programming knowledge, a data package of a machine can thus be quickly set up and prepared for evaluation. Then a CSV file is imported into a database, should the structures fit the database software.
8. Software for CSV files
Not every CSV file is compatible with every software. The key factor here is that the structures and separators of the CSV file need to match the software. That enables the interaction and use of CSV files in the industry. Some CSV software programs require specific separators or structures for handling special characters. Once these structures fit together, CSV files are automatically read and made available to other systems. Software that can read and write CSV files is called CSV readers.
Excel as CSV reader
The handling of a CSV file is easy to implement with Microsoft Excel. Excel allows the creation and editing of tables and data sets. For example, customer data can be created in Excel, edited and exported as a CSV file. The result is a CSV file that contains delimited records. These are forwarded to other systems or the cloud.
Existing CSV files will also open with Excel. The Microsoft program even provides the option to select the separator and optimize the visualization. Thereupon, CSV datasets can be adjusted in Excel and exported again as a CSV file. That allows users to make simpler queries and adjustments to large CSV files. For industrial use, Excel, therefore, shines with several advantages:
- Easy import and export of a CSV file
- Simple file handling
- Possibility for dataset queries
Especially for starting into the work with CSV files, the application with Excel offers itself. We provide a sample CSV file for you to try out.
9. Security
The CSV data format does not have encryption and can therefore read by unauthorized third parties. The advantage that humans can read CSV makes encryption even more necessary. However, a CSV file can be secured by, for example, equipping the file with password protection or encryption automation. That encrypts the file, but the content remains plain and thus readable.
Convenient communication with CSV in practice
CSV is an industry-based data format that has been around for a long time. The effortless way to format data records in text form is still in use today and is compatible with many systems.
For example, CSV provides machine data in a package for other systems. The CSV data format allows selected, relevant data to be used for processing and evaluation. Middleware, such as the OPC Router, can save machine data in a CSV file and store it in the cloud. With the OPC Router, a separate CSV file can be created for each data record or a CSV file can be fed with data and supplemented. The OPC Router can write the data from the CSV file into a database or into an ERP system such as SAP. That makes machine data digitally available for evaluation throughout the company.
More information
Read what middleware is and how it can help your business. In our “What is Middleware?” article, you’ll learn all about how it works, its advantages, and how to use it in practice.
Read how using the OPC Router from inray at the bicycle manufacturer corratec creates fail-safety and avoids downtimes. The OPC router and the visualization software FAS achieve more transparent data transfers from the ERP system to the control level in perfect interaction.
Learn more about Industry 4.0! The fourth industrial revolution through digitalization: people, machines and products are directly networked with each other. Read more in our article!
Further interesting articles on the topics of Industry 4.0, cloud, technology, alerting and practical application examples along with case studies can be found in our Knowledge Base.