How to escape quotes in CSV

Wrap the field in double quotes and write each embedded double quote twice. Parse the file back and print every field with its length: here She said ""hi"" came back as 13 characters holding one quote pair. The backslash spelling parsed without error into four fields instead of three.

Why check this

Run this at review of any export feature, and again when a ticket says a row landed in the wrong columns.

CSV has no escape character. RFC 4180 section 2 gives one mechanism for all three troublesome characters: enclose the field in double quotes, and write any quote inside it twice. A writer that reaches for a backslash instead, the habit JSON taught it, produces a file no reader rejects and every reader misreads.

The failure it prevents is a silent column shift. Step 4 reproduces it: one row carries four fields where its neighbours carry three, so a name lands in the address column and the import reports success.

Prerequisites

• Node 22 for the builder and the parser, both dependency free.
• Python 3 for the second reader. Its csv module ships with the interpreter.
• LibreOffice headless, plus xxd, diff, grep and sed from Git Bash. This run called LibreOffice Calc 24.2.3.2 by its full path, Ukrainian UI locale.
• Every quote, backslash, CR and LF below is built at runtime from String.fromCharCode: an escape typed into a tool argument can arrive decoded.

make.mjs writes three fixtures: one conforming, one row in two spellings, four accidents.

import { writeFileSync } from 'node:fs';

const DQ = String.fromCharCode(34);   // "
const BS = String.fromCharCode(92);   // \
const CR = String.fromCharCode(13);
const LF = String.fromCharCode(10);
const q = (s) => DQ + s + DQ;

const quoting = [
  'id,value,case',
  '1,' + q('Doe, John') + ',comma',
  '2,' + q('She said ' + DQ + DQ + 'hi' + DQ + DQ) + ',doubled quote',
  '3,' + q('line one' + LF + 'line two') + ',newline',
  '4,' + q('a, ' + DQ + DQ + 'b' + DQ + DQ + ', and' + LF + 'c') + ',all three',
].join(LF) + LF;

const escape = [
  'id,value,case',
  '1,' + q('Doe, ' + DQ + DQ + 'Jack' + DQ + DQ + ', John') + ',doubled',
  '2,' + q('Doe, ' + BS + DQ + 'Jack' + BS + DQ + ', John') + ',backslash',
].join(LF) + LF;

const edge = [
  'id,value,case',
  '1, ' + q('leading space') + ',space before the quote',
  '2,' + q('ab') + 'cd,partly quoted',
  '3,' + q('lf' + LF + 'inside') + ',lf in field',
  '4,' + q('crlf' + CR + LF + 'inside') + ',crlf in field',
  '5,' + DQ + 'unterminated,eof',
].join(LF) + LF;

for (const [name, text] of [['quoting.csv', quoting], ['escape.csv', escape], ['edge.csv', edge]]) {
  writeFileSync(name, text, 'utf8');
  console.log(name.padEnd(12) + 'bytes ' + Buffer.byteLength(text, 'utf8'));
}

rfc4180.mjs is the reader, printed in full so you can run it yourself.

import { readFileSync } from 'node:fs';

const DQ = String.fromCharCode(34);
const CR = String.fromCharCode(13);
const LF = String.fromCharCode(10);

export function parse(text) {
  const rows = [];
  const notes = [];
  let row = [];
  let field = '';
  let quoted = false;  // the cursor is inside a quoted field
  let closed = false;  // a quoted field has ended and the cursor is still in it
  let i = 0;
  const at = () => 'record ' + (rows.length + 1);
  const endField = () => { row.push(field); field = ''; quoted = false; closed = false; };
  const endRow = () => { endField(); rows.push(row); row = []; };

  while (i < text.length) {
    const c = text[i];
    if (quoted) {
      if (c === DQ) {
        if (text[i + 1] === DQ) { field += DQ; i += 2; continue; }  // the rule
        quoted = false; closed = true; i += 1; continue;
      }
      field += c; i += 1; continue;
    }
    if (c === DQ) {
      if (field === '' && !closed) { quoted = true; i += 1; continue; }
      notes.push(at() + ': a quote inside an unquoted field, kept as text');
      field += c; i += 1; continue;
    }
    if (c === ',') { endField(); i += 1; continue; }
    if (c === CR && text[i + 1] === LF) { endRow(); i += 2; continue; }
    if (c === LF || c === CR) { endRow(); i += 1; continue; }
    if (closed) { notes.push(at() + ': text after the closing quote, kept as text'); closed = false; }
    field += c; i += 1;
  }
  if (quoted) notes.push(at() + ': the file ended inside a quoted field');
  if (field !== '' || row.length > 0) endRow();
  return { rows, notes };
}

