CSV dialects and validation

Import CSV with Commas Inside a Text Field

Preserve commas inside CSV text by using the correct delimiter and quote rules, then verify complete field values with an executable parser example.

By Uddhav Gopani, FounderPublished 27 September 2026Updated 29 September 2026

Import CSV with Commas Inside a Text Field

To import CSV containing commas inside text, use a CSV parser that recognizes quoted fields. In a comma-delimited file, the value Rao, Anika must be represented as one quoted field when it occupies one column. Splitting each line at every comma cannot distinguish that data comma from a column separator.

Inspect a three-column record

The example below has the columns id, name and note. The name contains a comma, while the note contains double quotation marks. A CSV reader returns exactly three values and preserves the leading zeros in the identifier because this example keeps all fields as strings.

import csv, io

source = 'id,name,note\n00123,"Rao, Anika","She said ""yes""."\n'
rows = list(csv.reader(io.StringIO(source, newline=""), strict=True))
assert rows == [
    ["id", "name", "note"],
    ["00123", "Rao, Anika", 'She said "yes".'],
]
output = io.StringIO(newline="")
csv.writer(output, lineterminator="\n").writerows(rows)
assert list(csv.reader(io.StringIO(output.getvalue(), newline=""))) == rows

The doubled quote marks inside the stored note represent literal quotation marks in the parsed field. The surrounding quote characters delimit the CSV field; they are not extra characters to retain in the name or note. The round-trip assertion verifies the parsed values after writing the CSV again.

Configure the import dialog deliberately

When an application offers delimiter and text-qualifier controls, choose the settings used by the file. For this fixture, the delimiter is a comma and the quote character is a double quote. Inspect the preview and confirm that Rao, Anika occupies one cell rather than two adjacent columns.

If the application guesses incorrectly, return to its text or CSV import workflow and set the options explicitly. Opening the same file through a different route can apply different defaults. The extension alone does not prove which dialect the content uses.

Do not remove or replace data commas

Removing the comma from the name would change the source value. Replacing every comma with a semicolon would change both field separators and commas that belong inside quoted text. Parse the original dialect first, then write the desired destination dialect if an actual format conversion is needed.

If a source row contains an unquoted comma in a value, the intended boundary may be ambiguous. A parser cannot know whether Rao and Anika were intended as separate columns or one name without additional context. Correct that record against the authoritative source rather than automatically joining whichever fields appear extra.

Check embedded newlines and quotes

A quoted CSV value can contain a newline. Read complete logical records through the parser instead of processing one physical line at a time. In Python, opening a CSV file with newline="" lets the CSV reader handle newline details without an extra text-layer transformation interfering.

Use a second fixture with a different comma-containing value, such as Tea, coffee, and water in one notes field. Confirm that it remains one value and that the surrounding columns stay aligned. This catches an import that happens to work for the first name but fails on another quoted field.

Review values after the fields are separated

Correct quoting solves field boundaries, but the receiving application can still reinterpret a value as a date or number. Check identifiers such as 00123, long digit strings and ambiguous dates after importing. Choose text columns where exact string preservation is required.

Also inspect character encoding with an accented name or non-English text from the real dataset. A three-column preview can have correct separators while showing damaged characters. Encoding and delimiter choices need separate checks.

Keep evidence of the successful import

Compare the parsed header, record count and representative field values with the original file. Save the selected import settings with the workflow if it will be repeated. Do not overwrite the original while trying different quoting or delimiter options; preserve it as the reference for resolving an ambiguous row.

CSV parsing references

Python’s CSV reader and writer provide the parser used in the executed example. RFC 4180 describes common quoting conventions.