ZipKit
Cheatsheet

CSV Format Cheatsheet

This cheatsheet covers how to write and read CSV so that commas, quotes and line breaks inside a field survive a round trip. It is for developers who export spreadsheets, import customer data or parse a file that "works on my machine". CSV has no single master specification, so the confusion it clears up is which rules come from RFC 4180 and which are only habits of Excel and your parsing library. For what the format is, see the CSV glossary page.

Quick reference

RFC 4180 rules

Rule What the RFC says
Record separator CRLF (bytes 0D 0A). Some programs use LF only
Last record May or may not end with a line break
Header line Optional, same format as a record, signaled by `header=present` or `header=absent`
Field count Each line should have the same number of fields
Spaces Part of the field. Do not trim them
Quotes around a field Optional. Required if the field holds a comma, a double quote or a line break
Quote inside a quoted field Doubled: `"` becomes `""`
Quotes in an unquoted field Not allowed
Trailing comma The last field must not be followed by a comma
Media type `text/csv`, optional parameters `charset` and `header`

RFC 4180 is category Informational, published October 2005. It describes common practice and does not define an Internet standard.

Delimiters you will meet

Delimiter Name Typical source
`,` CSV RFC 4180, most APIs and databases
`;` Semicolon-separated Spreadsheets in locales where the comma is the decimal separator
tab TSV Database dumps, copy-paste from sheets
vertical bar Pipe-separated Legacy exports where text contains commas

Quoting examples

Field value Written as
`plain` `plain`
`Smith, Jane` `"Smith, Jane"`
`said "hi"` `"said ""hi"""`
two lines `"line1` newline `line2"`
empty string nothing between the commas

Python csv module quoting constants

Constant Behavior when writing
`QUOTE_MINIMAL` Quote only fields with the delimiter, quote character or line break (default)
`QUOTE_ALL` Quote every field
`QUOTE_NONNUMERIC` Quote every non-number. When reading, unquoted fields become floats
`QUOTE_NONE` Never quote. Needs an escape character or it raises an error

Common patterns

Parse fields with commas, quotes and line breaks

import csv, io
data = 'id,name,note\r\n1,"Smith, Jane","said ""hi"""\r\n2,Bob,"line1\r\nline2"\r\n3,,\r\n'
for row in csv.reader(io.StringIO(data, newline='')):
    print(row)
['id', 'name', 'note']
['1', 'Smith, Jane', 'said "hi"']
['2', 'Bob', 'line1\r\nline2']
['3', '', '']

Every value comes back as a string, including numbers. The quoted line break stays inside one field. Open real files with newline='' so Python does not rewrite the embedded line breaks.

Write a CSV file correctly

import csv, io
buf = io.StringIO()
w = csv.writer(buf)
w.writerow(['id', 'name', 'note'])
w.writerow([1, 'Smith, Jane', 'said "hi"'])
w.writerow([2, 'Bob', 'a\nb'])
w.writerow([3, '', None])
print(repr(buf.getvalue()))
'id,name,note\r\n1,"Smith, Jane","said ""hi"""\r\n2,Bob,"a\nb"\r\n3,,\r\n'

The writer quotes only when needed, ends rows with CRLF and writes None as an empty field. Never build CSV with string concatenation.

Handle a semicolon-delimited file

import csv, io
s = 'name;price\nTea;1,50\n'
print(list(csv.reader(io.StringIO(s))))
print(list(csv.reader(io.StringIO(s), delimiter=';')))
print(csv.Sniffer().sniff(s).delimiter)
[['name;price'], ['Tea;1', '50']]
[['name', 'price'], ['Tea', '1,50']]
;

The default comma delimiter splits the price in half and leaves the header as one column. Pass delimiter=';', or use Sniffer to guess it from a sample.

Strip a UTF-8 byte order mark

import csv, io
raw = b'\xef\xbb\xbfid,name\n1,Ann\n'
print(next(csv.reader(io.StringIO(raw.decode('utf-8')))))
print(next(csv.reader(io.StringIO(raw.decode('utf-8-sig')))))
['\ufeffid', 'name']
['id', 'name']

Decoding with utf-8 leaves the BOM glued to the first header, so a lookup of id fails. Decode with utf-8-sig.

Handle ragged rows with DictReader

import csv, io
for r in csv.DictReader(io.StringIO('a,b\n1,2\n3\n4,5,6\n')):
    print(r)
{'a': '1', 'b': '2'}
{'a': '3', 'b': None}
{'a': '4', 'b': '5', None: ['6']}

A short row fills missing columns with None. A long row puts the extras under the key None. Check for both before trusting the data.

Neutralize formula injection before export

import csv, io
def safe(v):
    v = str(v)
    return "'" + v if v[:1] in ('=', '+', '-', '@', '\t', '\r') else v
b = io.StringIO(); w = csv.writer(b, lineterminator='\n')
w.writerow(['name', 'comment'])
w.writerow(['x', '=HYPERLINK("http://example.com","click")'])
w.writerow(['y', safe('=1+2')])
print(b.getvalue(), end='')
name,comment
x,"=HYPERLINK(""http://example.com"",""click"")"
y,'=1+2

Row x shows the problem: quoting alone does not stop a spreadsheet from treating the cell as a formula. Row y shows the sanitized version, which the spreadsheet reads as text.

Pitfalls

  • Splitting on commas: in JavaScript, '1,"Smith, Jane",ok'.split(',') returns [ '1', '"Smith', ' Jane"', 'ok' ], four pieces instead of three. The same mistake in awk counts 4 fields for a,"b,c",d. Use a real CSV parser.
  • Assuming the delimiter: a file from a European locale often uses ;, and a comma decimal such as 1,50 then looks like two columns in a comma parser. Detect or ask for the delimiter.
  • Losing leading zeros: CSV has no types. 007 is text in the file, but a spreadsheet may show 7. Parse such columns as strings, and note that 1e3 is also just text until something converts it.
  • Hidden BOM in the first header: the header reads id and every lookup of id misses. Decode as utf-8-sig.
  • Formula injection: a cell beginning with =, +, - or @ can run as a formula when the export is opened in a spreadsheet. OWASP also lists tab, carriage return and line feed, and full-width variants of those characters in some locales. Quoting is not enough, and OWASP notes that quote-based fixes can fail after Excel saves the file again.
  • Mixed line endings: RFC 4180 says CRLF, but LF files are common, and the W3C CSV on the Web recommendation allows both. Parsers should accept either, and writers should pick one.
  • Reading without newline='' in Python: line breaks inside quoted fields are translated, which corrupts multi-line values.
  • Treating the header as optional by accident: the RFC leaves it to the header parameter or to you. Decide explicitly and document it.
  • Serving the wrong media type: send text/csv and add ;charset=utf-8 when the encoding is not US-ASCII. W3C advises UTF-8 as the default encoding.

Related ZipKit tools

  • CSV to JSON Converter — converts CSV text to a JSON array with header, delimiter and number or boolean parsing options
  • CSV Viewer — opens CSV or TSV files in a sortable, searchable table and auto-detects the delimiter
  • JSON to CSV Converter — converts a JSON array of objects into CSV

Related cheatsheets