const visible = (s) => s.split(CR).join('<CR>').split(LF).join('<LF>');

export function report(file, mode) {
  const text = readFileSync(file, 'utf8');
  const lf = [...text].filter((c) => c === LF).length;
  const { rows, notes } = parse(text);
  console.log(file + '  LF bytes ' + lf + '  records ' + rows.length + '  data rows ' + (rows.length - 1));
  if (mode !== 'count') {
    for (const [r, row] of rows.entries()) {
      console.log('record ' + (r + 1) + '  fields ' + row.length);
      for (const [f, v] of row.entries()) {
        console.log('  [' + f + '] len ' + String(v.length).padStart(2) + '  ' + visible(v));
      }
    }
  }
  for (const n of notes) console.log('note: ' + n);
}

if (process.argv[1] && process.argv[1].endsWith('rfc4180.mjs')) report(process.argv[2], process.argv[3]);

pycsv.py is the second opinion, and not mine.

import csv, sys

visible = lambda s: s.replace(chr(13), "<CR>").replace(chr(10), "<LF>")
path, strict = sys.argv[1], len(sys.argv) > 2 and sys.argv[2] == "strict"

with open(path, newline="", encoding="utf-8") as fh:
    reader, n = csv.reader(fh, strict=strict), 0
    try:
        for row in reader:
            n += 1
            print("record " + str(n) + "  fields " + str(len(row)))
            for f, v in enumerate(row):
                print("  [" + str(f) + "] len " + str(len(v)).rjust(2) + "  " + visible(v))
    except csv.Error as e:
        print("record " + str(n + 1) + "  csv.Error: " + str(e))

cells.sh reads the cell text out of LibreOffice's HTML.

sed -e 's/ data-sheets-value="[^"]*"//' -e 's/<br>/[BR]/g' "$1" \
  | grep -o '<t[rd][^>]*>[^<]*' \
  | sed -e 's/<tr>/row/' -e 's/<td[^>]*>/  cell: /'

Build the fixtures and convert two.

node make.mjs

quoting.csv bytes 128
escape.csv  bytes 80
edge.csv    bytes 156

soffice --headless --convert-to html --outdir calc quoting.csv escape.csv

Steps

1. 1

   Step 1.

   Read the bytes of the conforming fixture.

   xxd quoting.csv
   

   00000000: 6964 2c76 616c 7565 2c63 6173 650a 312c  id,value,case.1,
   00000010: 2244 6f65 2c20 4a6f 686e 222c 636f 6d6d  "Doe, John",comm
   00000020: 610a 322c 2253 6865 2073 6169 6420 2222  a.2,"She said ""
   00000030: 6869 2222 222c 646f 7562 6c65 6420 7175  hi""",doubled qu
   00000040: 6f74 650a 332c 226c 696e 6520 6f6e 650a  ote.3,"line one.
   00000050: 6c69 6e65 2074 776f 222c 6e65 776c 696e  line two",newlin
   00000060: 650a 342c 2261 2c20 2222 6222 222c 2061  e.4,"a, ""b"", a
   00000070: 6e64 0a63 222c 616c 6c20 7468 7265 650a  nd.c",all three.

   2222 at 0x2e and at 0x32 is a doubled quote; 22 at 0x34 closes the field. The 0a at 0x4f lies inside a quoted field, so it is data.

2. 2

   Step 2.

   Parse it and print every field with its length.

   node rfc4180.mjs quoting.csv
   

   quoting.csv  LF bytes 7  records 5  data rows 4
   record 1  fields 3
   [0] len  2  id
   [1] len  5  value
   [2] len  4  case
   record 2  fields 3
   [0] len  1  1
   [1] len  9  Doe, John
   [2] len  5  comma
   record 3  fields 3
   [0] len  1  2
   [1] len 13  She said "hi"
   [2] len 13  doubled quote
   record 4  fields 3
   [0] len  1  3
   [1] len 17  line one<LF>line two
   [2] len  7  newline
   record 5  fields 3
   [0] len  1  4
   [1] len 13  a, "b", and<LF>c
   [2] len  9  all three

   Record 3 field 1 is 13 characters: the two source quotes collapsed to one. Records 4 and 5 hold a line break and still report three fields.

3. 3

   Step 3.

   Count the rows twice, by line and by record.

   wc -l quoting.csv; node rfc4180.mjs quoting.csv count
   

   7 quoting.csv
   quoting.csv  LF bytes 7  records 5  data rows 4

   wc -l counts LF bytes and reports 7. The parse reports 4 data rows. The other two lines are breaks inside fields.

4. 4

   Step 4.

   Parse the file holding one row in two spellings.

   node rfc4180.mjs escape.csv
   

   escape.csv  LF bytes 3  records 3  data rows 2
   record 1  fields 3
   [0] len  2  id
   [1] len  5  value
   [2] len  4  case
   record 2  fields 3
   [0] len  1  1
   [1] len 17  Doe, "Jack", John
   [2] len  7  doubled
   record 3  fields 4
   [0] len  1  2
   [1] len 12  Doe, \Jack\"
   [2] len  6   John"
   [3] len  9  backslash
   note: record 3: text after the closing quote, kept as text
   note: record 3: a quote inside an unquoted field, kept as text
   note: record 3: a quote inside an unquoted field, kept as text

   The doubled row returned the value whole, 17 characters in one field. The backslash row returned four, split at the comma the writer believed was protected. Nothing errored.

5. 5

   Step 5.

   Parse the four shapes that make quoting ambiguous.

   node rfc4180.mjs edge.csv
   

   edge.csv  LF bytes 8  records 6  data rows 5
   record 1  fields 3
   [0] len  2  id
   [1] len  5  value
   [2] len  4  case
   record 2  fields 3
   [0] len  1  1
   [1] len 16   "leading space"
   [2] len 22  space before the quote
   record 3  fields 3
   [0] len  1  2
   [1] len  4  abcd
   [2] len 13  partly quoted
   record 4  fields 3
   [0] len  1  3
   [1] len  9  lf<LF>inside
   [2] len 11  lf in field
   record 5  fields 3
   [0] len  1  4
   [1] len 12  crlf<CR><LF>inside
   [2] len 13  crlf in field
   record 6  fields 2
   [0] len  1  5
   [1] len 17  unterminated,eof<LF>
   note: record 2: a quote inside an unquoted field, kept as text
   note: record 2: a quote inside an unquoted field, kept as text
   note: record 3: text after the closing quote, kept as text
   note: record 6: the file ended inside a quoted field

   One space before the opening quote made both quotes data, 16 characters against 13. The half quoted field lost its quotes and returned abcd. CRLF survived inside a field as two characters. The unterminated field ran to end of file, leaving that record two fields wide.

6. 6

   Step 6.

   Compare the two readers over all three fixtures.

   for f in quoting.csv escape.csv edge.csv; do diff --strip-trailing-cr <(node rfc4180.mjs $f | grep -v 'note:\|LF bytes') <(python pycsv.py $f) > /dev/null && echo "$f  two parsers identical"; done
   

   quoting.csv  two parsers identical
   escape.csv  two parsers identical
   edge.csv  two parsers identical

   A parser agreeing with itself proves nothing, so this one is checked against Python's csv module. Every field matches, repairs included. Both are one implementation each, not the format.

7. 7

   Step 7.

   Run the second reader again with one keyword changed.

   python pycsv.py edge.csv strict
   

   record 1  fields 3
   [0] len  2  id
   [1] len  5  value
   [2] len  4  case
   record 2  fields 3
   [0] len  1  1
   [1] len 16   "leading space"
   [2] len 22  space before the quote
   record 3  csv.Error: ',' expected after '"'

   strict=True is the same reader refusing to repair. It stops at the half quoted record and never reaches the rows below it.

8. 8

   Step 8.

   Read the conforming file as a spreadsheet does.

   sh cells.sh calc/quoting.html
   

   row
   cell: id
   cell: value
   cell: case
   row
   cell: 1
   cell: Doe, John
   cell: comma
   row
   cell: 2
   cell: She said "hi"
   cell: doubled quote
   row
   cell: 3
   cell: line one[BR]line two
   cell: newline
   row
   cell: 4
   cell: a, "b", and[BR]c
   cell: all three

   Three columns, five rows, the shape both parsers reported. The doubled quotes display as one pair, and the line break sits inside one cell, marked [BR].

9. 9

   Step 9.

   Read the backslash file the same way.

   sh cells.sh calc/escape.html
   

   row
   cell: id
   cell: value
   cell: case
   cell: [BR]
   row
   cell: 1
   cell: Doe, "Jack", John
   cell: doubled
   cell: [BR]
   row
   cell: 2
   cell: Doe, \"Jack\
   cell:  John"
   cell: backslash

   That row splits into four cells here too, and Calc pads the others with an empty fourth cell. A reviewer sees a stray column and two halves of a name.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | A 13 character field from a source holding 4 quote bytes | The doubling collapsed, as items 6 and 7 of RFC 4180 require | Nothing. This is the conforming case and it is what your writer should emit. | | A row with more fields than its neighbours | A quote ended a field early, so a delimiter that was data became a separator | Read the raw bytes of that row. Step 4 and step 5 are the two ways it happens. | | A backslash in a parsed value | The writer used a JSON escape in a format that has none | Fix the writer. The reader is correct, and a consumer would have to know which convention produced the file. | | wc -l and the record count disagree | At least one field holds a line break | Count with a parser. The difference is the number of breaks inside quoted fields. | | Quotes present in the parsed value | The field did not start with a quote, so nothing was enclosing | Look for a space or any stray byte between the delimiter and the quote. | | A parse that stops with an error | The reader is in strict mode and the file is malformed | Keep it that way in the import path. Step 7 is the flag that produced it. | | A spreadsheet with an empty last column on most rows | One row has more fields and the sheet was padded to match | The row with the extra field is the defect. The empty column is the symptom. |

Common mistakes

Thresholds

What to check next

• How to count rows in a csv file: counting done properly, after step 3.
• How to check the delimiter of a csv file: the separator decides what needs quoting.
• How to check formula injection in a csv export: a quoted field a spreadsheet runs.
• How to check the encoding of a CSV file: the bytes behind a stray character.
• How to test csv import validation: where step 7's flag belongs.

FAQ

How to escape double quotes in csv?

Write the quote twice and enclose the field in quotes. Step 2 measured it: She said ""hi"" returned 13 characters carrying one quote pair. A backslash is not an escape here, and step 4 shows it costing a field.

What is a csv text qualifier?

The character that encloses a field. Tools with a configurable one call it the text qualifier. RFC 4180 names only the double quote, which all three readers here assumed.

Can a csv cell contain a newline?

Yes, inside a quoted field. Step 2 returned one field holding line one, a line break and line two, and step 8 shows Calc rendering that as a single cell.

Why does one exported row have an extra column?

A quote closed its field early, so a delimiter that should have been data became a separator. Step 4 reproduces it from the backslash spelling, step 5 from a half quoted field.

Verified