CSV Format: What a CSV File Is, Quoting Rules and Excel Pitfalls

CSV file format rules (RFC 4180)

Each record is one line ending in CRLF, and fields are separated by commas. An optional first line holds the column names. A field that contains a comma, a double quote or a line break must be enclosed in double quotes, and a double quote inside such a field is written twice.

id,name,note
1,Ada Lovelace,first programmer
2,"Hopper, Grace","said ""it's easier to ask forgiveness"""
3,Alan Turing,"multi-line
note"

The third record is still one record, even though it spans two lines. That is why splitting a CSV file on newlines, or a line on commas, is wrong. Always use a real CSV parser.

Beyond RFC 4180, real files vary. Semicolons are the default separator in locales that use a decimal comma, such as much of Europe, because Excel follows the system's list separator. Tabs are used in TSV files. Some producers escape quotes with a backslash instead of doubling them. When a file will not parse, look at the first few lines in a text editor, not a spreadsheet, and check the separator, the quote style and the encoding.

Converting CSV to JSON

Each row becomes an object keyed by the header row. The main decision is types: CSV has none, so every value is a string until you convert it.

import csv, json

with open('users.csv', newline='', encoding='utf-8-sig') as f:   # -sig strips an Excel BOM
    rows = list(csv.DictReader(f))

for r in rows:
    r['id'] = int(r['id'])                     # convert types explicitly
print(json.dumps(rows, indent=2, ensure_ascii=False))

# Miller: CSV to JSON on the command line
mlr --icsv --ojson cat users.csv > users.json

Convert types deliberately, column by column. Automatic inference turns ZIP codes such as 02115 into 2115, phone numbers into numbers, and IDs longer than 15 digits into rounded floats. An empty field could mean an empty string, null or "not provided"; decide which, and apply it consistently.

Pass newline='' in Python, as the csv module documentation requires. Without it, quoted fields containing line breaks are read incorrectly on some platforms.

Converting JSON to CSV

The hard part is that JSON is a tree and CSV is a table. Flat arrays of objects convert directly; nested objects and arrays need a flattening rule.

[{"id": 1, "name": "Ada", "address": {"city": "London"}, "tags": ["math", "code"]}]

id,name,address.city,tags
1,Ada,London,math;code

The usual rules are: nested objects become dotted column names (address.city), and arrays are joined with a separator that does not appear in the data, or expanded into one row per item when each item needs its own row. With pandas, pd.json_normalize(data) applies the dotted-name rule. With jq, a plain @csv fails on arrays ("array is not valid in a csv row"), so join them first:

jq -r '(.[0] | keys_unsorted) as $k
       | $k, (.[] | [.[$k[]] | if type == "array" then join(";") else . end])
       | @csv' users.json

Objects that do not all have the same keys need a combined header: collect every key from every object first, or the columns will shift between rows. Many quick JSON to CSV converters take the header from the first object only.

What Excel does to your CSV

Most CSV problems reported by users are really Excel problems. When Excel opens a CSV file by double-clicking, it converts values it recognises: leading zeros disappear (00123 becomes 123), long numbers such as card or IMEI numbers turn into scientific notation and lose digits after the fifteenth, and text such as 1-2 or MARCH1 becomes a date. The scientific community renamed several human genes because of this.

Encoding is the other trap. Excel on Windows assumes a legacy code page for CSV files unless the file starts with a UTF-8 byte-order mark. Write CSV for Excel users with encoding='utf-8-sig' in Python, or prepend \uFEFF in JavaScript, and accented names will display correctly.

To keep values as text, users should import the file through Data > From Text/CSV and set those columns to Text, rather than opening it directly. For data you control, consider offering XLSX alongside CSV.

CSV injection

A field that starts with =, +, - or @ is treated as a formula when a spreadsheet opens the file. If an export contains user-supplied text, such as a name or a comment, an attacker can plant a formula that runs when an administrator opens the export. OWASP describes this as CSV injection, or formula injection.

When exporting user data for spreadsheets, prefix any value starting with those characters, and with tab or carriage return, with a single quote, and still quote the field. Keep a raw export without this escaping for systems that read the file programmatically.

Sources and further reading

RFC 4180 describes the common CSV format and the text/csv media type. The Python csv module documentation covers dialects and the newline='' requirement. The OWASP page on CSV injection describes formula injection. The Miller, jq and pandas documentation cover conversion and flattening, and Microsoft documents importing text files with explicit column types in Excel.

Frequently asked questions

How do I convert CSV to JSON?

Parse the file with a real CSV parser, such as Python's `csv.DictReader` or `mlr --icsv --ojson`, so that each row becomes an object keyed by the header. Then convert types explicitly, because CSV stores everything as text.

How do I convert JSON to CSV?

For a flat array of objects, each key becomes a column. Flatten nested objects into dotted column names and join or expand arrays; `pandas.json_normalize` or a jq filter that joins arrays handles this.

How are commas inside CSV values handled?

Enclose the field in double quotes: `"Hopper, Grace"`. A double quote inside a quoted field is written as two double quotes, per RFC 4180.

Why does Excel remove leading zeros from my CSV?

Excel converts fields that look like numbers when it opens a CSV directly. Import the file through Data > From Text/CSV and set the column type to Text, or provide an XLSX file.

Why do accented characters look wrong when I open a CSV in Excel?

Excel assumes a legacy encoding unless the file starts with a UTF-8 byte-order mark. Save the file as UTF-8 with BOM, for example with `encoding='utf-8-sig'` in Python.

What is CSV injection?

A field starting with `=`, `+`, `-` or `@` runs as a formula when opened in a spreadsheet. Escape such values in exports that contain user input by prefixing them with a single quote.