CSV files are common when working with software or web solutions. But how is a CSV file structured?
Prename,Surname,Place
Andrea,Steiner,Bern
Hans,Meier,"Berlin, Mitte"
Peter,Müller,Paris
Sandra,Weber,WienCSV File Format
CSV and TSV files are text files with the appropriate file extension, where the data is displayed in a simple tabular form using delimiters (see example above).
Each line of text corresponds to a data record with fields or columns separated with the defined separator such as comma. If required, field content can be enclosed with a text delimiter such as a double quotation mark as soon as a comma or line break is part of the field content (see example with "Berlin, Mitte").
CSV itself stands for "comma-separated values" or "character-separated values", since the field separator can vary depending on the application (e.g. comma, semicolon, colon). TSV means «tab-separated-values» and is used for files with tabulators as field separators.
No official standard
Since there is no official standard and the CSV format is only described fundamentally in RFC 4180 by IETF, you will often find the CSV files with different characteristics and encodings.
The smasi CSV-Wizard from 2S-Software can be of help here in correcting the formats for the desired target application.
Advantages and disadvantages
Advantages
CSV files are easy to create and easy to read in any text editor. Due to the few control characters, the text files are relatively slim.
Disadvantages
The CSV format is less suitable for structured content or nested tree structures. In that case, JSON or XML files are preferable. In addition, CSV files cannot be validated and there is no official standard.
Compared to a Microsoft Excel document, a CSV file cannot contain multiple sheets and does not support formatting and formulas.
Intended use
CSV is a widely used format for data exchange, since almost every software solution offers the option for exporting and importing CSV files. Due to the different interpretations of the formats, the files often have to be edited using a suitable tool such as smasi CSV-Wizard.
Recommendation
It is generally recommended to use the format description according to RFC 4180 , but if possible, the TSV format should be used with a tabulator instead of a comma as a separator. Here is the practical recommendation:
If possible, use the TSV format with the tabulator as a field separator.
The first line should include the appropriate headings for all columns.
As soon as a tabulator or a line break is contained in a field content, double quotation marks should be used. Otherwise, it should be avoided.
A number is to be displayed without a thousand separator.
A date is to be mapped in SQL format (YYYY-MM-DD hh:mm:ss).
More information
smasi CSV-Wizard as a helpful tool for reformatting
RFC 4180 format description from IETF (Internet Engineering Task forces)