Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 39 ยท TOML, CSV and Other Formats

CSV Parsing and Its Pitfalls

Learn the CSV format and RFC 4180 rules - quoting, embedded commas and newlines, delimiters, BOM and encodings, type inference - and write a correct CSV parser and writer in Node.

CSV ("comma-separated values") is the lowest common denominator of tabular data. Spreadsheets export it, databases dump it, and every programming language can read it. It looks trivial: a line per row, commas between cells. That is exactly why so many programs get it wrong. This lesson teaches the rules that make CSV safe to read and write, and then builds a small parser that handles them.

The basic shape

TEXT
id,name,price
1,Notebook,3.50
2,Pen,1.20

The first line is usually a header naming the columns. Every following line is a record. There is no type information: 3.50 is just text until your code converts it. There is also no official single standard. RFC 4180 (2005) describes the common practice, and most tools follow it:

  1. Records are separated by line breaks (CRLF in the RFC; LF is accepted nearly everywhere).
  2. Fields are separated by a comma.
  3. A field may be enclosed in double quotes. A field must be quoted if it contains a comma, a double quote or a line break.
  4. A double quote inside a quoted field is written as two double quotes ("").
  5. Every record should have the same number of fields.
TEXT
id,note
1,"Says ""hello"", then leaves"
2,"line one
line two"

Record 1 has the note Says "hello", then leaves. Record 2 contains a newline inside a field, so a CSV record is not always one physical line.

Why split(',') is wrong

The naive parser line.split(',') breaks on the first quoted comma, on embedded newlines and on escaped quotes. Use a real parser, or write a small state machine as below.

Node.js Naive split versus a correct CSV parser

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The naive version splits the quoted note into several wrong cells and cuts record 2 in half. The state machine tracks whether it is inside quotes, which is the only thing that matters.

Header rows to objects

Most programs want records as objects. Zip the header with each row, and decide how to treat missing or extra fields.

Node.js From rows to typed objects, and back to CSV

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Everything the parser returns is a string. Number(r.price) is your job, as is deciding what an empty cell means (empty string, null, or zero).

Pitfalls to know

  • Delimiters vary. In many European locales spreadsheets export semicolons because the comma is the decimal separator (3,50). Tabs give TSV. Let the user choose the delimiter or sniff it from the header.
  • Encoding and the BOM. Excel often writes UTF-8 with a byte order mark (the invisible character U+FEFF) at the start, which ends up glued to the first header name. Strip it (text.replace(/^๏ปฟ/, '')) and always read the file as UTF-8 explicitly; older exports may be Windows-1252.
  • Leading zeros and long numbers. 007 and 12345678901234567890 survive in CSV but are mangled the moment a spreadsheet interprets them as numbers. Treat identifiers as strings.
  • Dates. There is no date type; 03/04/2025 is March 4 in the US and April 3 elsewhere. Prefer ISO 2025-04-03.
  • Empty versus missing. a,,c has an empty second field; a line with fewer fields is a different problem. Decide a policy.
  • Trailing newline. A final line break does not create an extra empty record.
  • Header duplicates. Two columns with the same name collapse into one object key.
  • CSV injection. A cell that starts with =, +, - or @ may be run as a formula when the file is opened in a spreadsheet; see Data Format Security.

Streaming large files

A CSV file with millions of rows should not be read into memory at once. Parse it as a stream: read chunks, keep the parser state (inside quotes or not, partial field) between chunks, and emit each record as soon as it is complete. In Node, combine fs.createReadStream with readline only if no field contains a newline; otherwise use a parser that supports quoted newlines in streaming mode, such as a state machine like the one above fed chunk by chunk. See also JSON Lines and Binary Formats for streaming-friendly alternatives.

flowchart LR
  A["chunk of bytes"] --> B["decode UTF-8"]
  B --> C["state machine: field / quotes / row"]
  C --> D["complete record"]
  D --> E["validate + convert"]
  E --> F["process or write"]

A streaming parser keeps its state between chunks.

CSV versus JSON

CSV is compact and spreadsheet-friendly but flat: no nesting, no types, no standard. JSON is typed and hierarchical but repeats every key in every row. For tabular exports CSV wins; for configuration and APIs JSON wins. See Choosing a Format and Converting Between Them.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which field MUST be enclosed in double quotes in CSV?

    Those characters would otherwise be read as structure. A quote inside a quoted field is doubled.
  2. 2. How is the text 5" pipe written inside a quoted CSV field?

    CSV escapes a double quote by doubling it, not with a backslash.
  3. 3. Why is line.split(',') not a safe CSV parser?

    Quoted fields may contain commas and newlines, so you must track quote state.
  4. 4. What type are values returned by a CSV parser?

    CSV carries no type information; converting is the application's job.
  5. 5. What is a BOM at the start of a CSV file likely to do?

    The invisible U+FEFF character is read as text, so the first column name looks right but does not match.

Summary

  • CSV is rows of delimited text; RFC 4180 describes quoting and escaping by doubling quotes.
  • A record can span several lines; never parse with split(',').
  • Everything is a string: convert types, handle empty cells and dates deliberately.
  • Watch for delimiter variants, BOM, encodings, and leading zeros.
  • Stream big files and guard against formula injection when writing for spreadsheets.

Next: INI, .properties and Other Plain-Text Formats