CSV vs JSON: choosing a format and avoiding the classic pitfalls

CSV and JSON are the two text formats you will meet most often when moving data between programs. Both are easy to read in a text editor, and both have traps that cost people hours.

The same data in both formats

id,name,city
1,Ada,London
2,"Smith, Jane",Berlin

[
  {"id": 1, "name": "Ada", "city": "London"},
  {"id": 2, "name": "Smith, Jane", "city": "Berlin"}
]

When to use which

• CSV fits flat tables: one row per record, the same columns in every row. Spreadsheets open it directly, and it is compact.
• JSON fits nested or varying data: lists inside records, optional fields, true/false and null. It is the default for web APIs.
• JSON Lines (one JSON object per line) suits logs and large exports, because tools can process it line by line without loading everything.

CSV pitfalls

Commas and quotes inside values. A field containing the delimiter, a quote or a line break must be wrapped in double quotes, and a quote inside it is doubled: "She said ""hi""". This is why splitting a line on commas is wrong:

import csv, io

line = '1,"Smith, Jane","She said ""hi"""'
print(line.split(","))                       # ['1', '"Smith', ' Jane"', '"She said ""hi"""']
print(next(csv.reader(io.StringIO(line))))   # ['1', 'Smith, Jane', 'She said "hi"']

• Delimiters vary. In locales that use a decimal comma, spreadsheet programs often write semicolons instead. Tab-separated files (TSV) are another variant. Check the first line before parsing.
• Encodings vary. Modern tools write UTF-8, but older exports may use a legacy code page. Excel’s “CSV UTF-8” option adds a byte order mark (BOM), which can glue invisible characters onto the first column name. In Python, open such files with encoding="utf-8-sig".
• Everything is text. CSV has no types, so 007, 1e5 or 2026-03-01 are just characters. Spreadsheets may “helpfully” turn them into numbers or dates and drop leading zeros. Keep postal codes and ids as text.
• No single standard. RFC 4180 describes the common form, but files in the wild differ in headers, line endings and empty values. An empty field can mean “empty string” or “missing”; decide which.

In Python, always open CSV files with newline="" and let the csv module do the parsing.

JSON pitfalls

• Strict syntax. Keys and strings need double quotes. There are no comments and no trailing commas. A Python dict printed with print() uses single quotes, so it is not valid JSON; use json.dumps().
• Encoding. RFC 8259 requires UTF-8 for JSON exchanged between systems. Non-ASCII text can appear directly or as \u00e9 escapes.
• Big numbers. JSON itself has no size limit, but JavaScript stores numbers as 64-bit floats, so integers above 9007199254740991 (2 to the power of 53, minus 1) lose precision. APIs often send large ids as strings for that reason.
• No date type. Agree on a string format; ISO 8601 (2026-03-01T12:00:00Z) is the usual choice.
• One document per file. A file with several objects on separate lines is JSON Lines, not JSON. A normal JSON parser reports “Extra data” on the second line.

Practical rules

1. Use a real parser for both formats: the csv and json modules in Python, or jq on the command line.
2. Write UTF-8, and say so in documentation.
3. For CSV, always include a header row and quote fields that need it.
4. Validate a sample after every export: open it in a parser, not only in a spreadsheet.