What Is CSV and Why Everyone Uses It

CSV (Comma-Separated Values) is the simplest format for storing tabular data in plain text. Each line represents one row of a table, and the values are separated by commas. Because it is simple and supported by every program — from spreadsheets to databases — it has become the universal language for exchanging data between programs.

name,surname,age
Ana,Smith,34
Marko,Miller,41
Petra,Wilson,29

How CSV Is Structured

The first line is usually the table header, naming the columns. Each following line is one record. The default separator is a comma, but in Europe a semicolon (;) is often used instead, because decimal numbers there use a comma.

Quotes in Values

But what if a value contains the separator itself — for example the surname Miller, Jr.? In that case we enclose the value in double quotes, so the program knows where the value begins and ends. If the value contains a quote, we write it doubled.

name,surname,note
Ana,"Miller, Jr.","payment is ""in full"""

CSV in Spreadsheets

When we open a CSV file in Excel or LibreOffice Calc, the text is converted into a spreadsheet with columns. The biggest source of trouble is the separator: if the program does not recognize the semicolon or comma on its own, all values end up in a single column. In LibreOffice the separator can be chosen during import, while Excel often helps with the import wizard.

CSV and Databases

CSV is also the standard format for exporting and importing data into databases. The header rule applies here as well: column names must match the names in the table. To import into MySQL we use the LOAD DATA command or the phpMyAdmin tool.

LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 1 ROWS;

When CSV Is Not the Right Choice

CSV is great for simple tables, but it has limits: it does not support multiple sheets, cell formatting, formulas or data types. Numbers and dates can be interpreted differently by each program. For complex needs there are more advanced formats, such as XLSX or JSON.

CSV became the standard because of its simplicity: it is readable by humans, manageable for every program and free of unnecessary content. Just right for exchanging data between programs, regardless of system or language.