What are CSV files?

CSV is the abbreviation for Comma Separated Values and is a commonly used file format when exchanging structured data. It is supported by many popular databases or text programs, such as Excel or Editor, both for reading and outputting information.

What is a CSV file?

A CSV file (short for Comma Separated Values) is a text or database file used in many applications to transfer data. It can store data organized in tables. The name comes from the fact that in the original form values are separated using commas. However, other separators can also be used. In Germany, for example, the semicolon (“;”) is often used because otherwise there could be confusion with the comma in decimal numbers.

A CSV file can be opened and edited with common text programs, such as Excel or the text editor. The relatively easy readability of Comma Separated Values files also leads to their widespread use. In the editing programs, the separator can also usually be specified if it is not a comma.

How is a CSV file structured?

The official structure of a CSV file is regulated in RFC Standard 4180. The following, very vague, characteristics are mentioned, which must be given in a Comma Separated Values file:

Each record is stored in a single line. A line break separates two different data sets.
There can be a final line break in the last line of the file, but it does not have to be.
The first line of the document can be but does not have to be, a so-called header line. In this, there are as many entries as in any other record with the difference that the names for the columns are maintained there.

In addition to these formal features, there are other customs that have become established over the years. As the name suggests, the values of a data set are separated by commas. In German-speaking or European countries, however, a semicolon (“;”) can also be used as a data separator. This was introduced because in this language decimal numbers are also separated with commas and confusion arose as a result.

Empty fields in a CSV file do not have to be explicitly marked but are stored by following two commas (or two semicolons) directly.

Text editor view of a CSV file | Source: Author

If you open a CSV file in the text editor, you can see this structure very well. In the top line we find the header with the column names (“index”, “id”, “dateAdded”,…). Since this is an American file, the values are really separated by commas. Because of the different lengths of the entries, one column is not exactly below the other, which is why the file is difficult to read column by column in this case.

How to define data formats in CSV?

The problem with Comma Separated Value files is the missing definition of a data type. This means that certain specifications can quickly be misinterpreted. For example, the specification 2/10/2022 can mean either October 2, 2022, or February 10, 2022, depending on who opens the file. The parties involved must therefore define how the individual fields are to be interpreted during data exchange.

For example, problems can occur with these data types:

Date format
Number format, e.g. does a period separate thousands or decimals?
Texts, e.g. which character encoding should be used?
Time information, e.g. does 4:03 mean either 4 minutes and 3 seconds or 4 hours and 3 minutes?

Which applications use Comma Separated Files?

The Comma Separated Value files are especially suitable for tabularly arranged data to be exchanged between several systems. The following applications, for example, use the file format:

Websites that offer a data download that cannot take place via an application programming interface.
Many accounting programs use the file format as input or output.
Enterprise Resource Planning tools, such as SAP, output CSV files in which, for example, the inventory is stored.

How to open CSV files in Python?

In Python, the best way to open Comma Separated Values files is to use Pandas. The data is stored directly in a so-called DataFrame. This is a file format of Pandas, which is used especially for tabular data.

The corresponding data set is taken from this Kaggle page.

What are the common problems when working with CSV files?

Most problems with CSV files are often related to formatting and encoding, which can cause errors when reading or writing the file. Some common problems are:

Inconsistent delimiters: CSV files are usually separated by commas, but other delimiters such as semicolons, tabs, and pipes can also be used. Inconsistent delimiters within a file can cause errors when parsing the file.
Missing data: If values are missing from the data, it can cause problems when reading or parsing the file. Sometimes missing data is represented by an empty cell, in other cases by a specific value like “NA” or “null”.
Encoding errors: CSV files are usually saved in UTF-8 encoding, but sometimes files are saved in other encodings such as ANSI or ASCII. This can cause problems with special characters and non-English characters.
Line endings: Different operating systems use different line endings, which can cause problems when transferring files between systems. Windows typically uses carriage return (CR) and line feed (LF) characters, while Mac and Linux systems use only LF characters.
Large files: CSV files can be unwieldy and difficult to process if they are very large. Large files can slow down processing times and cause memory problems. It is important to consider file size and structure.

By being aware of these common issues and taking steps to address them, you can avoid errors and ensure that your CSV files are accurate and useful for analysis.

What best practices should you follow?

In order to prevent the problems just mentioned and avoid them from arising in the first place, there are some tips that you can take to heart. Also, over the years, these best practices have become accepted:

Always define a header: A header provides a description of the data in each column, making it easier to understand and work with.
Choose the delimiter carefully: The comma is the most common delimiter in CSV files, but depending on the data situation, other delimiters such as semicolons or tabs may be more appropriate. For example, in the European language area, the comma is also used to separate the decimal places of a number. So here the semicolon is more suitable.
Handle missing data: Since CSV files often have missing data, it is important to decide how to handle it. A common approach is to use a special symbol like “NA” or “NULL” to indicate missing values.
Use consistent formatting: Consistent formatting makes these files easier to read and work with. For example, dates should be formatted the same throughout the file.
Watch out for encoding problems: CSV files can have encoding problems if they are not saved in the correct format. Therefore, always save in UTF-8 encoding, which is widely supported and can handle all characters.
Check for errors and inconsistencies: Before working with CSV files, it is important to check the data for errors and inconsistencies. This may include checking for missing values, incorrect data types, or inconsistent formatting.
Consider using a CSV library: If you are working with large or complex CSV files, it can be helpful to use a CSV library. These libraries can handle tasks such as parsing, cleaning, and formatting data. Some of the most popular libraries include Pandas and CSVKit.

How is Parquet different from CSV?

CSV files are one of the most used file formats in the analytical field and are preferred by many users because they are easy to understand and compatible with many programs. Therefore, the comparison with Apache Parquet is a good one. Furthermore, CSV files are row-oriented, i.e. they store data record by data record and are therefore the exact opposite of Apache Parquet files. For the comparison, we will use some, different criteria.

Apache Parquet is a column-oriented data format, which is not only available as open source but also enables the efficient processing of complex, large data sets. In addition, it is usable in a wide variety of programming languages, including Python.

Data analysis

For data analysis, CSV files are still quasi-standard, as it is easy to understand for many users and at the same time provide a good basis for fast and uncomplicated data transport. Furthermore, it is compatible with many analysis tools, such as Power BI or Tableau. However, due to its row-oriented structure, individual queries or changes can take a very long time, especially if a table has many columns. At the same time, with CSV, nested data in a column must first be separated in a time-consuming process, while Parquet can already handle this natively very well.

Query Performance

For many queries in the data science area, only individual columns are of interest, since aggregations such as totals or averages are performed with these. Apache Parquet is much better suited for this than CSV due to its column orientation. With CSV files, all columns must always be included in a query, even if they are not needed for the final result. This row orientation can only be useful if you need information from individual rows, for example if you want to search for individual orders in the entire order backlog. However, this is rarely really desired.

Resource efficiency

Due to Apache Parquet’s optimal data compression, the same data can be stored with less disk space than comparable CSV files. This results in less cost in disk consumption or cloud licenses if that is where the data resides. This can quickly add up to a lot of money for large amounts of data. Most importantly, the smaller amount of data also saves money and time in subsequent steps. When the data is processed, this can happen much faster and requires less hardware, which again saves a lot of money.

On the other hand, trained personnel in the use of Apache Parquet is not as widespread as employees who know how to use CSV. In this point, it depends on the amount of data, and whether the purchase of trained personnel is worthwhile.