CSV files explained: create, open and fix them without headaches

CSV stands for comma-separated values. A CSV file is a plain text file where each line is a row of a table and commas separate the columns. Spreadsheets, databases, banking sites, e-commerce platforms and analytics tools all import and export CSV, which makes it the closest thing to a universal language for tabular data.

What a CSV file looks like

Here is a small table:

And here is the same table as CSV:

Name,City,Score
Ana,Lisbon,92
Daniel,Madrid,85

That is the whole idea. The first line is usually a header with column names, and each following line is one record. Because it is plain text, you can open a CSV in any text editor to see exactly what it contains.

The rules for commas, quotes and line breaks

Problems start when the data itself contains a comma. Consider a city like "Washington, D.C." — the comma inside the value would split it into two columns. CSV solves this with quotes. The most widely followed description of the format, RFC 4180, sets out these rules:

• Fields containing a comma, a double quote or a line break must be wrapped in double quotes.
• A double quote inside a quoted field is written as two double quotes.
• Spaces are part of the field, so Ana, Lisbon makes the second value " Lisbon" with a leading space.

Name,City,Comment
Ana,"Washington, D.C.","Said ""great service"""
Daniel,Madrid,"Line one
Line two"

Good spreadsheet software handles all of this automatically when it saves CSV. You only need to think about it when writing or editing CSV by hand.

Commas vs semicolons: the regional trap

In many countries — including much of Europe and Latin America — the comma is used as the decimal separator (3,14 instead of 3.14). Spreadsheet programs in those regions often use a semicolon as the CSV delimiter instead, because the comma is already taken.

This causes the most common CSV complaint: you open a file and everything appears in one column. The file uses commas, but your spreadsheet expects semicolons, or the other way around. Fixes:

• Excel: instead of double-clicking, use Data → From Text/CSV, and choose the correct delimiter in the preview.
• Google Sheets: File → Import, upload the file and set "Separator type" (or let it detect automatically).
• Numbers (Mac): open the file, then adjust the delimiter in the import settings if columns are merged.
• LibreOffice Calc: shows an import dialog every time where you can tick comma, semicolon or tab.

Accents and special characters

If names like "José" appear as "José", the file is UTF-8 but the spreadsheet read it as a different encoding. Importing through the menus mentioned above usually lets you choose "UTF-8" as the file origin. When exporting, choose "CSV UTF-8" if your spreadsheet offers it, to keep accents intact for whoever opens the file next.

Leading zeros and other automatic "fixes"

Spreadsheets try to be helpful by guessing data types, and sometimes the guess destroys data:

• Leading zeros vanish. A postal code like 01310 becomes 1310, and a phone number loses its leading 0.
• Long numbers turn into scientific notation. A 16-digit ID can appear as 1.23457E+15, and Excel keeps only 15 significant digits, so the last digits may be lost if you save.
• Values become dates. Something like 3-4 or a gene name may be converted into a date.

To avoid this, import the file through the data import tool and set those columns to "Text" before loading. Always keep the original CSV until you have checked the result.

How to create a CSV file

1. From a spreadsheet: build your table, then choose File → Save As (or Download) → CSV. Only the current sheet is saved, and formulas are replaced by their values.
2. By hand: type rows in a plain text editor, separate columns with commas, and save the file with a .csv extension in UTF-8.
3. From a notepad with tables: fill in a grid and export it directly as CSV.

What CSV cannot store

CSV holds values and nothing else. It does not keep:

• Formatting such as bold, colors, column widths or borders.
• Formulas (only their results).
• Multiple sheets — one file holds one table.
• Charts, images or comments.
• Data types — every value is just text until a program interprets it.

That is a feature as much as a limitation. Because CSV is so minimal, almost any program can read it, which is why it remains the default format for moving data between systems.

When to use CSV — and when not to

Use CSV to import contacts into an email service, move product lists between shops, send data to a colleague who uses a different spreadsheet program, load data into analysis tools, or keep a simple, durable archive of a table.

Use a spreadsheet format (.xlsx or .ods) when you need formulas, multiple sheets, formatting or charts to survive.

Quick checklist for clean CSV files

• Include a header row with clear column names.
• Save as UTF-8.
• Know which delimiter your audience expects — comma or semicolon.
• Import, do not double-click, when columns or accents look wrong.
• Set ID, phone and postal code columns to Text when importing.
• Keep the original file until you have checked the import.

CSV looks almost too simple to need explaining, but understanding quoting, delimiters and encodings turns it from a source of frustration into one of the most reliable tools you have for working with data.