DoItAl

Practical guide

How to Read CSV with Quoted Commas and Multiline Cells

Work through a real CSV-shaped example with a comma inside a field, a line break inside a field, doubled quotes, and an empty value.

Prepared by DoItAl · Worked examples checked . How examples are checked and corrected

Start with a file that looks like six lines but has four records

Download the invented sample below and open it as text. It has one header and four data records. The delivery note for ID 00128 contains a line break inside double quotes, so that one record occupies two physical lines. Counting line breaks is not a reliable way to count CSV records.

Keep the source file as your reference. DoItAl's CSV Cleaner previews parsed cells before any optional cleaning. The sample uses comma as its delimiter and retains the five-character IDs as text.

id,delivery_note,status
00127,"Second floor, blue door",paid
00128,"Leave at front desk
Call on arrival",pending
00129,"Customer said ""yes""",paid
00130,,paid

Read the four special cases as cells

A comma between the quotes around Second floor, blue door belongs to one delivery_note cell. The line break between front desk and Call on arrival also belongs to one cell. Two consecutive double quotes inside a quoted cell represent one literal quote. The two commas around the empty note for 00130 leave an empty cell; they do not remove the column.

The table shows the parsed value, not the raw CSV spelling. The parser should return five rows including the header, three columns in every row, and no syntax errors for this sample.

Expected values after parsing the downloadable sample
IDdelivery_note cellstatus cell
00127Second floor, blue doorpaid
00128Leave at front desk ↵ Call on arrivalpending
00129Customer said "yes"paid
00130(empty cell)paid

Why splitting on commas or newlines breaks the file

A simple split on commas would turn the note for 00127 into two columns. A simple split on line breaks would turn the note for 00128 into a second record. Both errors can silently shift values into the wrong destination columns. A CSV parser tracks whether a delimiter or line break is inside a quoted field.

RFC 4180 documents the common rules for quoted commas, quoted line breaks and doubled quotes. Real exports still differ in delimiters and line endings, so inspect the detected delimiter and the parsed preview instead of assuming every text file is comma-delimited.

Distinguish an empty value from a broken row

The blank delivery_note for 00130 is valid because the row still has three cells. A row with only an ID and status would have fewer cells than the header and needs investigation before import. A missing closing quote can make later lines appear to belong to the same cell; stop on parser errors rather than exporting a guessed repair.

CSV Cleaner can report structural issues and apply chosen repairs. It should not decide whether an intentionally blank delivery note is business-invalid. Check the receiving system's rules before filling blanks, dropping rows or renaming headers.

Continue with the operation you actually need

If the receiving system wants only paid records, use Filter & Extract Rows after confirming the parsed columns. In this sample, paid would keep IDs 00127, 00129 and 00130, while 00128 belongs in the excluded output. If the destination wants different column names or order, use Column Mapper after that selection. Mapping changes the layout; it should not turn 00127 into 127.

Download and inspect the final CSV as text before importing it. Spreadsheet software may infer numeric types when opening a file, so explicitly import ID columns as text there. DoItAl processes the local file in your browser, but the destination system's import rules and handling of empty fields remain separate checks.

Sources and further reading

The workflow and sample files are original DoItAl examples. References explain the underlying format or calculation; they do not endorse this site.

Material update: