CSV files

A CSV file is a table saved as plain text. Each line is a row, and the values in a row are separated by commas. Because it’s just text, almost every program that handles data can read and write it, from spreadsheets and databases to banking apps and programming languages. That makes CSV the standard way to move tables between systems.

What a CSV looks like inside

Name,City,Amount
"Smith, Jane",London,1250.00
Ali,"New
York",80

A few simple rules, written down in RFC 4180, make this work:

• A value containing a comma, a quote mark or a line break is wrapped in double quotes.
• A quote mark inside a quoted value is written twice: "He said ""hi""".
• The first row is usually, but not always, the column headings.

Comma, semicolon or tab

Despite the name, not every CSV uses commas. In countries that write decimals with a comma, such as Germany, France and Brazil, Excel saves and expects semicolons, because 12,50 would otherwise split into two cells. Tab-separated files (often .tsv) avoid the problem entirely. A CSV that opens with everything in column A almost always uses a different separator from the one your spreadsheet expected.

What CSV can’t store

• Formatting: no fonts, colours, column widths or merged cells.
• More than one table: a workbook with several sheets becomes several CSV files.
• Formulas: only their results.
• Data types: CSV doesn’t say whether 01067 is a number or text. The program opening the file has to guess.

Why Excel changes your data

That last point causes the most trouble. When Excel opens a CSV directly, it guesses the type of each value and often guesses wrong: postcodes and IDs lose their leading zeros, 16-digit card or account numbers are rounded, and codes like 3-4 or gene names like MARCH1 turn into dates. The data in the file is fine; it’s the opening that alters it. Converting the CSV to an Excel file with a tool that keeps such values as text, or using Excel’s Data › From Text/CSV import, avoids this.

Encoding

CSV has no built-in way to say which character encoding it uses. Modern files should be UTF-8. Excel recognises UTF-8 reliably only when the file starts with a byte-order mark, which is why CSVs exported without one can show é instead of é.

CSV or Excel?

Use CSV to exchange data with other software, import into a system, or keep a simple, future-proof copy. Use Excel (XLSX) when people will work with the data: formatting, formulas, several sheets, and numbers that stay numbers.