Commas and Double Quotes in CSV: Escape Fields Correctly

Read the three different jobs of punctuation

In P11, the first and second structural commas separate item_id, description, and note. The comma inside Mug, blue is ordinary description text because the entire field is quoted. The outer quotes are file syntax and do not belong to the parsed description.

The note contains a literal pair of quotation marks around Fragile. Each becomes a doubled quote inside the quoted field. The ending sequence has three quotes: two encode the closing quotation mark in the note, and one closes the CSV field. Read this as structure and content, not as a count of decorations.

Choose quoting based on the whole field

Use the table when reviewing an export. It describes a common double-quote CSV dialect, not every possible custom delimited format. If a supplier uses a different escape character, agree on that convention before modifying its files.

Let a CSV writer build the file

The Python example starts with ordinary field values and writes the complete sample. Keep newline="" when opening the output. The writer is responsible for escaping the note and description, so you do not add extra quote characters to those input values yourself.

import csv

rows = [
    ["item_id", "description", "note"],
    ["P11", "Mug, blue", 'Label says "Fragile"'],
    ["P12", "Tray", "Top shelf\nKeep upright"],
]
with open("sample.csv", "w", encoding="utf-8", newline="") as f:
    csv.writer(f, lineterminator="\n").writerows(rows)

with open("sample.csv", encoding="utf-8", newline="") as f:
    assert list(csv.reader(f)) == rows

Avoid hand-built CSV concatenation

Joining every row with commas appears to work until a value contains a comma. Adding quotes around everything also fails if literal quotes inside the fields are left unescaped. Both mistakes can survive a quick inspection when most rows contain simple text.

Do not use smart quotation marks as field boundaries. A typographic opening quote and closing quote are different characters from the straight double quote used by this dialect. A word processor can introduce that substitution while editing an otherwise ordinary example.

Verify values after a complete round trip

Read the generated file back with a CSV parser. Compare field values, record count, and field count with the original table. The code checks all values directly, including the newline and literal quotation marks. It does not merely compare file size or count commas.

Two valid CSV files can differ in whether they quote optional fields or use LF versus CRLF record endings. For most data comparisons, parsed field equality is more useful than byte equality. If a receiver requires particular line endings, encoding, or quoting, include those requirements in your export settings and check them separately.

Keep file syntax separate from cell types

Quoting a digit-only identifier protects its field boundary; it does not declare an Excel Text cell. Import identifiers with a Text type when zeros or long digit strings matter. Likewise, correctly escaped numeric text still needs the correct decimal convention before it can be used in calculations.

Empty fields also need an agreed meaning. PostgreSQL documents different handling for an unquoted empty value and quoted empty text in its default CSV COPY behavior. Do not assume every spreadsheet, database, and parser treats them as interchangeable. Column Harbor checks selected CSV risks but does not rewrite quoting or set destination data types.

Related help: Preserve leading zeros with an explicit Excel import type