CSV files explained: delimiters, quotes, encoding and Excel

Updated: 23 October 2026

A CSV file is plain text: one record per line, with the values separated by commas. Almost every database, spreadsheet and online service can export it, which makes it the usual way to move a table from one program to another. It is also easy to get wrong, because CSV was in use long before anyone wrote it down, and programs still disagree on the details.

This part of the series covers the rules CSV has, the separator and quoting problems that split values in the wrong place, character encoding, what spreadsheets change when they open a file, and a risk to keep in mind when you export data that others will open.

The rules CSV actually has

There is no single official standard. The closest is RFC 4180, published in October 2005, which documents the format most programs use and registers the text/csv media type. It is an informational document, not an Internet standard, and it sets out these rules:

Each record is on its own line, ended by a line break (CR LF). The last line may or may not end with one.
The first line may be a header with the column names, in the same format as the other lines.
Values are separated by commas, every line should have the same number of values, and there is no comma after the last one.
Spaces are part of the value: in Ana, Lisbon the second value starts with a space.
A value that contains a comma, a double quote or a line break should be wrapped in double quotes.
Inside a quoted value, a double quote is written twice.

Everything else varies. The Library of Congress, which catalogues file formats for long-term preservation, lists common departures: semicolons instead of commas, line breaks with only LF or only CR, backslashes or single quotes used instead of double quotes, and different ways of handling spaces. A file can therefore be a valid CSV for the program that wrote it and still be read wrongly by another.

Commas, semicolons and tabs

Where the comma is the decimal separator, as in much of Europe and South America, a comma between values would clash with numbers such as 1,20. Files made there often use a semicolon instead. Excel follows the computer's regional settings: Microsoft notes that setting the decimal separator to a comma makes Excel use a semicolon as the list separator.

That is the usual reason a file opens with everything in the first column: it was written with one separator and read with another. The same table, with a decimal comma in the price (the slash marks a new line):

name;price / Pão;1,20Semicolon-separated: two columns, name and price, and 1,20 stays one value.
name,price / Pão,"1,20"Comma-separated: the price has to be quoted, or 1 and 20 would become two values.
name;price / Pão;1,20 read as comma-separatedThe header becomes a single column called name;price, and the comma in 1,20 splits the row into Pão;1 and 20.

Tab-separated files avoid most of this, because tabs rarely appear inside values. They often use the .tsv or .txt extension rather than .csv.

Quotes: the rule that matters most

Many broken CSV files come down to a value that needed quotes and did not get them, or a quote that was never closed. These follow RFC 4180:

"Lisbon, Portugal"One value. Without the quotes, it would split into two columns, Lisbon and Portugal.
"She said ""hi"""Read as She said "hi": inside quotes, each "" stands for one double quote.
"line one / line two"One value with a line break inside (shown here as a slash). Programs that read a file line by line, without looking at quotes, split it into two records.
"never closedA quote with no partner can swallow the rest of the file into one value. A careful parser reports it as an error instead.

If you write CSV yourself, quote any value that contains the separator, a double quote or a line break, and double the quotes inside it. Quoting every value is also allowed and avoids surprises.

Character encoding: why accents turn into Ã£

A CSV file says nothing about its character encoding. RFC 4180 notes that common usage is US-ASCII, which has no accented letters, and today most files are UTF-8. UTF-8 stores letters such as ã, é or € in two or three bytes. A program that reads those bytes with a different encoding, such as Windows-1252, shows each byte as a separate character:

Pão → PÃ£oThe two bytes of ã, read as Windows-1252.
café → cafÃ©The same happens to é.
€ → â‚¬The euro sign takes three bytes in UTF-8, so it becomes three characters.

Microsoft explains that Excel opens a UTF-8 CSV correctly by double-clicking only if the file starts with a byte order mark (BOM), three invisible bytes that announce UTF-8. Without one, Microsoft recommends importing the file instead, with Data > From Text/CSV. The same garbling in a file you created yourself means it was saved in one encoding and opened in another.

What spreadsheets change when you open a CSV

When Excel opens a .csv file directly, it guesses each value's type with its default settings. Microsoft documents what that does to data that only looks like a number or a date:

Value in the CSV	After opening in Excel	Why
007	7	Leading zeros are removed from numbers.
1234567890123456	1234567890123450, shown in scientific notation	Excel keeps 15 significant digits; the rest become zeros.
SEPT2	2-Sep	Read as a date.
MARCH1	1-Mar	Read as a date.

The changes are kept if the file is saved again. The gene names in the table are real examples: a 2016 study in Genome Biology found such conversions in about one fifth of the papers that published gene lists as Excel files, and in 2019 the HUGO Gene Nomenclature Committee renamed the SEPT and MARCH genes to SEPTIN and MARCHF because of it. Postcodes, phone numbers, product codes and long identifiers are affected the same way.

To keep values as they are, import the file instead of opening it: Data > From Text/CSV, then Edit to open Power Query, select the columns and set their data type to Text. Microsoft also suggests renaming the file from .csv to .txt before opening it, which brings up the Text Import Wizard, where you choose how each column is read. Recent versions of Excel for Microsoft 365 and Excel 2024 also have Automatic Data Conversions settings that can turn some of these changes off.

A risk when exporting: formula injection

A value that starts with =, +, - or @ can be read as a formula by spreadsheet programs. If a website exports text that users typed, such as names or comments, a value written as a formula can be run as one when someone opens the file, instead of being shown as text. MITRE’s catalogue of software weaknesses lists this as CWE-1236, better known as CSV or formula injection.

It concerns software that writes CSV files from untrusted input. The usual defence is to put a single quote (') in front of any value that starts with one of those characters, although MITRE notes that no fix works in every spreadsheet program. If you receive a CSV from an unknown source, look at it in a text editor or import it as text before trusting what a spreadsheet shows.

CSV or JSON?

CSV is a flat table with no types: every value is text until a program decides otherwise. JSON, from the first part of this series, has numbers, true, false, null, lists and nested objects. CSV is smaller and opens in any spreadsheet; JSON is what most web APIs use. Converting between them means deciding what to do with the differences.

The nTools converter keeps every CSV value as text, so 007 and long identifiers survive intact, and turns nested JSON values into JSON text inside one cell. It reads comma, semicolon and tab-separated data, follows the quoting rules above, reports a quote that is never closed, and runs entirely in your browser.

CSV ↔ JSON converter

Frequently asked questions

Why does my CSV open with everything in one column?

The file uses a different separator from the one the program expects, usually a semicolon read as comma-separated or the other way round. Import it and choose the separator instead of double-clicking the file.

Why are the accents wrong after opening a CSV?

The file is UTF-8 and was read with another encoding. In Excel, import it with Data > From Text/CSV instead of double-clicking it, or save the file as UTF-8 with a byte order mark.

How do I keep leading zeros when opening a CSV in Excel?

Import the file instead of double-clicking it, and set the columns that hold codes, postcodes or phone numbers to Text before loading.

Can a CSV file have several sheets or formatting?

No. It holds one table of plain text. When you save a workbook as CSV, Excel saves only the current sheet, and features that plain text cannot hold, such as formatting and charts, are lost.

Is a semicolon-separated file still a CSV?

Strictly, RFC 4180 describes commas only, but semicolon-separated files are common and most programs accept them when you choose the separator.