How to check leading zeros in a csv export

Compare every exported field against the source value by code point. Run node csvzeros.mjs detect source.json export.csv saved/export.csv. Here it reported 0 differ for the export and 5 differ for the same file after a spreadsheet opened and saved it, where 02138 became 2138.

Why check this

Run this on the first export of a release, and again when a ticket says an identifier changed between export and import.

A code made of digits is not a number, and CSV carries no types to say so. The four readers below decided differently about one field.

The failure it prevents is an account number that stops matching. Step 10 turned 000123456789 into 123456789 in the file itself, with no error and no warning.

Prerequisites

• Node 22 for the builder and the detector below.
• LibreOffice headless. This run used LibreOffice Calc 24.2.3.2 on a Ukrainian UI locale, LCID 1058, decimal comma.
• Excel is not installed on the machine that produced this page, so nothing here describes Excel. Check the reader you ship.
• xxd, sed, grep, tr and cygpath from Git Bash, and a scratch directory.
• -env:UserInstallation gives each conversion its own profile, so a second Calc process cannot attach.
• Fixtures are generated, because a tab typed into a shell argument can arrive decoded.

make.mjs writes the fixtures and the source values.

import { writeFileSync } from 'node:fs';

const LF = String.fromCharCode(10);
const DQ = String.fromCharCode(34);
const TAB = String.fromCharCode(9);
const APOS = String.fromCharCode(39);
const q = (s) => DQ + s.split(DQ).join(DQ + DQ) + DQ;

const codes = ['id,code', '1,007'].join(LF) + LF;

const source = [
  { id: '1', kind: 'zip', value: '02138' },
  { id: '2', kind: 'phone', value: '0441234567' },
  { id: '3', kind: 'account', value: '000123456789' },
  { id: '4', kind: 'plus', value: '+4915112345678' },
  { id: '5', kind: 'long', value: '12345678901234567890' },
  { id: '6', kind: 'mixed', value: '0A12' },
];
const exportCsv = ['id,kind,value']
  .concat(source.map((r) => [r.id, r.kind, r.value].join(',')))
  .join(LF) + LF;

const escapes = [
  ['1', 'plain', '007'],
  ['2', 'quoted', q('007')],
  ['3', 'apostrophe', APOS + '007'],
  ['4', 'tab', TAB + '007'],
  ['5', 'space', ' 007'],
  ['6', 'formula', q('="007"')],
  ['7', 'arithmetic', q('=1+1')],
];
const escapesCsv = ['id,form,value']
  .concat(escapes.map((r) => r.join(',')))
  .join(LF) + LF;

writeFileSync('codes.csv', codes, 'utf8');
writeFileSync('export.csv', exportCsv, 'utf8');
writeFileSync('escapes.csv', escapesCsv, 'utf8');
writeFileSync('source.json', JSON.stringify(source, null, 2) + LF, 'utf8');

for (const f of ['codes.csv', 'export.csv', 'escapes.csv']) {
  const t = { 'codes.csv': codes, 'export.csv': exportCsv, 'escapes.csv': escapesCsv }[f];
  console.log(f.padEnd(12) + 'bytes ' + Buffer.byteLength(t, 'utf8'));
}

csvzeros.mjs is the reader. Its detect mode is the deliverable.

import { readFileSync } from 'node:fs';

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

// RFC 4180 reader. It returns the characters a field holds and changes none.
export function parseCsv(text) {
  const rows = [];
  let row = [], field = '', quoted = false, i = 0;
  while (i < text.length) {
    const c = text[i];
    if (quoted) {
      if (c === DQ && text[i + 1] === DQ) { field += DQ; i += 2; continue; }
      if (c === DQ) { quoted = false; i += 1; continue; }
      field += c; i += 1; continue;
    }
    if (c === DQ && field === '') { quoted = true; i += 1; continue; }
    if (c === ',') { row.push(field); field = ''; i += 1; continue; }
    if (c === CR && text[i + 1] === LF) { row.push(field); rows.push(row); row = []; field = ''; i += 2; continue; }
    if (c === LF || c === CR) { row.push(field); rows.push(row); row = []; field = ''; i += 1; continue; }
    field += c; i += 1;
  }
  if (field !== '' || row.length > 0) { row.push(field); rows.push(row); }
  return rows;
}

const cps = (s, max = 8) => [...s].slice(0, max)
  .map((c) => 'U+' + c.codePointAt(0).toString(16).toUpperCase().padStart(4, '0')).join(' ')
  + ([...s].length > max ? ' ...' : '');
const read = (f) => parseCsv(readFileSync(f, 'utf8'));
const pad = (v, w) => String(v).padEnd(w);
const len = (s) => 'len ' + String(s.length).padStart(2) + '  ';

const mode = process.argv[2];

if (mode === 'parse') {
  const rows = read(process.argv[3]);
  const col = rows[0].indexOf(process.argv[4]);
  for (const r of rows.slice(1)) {
    console.log('row ' + r[0] + '  ' + pad(r[1], 11) + len(r[col]) + pad(r[col], 22) + cps(r[col]));
  }
} else if (mode === 'numbers') {
  const rows = read(process.argv[3]);
  const col = rows[0].indexOf('value');
  console.log('kind        parser string          Number()              round trips');
  for (const r of rows.slice(1)) {
    const s = r[col];
    const n = Number(s);
    console.log(pad(r[1], 12) + pad(s, 22) + pad(String(n), 22) + (String(n) === s ? 'yes' : 'no'));
  }
} else if (mode === 'json') {
  for (const literal of ['{"code":007}', '{"code":"007"}']) {
    try {
      const v = JSON.parse(literal).code;
      console.log(pad(literal, 18) + 'parsed  ' + pad(typeof v, 8) + JSON.stringify(v));
    } catch (e) {
      console.log(pad(literal, 18) + 'threw   ' + e.constructor.name + ': ' + e.message);
    }
  }
  const back = JSON.parse(JSON.stringify({ code: Number('007') })).code;
  console.log(pad('Number then JSON', 18) + 'parsed  ' + pad(typeof back, 8) + JSON.stringify(back));
} else if (mode === 'detect') {
  // The deliverable: compare every exported field against the source value.
  const source = JSON.parse(readFileSync(process.argv[3], 'utf8'));
  let total = 0;
  for (const file of process.argv.slice(4)) {
    const rows = read(file);
    const head = rows[0];
    const col = head.indexOf('value');
    let bad = 0;
    for (const [i, want] of source.entries()) {
      const got = rows[i + 1] === undefined ? '' : rows[i + 1][col];
      if (got === want.value) continue;
      bad += 1;
      console.log('row ' + (i + 2) + ' column ' + (col + 1) + ' (' + head[col] + ')  kind ' + want.kind);
      console.log('  source  ' + len(want.value) + pad(want.value, 22) + cps(want.value));
      console.log('  file    ' + len(got) + pad(got, 22) + cps(got));
    }
    console.log(pad(file, 22) + source.length + ' rows compared, ' + bad + ' differ');
    total += bad;
  }
  process.exitCode = total === 0 ? 0 : 1;
}

calc.sh is the spreadsheet, headless. In its show output sdval is the value Calc stored, and stored-as-text marks a cell kept as a string.

#!/bin/sh
# Three ways to hand a CSV to LibreOffice Calc, all headless.
#   sh calc.sh show  f.csv        what Calc displays, one <td> per cell
#   sh calc.sh save  f.csv ...    open and save back as CSV, then print it
#   sh calc.sh text  f.csv        same save, every column typed as text
SOFFICE="/c/Program Files/LibreOffice/program/soffice.com"
DIR=$(cygpath -m "$PWD" 2>/dev/null || echo "$PWD")
PROFILE="-env:UserInstallation=file:///$DIR/loprofile"
TEXTCOLS="Text - txt - csv (StarCalc):44,34,76,1,1/2/2/2/3/2"
MODE="$1"
shift

# Print one saved file with the byte count and the terminators it came back with.
emit() {
  echo "== $1  bytes $(wc -c < "$1")  CR $(tr -cd '\r' < "$1" | wc -c)  LF $(tr -cd '\n' < "$1" | wc -c)"
  cat "$1"
}

case "$MODE" in
  show)
    "$SOFFICE" "$PROFILE" --headless --convert-to html --outdir calc "$1" > /dev/null 2>&1
    sed -e 's/ data-sheets-value="[^"]*"/ stored-as-text/' "calc/$(basename "$1" .csv).html" \
      | grep -o '<td[^>]*>[^<]*'
    ;;
  save)
    "$SOFFICE" "$PROFILE" --headless --convert-to csv --outdir saved "$@" > /dev/null 2>&1
    for f in "$@"; do emit "saved/$(basename "$f")"; done
    ;;
  text)
    MSYS_NO_PATHCONV=1 "$SOFFICE" "$PROFILE" --headless --infilter="$TEXTCOLS" \
      --convert-to csv --outdir typed "$@" > /dev/null 2>&1
    for f in "$@"; do emit "typed/$(basename "$f")"; done
    ;;
esac

Build the fixtures.

node make.mjs

codes.csv   bytes 14
export.csv  bytes 131
escapes.csv bytes 124

Steps

1. 1

   Step 1.

   Read the bytes.

   xxd codes.csv
   

   00000000: 6964 2c63 6f64 650a 312c 3030 370a       id,code.1,007.

   The field is 30 30 37, zeros included. CSV has no types, so nothing marks it as text or number.

2. 2

   Step 2.

   Read them with a CSV parser.

   node csvzeros.mjs parse codes.csv code
   

   row 1  007        len  3  007                   U+0030 U+0030 U+0037

   Three characters in, three out. An RFC 4180 reader returns the field as written and decides nothing.

3. 3

   Step 3.

   Read the same value through JSON.

   node csvzeros.mjs json
   

   {"code":007}      threw   SyntaxError: Unexpected number in JSON at position 9 (line 1 column 10)
   {"code":"007"}    parsed  string  "007"
   Number then JSON  parsed  number  7

   JSON has no number literal with a leading zero, so the value survives only as a string. One Number() call leaves 7.

4. 4

   Step 4.

   Read them with the spreadsheet.

   sh calc.sh show codes.csv
   

   <td height="21" align="left" stored-as-text>id
   <td align="left" stored-as-text>code
   <td height="21" align="right" sdval="1" sdnum="1058;">1
   <td align="right" sdval="7" sdnum="1058;">7

   sdval="7" is the value Calc stored. The bytes, the parser and the JSON string were intact, so this reader is where 007 became 7.

5. 5

   Step 5.

   Print the six kinds the exporter wrote.

   node csvzeros.mjs parse export.csv value
   

   row 1  zip        len  5  02138                 U+0030 U+0032 U+0031 U+0033 U+0038
   row 2  phone      len 10  0441234567            U+0030 U+0034 U+0034 U+0031 U+0032 U+0033 U+0034 U+0035 ...
   row 3  account    len 12  000123456789          U+0030 U+0030 U+0030 U+0031 U+0032 U+0033 U+0034 U+0035 ...
   row 4  plus       len 14  +4915112345678        U+002B U+0034 U+0039 U+0031 U+0035 U+0031 U+0031 U+0032 ...
   row 5  long       len 20  12345678901234567890  U+0031 U+0032 U+0033 U+0034 U+0035 U+0036 U+0037 U+0038 ...
   row 6  mixed      len  4  0A12                  U+0030 U+0041 U+0031 U+0032

   Each value is still the length the source held.

6. 6

   Step 6.

   Put each through Number().

   node csvzeros.mjs numbers export.csv
   

   kind        parser string          Number()              round trips
   zip         02138                 2138                  no
   phone       0441234567            441234567             no
   account     000123456789          123456789             no
   plus        +4915112345678        4915112345678         no
   long        12345678901234567890  12345678901234567000  no
   mixed       0A12                  NaN                   no

   Not one survives. Three lose a zero, the plus sign goes, and the letter gives NaN.

7. 7

   Step 7.

   Show the six to the spreadsheet.

   sh calc.sh show export.csv
   

   <td height="21" align="left" stored-as-text>id
   <td align="left" stored-as-text>kind
   <td align="left" stored-as-text>value
   <td height="21" align="right" sdval="1" sdnum="1058;">1
   <td align="left" stored-as-text>zip
   <td align="right" sdval="2138" sdnum="1058;">2138
   <td height="21" align="right" sdval="2" sdnum="1058;">2
   <td align="left" stored-as-text>phone
   <td align="right" sdval="441234567" sdnum="1058;">441234567
   <td height="21" align="right" sdval="3" sdnum="1058;">3
   <td align="left" stored-as-text>account
   <td align="right" sdval="123456789" sdnum="1058;">123456789
   <td height="21" align="right" sdval="4" sdnum="1058;">4
   <td align="left" stored-as-text>plus
   <td align="right" sdval="4915112345678" sdnum="1058;">4915112345678
   <td height="21" align="right" sdval="5" sdnum="1058;">5
   <td align="left" stored-as-text>long
   <td align="right" sdval="1,23456789012346E+019" sdnum="1058;">1,23456789012346E+019
   <td height="21" align="right" sdval="6" sdnum="1058;">6
   <td align="left" stored-as-text>mixed
   <td align="left" stored-as-text>0A12

   Five of six became numbers, right aligned with an sdval, and only 0A12 stayed a string. The decimal comma is this install's Ukrainian locale.

8. 8

   Step 8.

   Print the seven spellings testers reach for.

   node csvzeros.mjs parse escapes.csv value
   

   row 1  plain      len  3  007                   U+0030 U+0030 U+0037
   row 2  quoted     len  3  007                   U+0030 U+0030 U+0037
   row 3  apostrophe len  4  '007                  U+0027 U+0030 U+0030 U+0037
   row 4  tab        len  4  	007                  U+0009 U+0030 U+0030 U+0037
   row 5  space      len  4   007                  U+0020 U+0030 U+0030 U+0037
   row 6  formula    len  6  ="007"                U+003D U+0022 U+0030 U+0030 U+0037 U+0022
   row 7  arithmetic len  4  =1+1                  U+003D U+0031 U+002B U+0031

   Rows 1 and 2 return the same three characters. Quoting changed the bytes and not the value.

9. 9

   Step 9.

   Show the seven to the spreadsheet.

   sh calc.sh show escapes.csv
   

   <td height="21" align="left" stored-as-text>id
   <td align="left" stored-as-text>form
   <td align="left" stored-as-text>value
   <td height="21" align="right" sdval="1" sdnum="1058;">1
   <td align="left" stored-as-text>plain
   <td align="right" sdval="7" sdnum="1058;">7
   <td height="21" align="right" sdval="2" sdnum="1058;">2
   <td align="left" stored-as-text>quoted
   <td align="right" sdval="7" sdnum="1058;">7
   <td height="21" align="right" sdval="3" sdnum="1058;">3
   <td align="left" stored-as-text>apostrophe
   <td align="left" stored-as-text>'007
   <td height="21" align="right" sdval="4" sdnum="1058;">4
   <td align="left" stored-as-text>tab
   <td align="left" stored-as-text>	007
   <td height="21" align="right" sdval="5" sdnum="1058;">5
   <td align="left" stored-as-text>space
   <td align="right" sdval="7" sdnum="1058;">7
   <td height="21" align="right" sdval="6" sdnum="1058;">6
   <td align="left" stored-as-text>formula
   <td align="left" stored-as-text>007
   <td height="21" align="right" sdval="7" sdnum="1058;">7
   <td align="left" stored-as-text>arithmetic
   <td align="right" sdval="2" sdnum="1058;" data-sheets-formula="=1+1">2

   Quoting and the leading space display 7. The apostrophe and the tab keep their extra character, and data-sheets-formula computes =1+1 into 2.

10. 10

    Step 10.

    Open both files and save them back.

    sh calc.sh save export.csv escapes.csv
    

    == saved/export.csv  bytes 135  CR 7  LF 7
    id,kind,value
    1,zip,2138
    2,phone,441234567
    3,account,123456789
    4,plus,4915112345678
    5,long,"1,23456789012346E+019"
    6,mixed,0A12
    == saved/escapes.csv  bytes 111  CR 8  LF 8
    id,form,value
    1,plain,7
    2,quoted,7
    3,apostrophe,'007
    4,tab,	007
    5,space,7
    6,formula,007
    7,arithmetic,2

    The loss stops being a rendering here. The file holds 2138, and CR 7 says every terminator was rewritten to CRLF.

11. 11

    Step 11.

    Measure what the escape forms became.

    node csvzeros.mjs parse saved/escapes.csv value
    

    row 1  plain      len  1  7                     U+0037
    row 2  quoted     len  1  7                     U+0037
    row 3  apostrophe len  4  '007                  U+0027 U+0030 U+0030 U+0037
    row 4  tab        len  4  	007                  U+0009 U+0030 U+0030 U+0037
    row 5  space      len  1  7                     U+0037
    row 6  formula    len  3  007                   U+0030 U+0030 U+0037
    row 7  arithmetic len  1  2                     U+0032

    Three forms kept the zeros, and two of those now differ from the source by one added character.

12. 12

    Step 12.

    Import again with every column typed as text.

    sh calc.sh text export.csv
    

    == typed/export.csv  bytes 138  CR 7  LF 7
    id,kind,value
    1,zip,02138
    2,phone,0441234567
    3,account,000123456789
    4,plus,+4915112345678
    5,long,12345678901234567890
    6,mixed,0A12

    Column type is set at import, not in the file. 1/2/2/2/3/2 names each column and format 2, text, and all six values return unchanged.

13. 13

    Step 13.

    Run the detector over all three files.

    node csvzeros.mjs detect source.json export.csv saved/export.csv typed/export.csv
    

    export.csv            6 rows compared, 0 differ
    row 2 column 3 (value)  kind zip
    source  len  5  02138                 U+0030 U+0032 U+0031 U+0033 U+0038
    file    len  4  2138                  U+0032 U+0031 U+0033 U+0038
    row 3 column 3 (value)  kind phone
    source  len 10  0441234567            U+0030 U+0034 U+0034 U+0031 U+0032 U+0033 U+0034 U+0035 ...
    file    len  9  441234567             U+0034 U+0034 U+0031 U+0032 U+0033 U+0034 U+0035 U+0036 ...
    row 4 column 3 (value)  kind account
    source  len 12  000123456789          U+0030 U+0030 U+0030 U+0031 U+0032 U+0033 U+0034 U+0035 ...
    file    len  9  123456789             U+0031 U+0032 U+0033 U+0034 U+0035 U+0036 U+0037 U+0038 ...
    row 5 column 3 (value)  kind plus
    source  len 14  +4915112345678        U+002B U+0034 U+0039 U+0031 U+0035 U+0031 U+0031 U+0032 ...
    file    len 13  4915112345678         U+0034 U+0039 U+0031 U+0035 U+0031 U+0031 U+0032 U+0033 ...
    row 6 column 3 (value)  kind long
    source  len 20  12345678901234567890  U+0031 U+0032 U+0033 U+0034 U+0035 U+0036 U+0037 U+0038 ...
    file    len 21  1,23456789012346E+019 U+0031 U+002C U+0032 U+0033 U+0034 U+0035 U+0036 U+0037 ...
    saved/export.csv      6 rows compared, 5 differ
    typed/export.csv      6 rows compared, 0 differ

    Three verdicts from one run. The middle names every damaged row by row, column and code point, and the exit status is 1.

The six kinds, from steps 5, 7 and 10:

| Value in the file | What Calc displayed | What the cell stored | After open and save | | --- | --- | --- | --- | | 02138 | 2138 | sdval="2138" | 2138 | | 0441234567 | 441234567 | sdval="441234567" | 441234567 | | 000123456789 | 123456789 | sdval="123456789" | 123456789 | | +4915112345678 | 4915112345678 | sdval="4915112345678" | 4915112345678 | | 12345678901234567890 | 1,23456789012346E+019 | sdval="1,23456789012346E+019" | "1,23456789012346E+019" | | 0A12 | 0A12 | text, no sdval | 0A12 |

The seven spellings, from steps 8, 9 and 11:

| Spelling in the file | What the parser returns | What Calc displayed | After open and save | | --- | --- | --- | --- | | 007 | 007 | 7 | 7 | | "007" | 007 | 7 | 7 | | '007 | '007, 4 characters | '007 | '007, 4 characters | | tab then 007 | U+0009 then 007 | U+0009 then 007 | U+0009 then 007 | | space then 007 | 4 characters | 7 | 7 | | ="007" | ="007", 6 characters | 007 | 007 | | =1+1 | =1+1 | 2 | 2 |

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | xxd shows the zeros and the spreadsheet does not | The file is correct and the reader is typing the field | Fix the import, not the exporter. Step 12 is the import setting that stops it. | | A cell with sdval and no leading zero | Calc stored a number, so the string is gone from the sheet | Treat the sheet as unusable for that column. Saving it writes the number back, as step 10 does. | | A cell with no sdval | Calc kept the field as text | The value is intact in the sheet. Step 4 contrasts the two in four lines. | | detect reports 0 differ on the export and rows on the saved file | The damage is downstream of your exporter | Attach step 13's output to the ticket. It names the row, the column and the code points. | | A value one character shorter than its source | A leading zero or a leading plus was parsed away | Compare lengths first. Step 13 prints both, so the difference is visible without counting. | | A value longer than its source and holding E+ | The number was too long to display and was rounded | That is a different defect from a lost zero. Follow it in the scientific notation check. | | A value that kept its zeros and still fails comparison | A fix added a character to the data | Read the code points. Step 11 shows U+0027 and U+0009 surviving into the saved file. |

Common mistakes

Thresholds

What to check next

• Csv scientific notation: where the long value went.
• How to check formula injection in a csv export: why the ="007" form is a hazard.
• How to escape quotes in csv: what quoting does do.
• How to count rows in a csv file: the terminator change in step 10.
• How to check the encoding of a CSV file: the other spreadsheet decision.

FAQ

How to keep leading zeros in csv?

Type the column as text at import. Step 12 sets that format and all six values come back unchanged. Nothing inside the file managed that without changing the value.

Why is excel removing leading zeros from csv?

Excel is not installed on the machine that produced this page, so nothing here describes it. The measurements show the file intact and a reader converting the field. Run steps 1, 5 and 13 against the reader you ship.

My csv file is missing leading zeros, where did they go?

Read the file with xxd first. If the bytes hold the zeros, a reader typed the field as a number, and step 10 shows that becoming permanent once it saves.

Does a leading apostrophe keep leading zeros in csv?

Here it kept them and changed the value. Step 11 measures '007 in the saved file as 4 characters beginning U+0027, so comparison against the source fails.

Verified