Csv scientific notation

Parse the export as strings, then compare each field with what a spreadsheet stores and shows. Run node numbers.mjs detect saved/ids.csv. On the file below it named two fields: a 16 digit card number, and a 19 digit id one save had rewritten to 1,23456789012346E+018.

Why check this

Run this on any export carrying an identifier, at release sign-off and after a change to the writer. An identifier is a digit string, not a quantity, and no CSV says so.

Two losses hide behind one symptom, and a tester who fixes one ships the other. The failure this prevents is an analyst who opens the export, saves it, and returns an id reading 1234567890123460096. Steps 4 and 6 reproduce it, with no error anywhere.

Prerequisites

• Node 22 for the two programs below.
• LibreOffice at C:\Program Files\LibreOffice\program\soffice.com, exported as SOFFICE. It runs a Ukrainian UI locale, LCID 1058, so its exponent strings carry a decimal comma.
• A private profile directory, exported as LOPROFILE, so concurrent conversions stay independent.
• The separator: How to check the delimiter of a csv file. The front of the string: How to check leading zeros in a csv export.
• The range of exact integers: MDN, Number.MAX_SAFE_INTEGER.

make-fixtures.mjs writes four files.

import { writeFileSync } from 'node:fs';

const LF = String.fromCharCode(10);
const Q = String.fromCharCode(34);
const APOS = String.fromCharCode(39);

// Six identifiers a tester meets in an export, and two rows of plain text in
// the same column. None of these is a quantity and none is ever added up.
const header = ['kind', 'value'];
const rows = [
  ['card', '4111111111111111'],
  ['snowflake', '1234567890123456789'],
  ['imei', '490154203237518'],
  ['isbn', '155860832X'],
  ['phone', '+14155552671'],
  ['ref', 'AB-1001'],
  ['ref', 'AB-1002'],
];

const csv = (head, body) => [head.join(','), ...body.map((r) => r.join(','))].join(LF) + LF;

writeFileSync('ids.csv', csv(header, rows), 'latin1');

// Fix 1: quote every field, header included.
const quote = (v) => Q + v + Q;
writeFileSync('ids-quoted.csv', csv(header.map(quote), rows.map((r) => r.map(quote))), 'latin1');

// Fix 2: prefix each value with an apostrophe.
writeFileSync('ids-apostrophe.csv', csv(header, rows.map(([k, v]) => [k, APOS + v])), 'latin1');

// Four integers around the safe-integer boundary, to find where the reader
// stops printing digits.
writeFileSync(
  'boundary.csv',
  csv(['label', 'value'], [
    ['15 digits', '923456789123456'],
    ['max safe', '9007199254740991'],
    ['max safe + 1', '9007199254740992'],
    ['card 16 digits', '4111111111111111'],
  ]),
  'latin1'
);

console.log('wrote ids.csv ids-quoted.csv ids-apostrophe.csv boundary.csv');

numbers.mjs measures one file, or two.

import { readFileSync } from 'node:fs';

const LF = String.fromCharCode(10);
const CR = String.fromCharCode(13);
const Q = String.fromCharCode(34);
const COMMA = String.fromCharCode(44);
const pad = (v, w) => String(v).padEnd(w);

/** Digit strings only. Anything else is not a candidate for a numeric parse. */
const allDigits = (s) => s.length > 0 && [...s].every((c) => c >= '0' && c <= '9');

/** First position where two strings differ, 1 based, 0 when they are equal. */
function firstDiff(a, b) {
  const n = Math.min(a.length, b.length);
  for (let i = 0; i < n; i += 1) if (a[i] !== b[i]) return i + 1;
  return a.length === b.length ? 0 : n + 1;
}

/** Minimal RFC 4180 split, enough for two columns and no embedded terminator. */
function rowsOf(file) {
  return readFileSync(file, 'latin1')
    .split(LF)
    .map((l) => (l.endsWith(CR) ? l.slice(0, -1) : l))
    .filter((l) => l.length > 0)
    .map((l) => {
      const out = [];
      let field = '';
      let quoted = false;
      for (let i = 0; i < l.length; i += 1) {
        const c = l[i];
        if (quoted) {
          if (c === Q && l[i + 1] === Q) { field += Q; i += 1; }
          else if (c === Q) quoted = false;
          else field += c;
        } else if (c === Q && field === '') quoted = true;
        else if (c === COMMA) { out.push(field); field = ''; }
        else field += c;
      }
      out.push(field);
      return out;
    });
}

/** The exponent string this locale writes, read back as the integer it denotes. */
function fromLocaleExponent(s) {
  const dot = s.split(COMMA).join('.');
  const n = Number(dot);
  return Number.isNaN(n) ? null : BigInt(n).toString();
}

const mode = process.argv[2];

if (mode === 'limits') {
  const id = '1234567890123456789';
  console.log('Number.MAX_SAFE_INTEGER   ' + Number.MAX_SAFE_INTEGER);
  console.log('digits in it              ' + String(Number.MAX_SAFE_INTEGER).length);
  console.log('source digits             ' + id);
  console.log('Number(source)            ' + String(Number(id)));
  console.log('BigInt(source)            ' + String(BigInt(id)));
  console.log('the double, exactly       ' + String(BigInt(Number(id))));
  console.log('first digit that changed  ' + firstDiff(id, String(BigInt(Number(id)))));
  console.log('Number(source) === source ' + (String(Number(id)) === id));
  console.log('isSafeInteger             ' + Number.isSafeInteger(Number(id)));
} else if (mode === 'json') {
  const id = '1234567890123456789';
  const payload = '{"id":' + id + ',"idText":"' + id + '"}';
  const parsed = JSON.parse(payload);
  console.log('payload            ' + payload);
  console.log('parsed.id          ' + String(parsed.id));
  console.log('parsed.idText      ' + parsed.idText);
  console.log('re-serialised      ' + JSON.stringify(parsed));
  console.log('id survived        ' + (String(parsed.id) === id));
  console.log('idText survived    ' + (parsed.idText === id));
  const kept = JSON.parse(payload, (k, v, ctx) => (k === 'id' ? ctx.source : v));
  console.log('reviver ctx.source ' + kept.id);
  console.log('reviver survived   ' + (kept.id === id));
} else if (mode === 'scan') {
  const rows = rowsOf(process.argv[3]).slice(1);
  console.log(pad('kind', 11) + pad('field the parser returned', 26) + pad('all digits', 12) + pad('through Number()', 21) + 'round trips');
  for (const [kind, value] of rows) {
    const num = Number(value);
    const back = Number.isNaN(num) ? 'NaN' : String(num);
    console.log(
      pad(kind, 11) + pad(value, 26) + pad(allDigits(value) ? 'yes' : 'no', 12) +
      pad(back, 21) + (back === value ? 'yes' : 'no')
    );
  }
} else if (mode === 'compare') {
  const src = rowsOf(process.argv[3]).slice(1);
  const out = rowsOf(process.argv[4]).slice(1);
  console.log(pad('kind', 11) + pad('before', 21) + pad('after the save', 24) + 'same');
  for (const [i, [kind, before]] of src.entries()) {
    const after = out[i][1];
    console.log(pad(kind, 11) + pad(before, 21) + pad(after, 24) + (after === before ? 'yes' : 'no'));
  }
  const changed = src.filter((r, i) => out[i][1] !== r[1]);
  console.log('fields changed by the save: ' + changed.length + ' of ' + src.length);
  for (const [, before] of changed) {
    const after = out[src.findIndex((r) => r[1] === before)][1];
    const asInt = fromLocaleExponent(after);
    if (asInt !== null && allDigits(before)) {
      console.log('  ' + before + ' now denotes ' + asInt + ', first digit changed at ' + firstDiff(before, asInt));
    }
  }
} else if (mode === 'detect') {
  const rows = rowsOf(process.argv[3]);
  const header = rows[0];
  let found = 0;
  let fields = 0;
  console.log(pad('row', 5) + pad('column', 8) + pad('value', 24) + 'why');
  for (const [r, row] of rows.slice(1).entries()) {
    for (const [c, raw] of row.entries()) {
      fields += 1;
      const v = raw.trim();
      const reasons = [];
      if (allDigits(v) && v.length > 15) reasons.push('digits only, length ' + v.length + ' over 15');
      const e = v.toUpperCase().indexOf('E');
      if (e > 0 && e < v.length - 1 && (v[e + 1] === '+' || v[e + 1] === '-' || allDigits(v[e + 1]))) {
        const head = v.slice(0, e).split(COMMA).join('').split('.').join('');
        const tail = v.slice(e + 1).replace('+', '').replace('-', '');
        if (allDigits(head) && allDigits(tail)) reasons.push('already in exponent form');
      }
      if (reasons.length > 0) {
        found += 1;
        console.log(pad(r + 2, 5) + pad(header[c], 8) + pad(v, 24) + reasons.join('; '));
      }
    }
  }
  console.log('fields at risk: ' + found + ' of ' + fields);
}

Write the fixtures, set the variables.

node make-fixtures.mjs && export SOFFICE="/c/Program Files/LibreOffice/program/soffice.com" LOPROFILE="-env:UserInstallation=file:///$(pwd -W)/loprofile"

Steps

1. 1

   Step 1.

   Put every field through a numeric parse.

   node numbers.mjs scan ids.csv
   

   kind       field the parser returned all digits  through Number()     round trips
   card       4111111111111111          yes         4111111111111111     yes
   snowflake  1234567890123456789       yes         1234567890123456800  no
   imei       490154203237518           yes         490154203237518      yes
   isbn       155860832X                no          NaN                  no
   phone      +14155552671              no          14155552671          no
   ref        AB-1001                   no          NaN                  no
   ref        AB-1002                   no          NaN                  no

   The file holds all seven values. A parse rewrites the snowflake id and drops the phone's plus. Length is not the test: the IMEI's 15 digits survive, the id's 19 do not.

2. 2

   Step 2.

   Find where that parse stops being lossless.

   node numbers.mjs limits
   

   Number.MAX_SAFE_INTEGER   9007199254740991
   digits in it              16
   source digits             1234567890123456789
   Number(source)            1234567890123456800
   BigInt(source)            1234567890123456789
   the double, exactly       1234567890123456768
   first digit that changed  18
   Number(source) === source false
   isSafeInteger             false

   Every integer to 9007199254740991 fits a double; above it some do not. The id lands on a neighbouring double, 1234567890123456768, printing as 1234567890123456800. BigInt keeps the digits. That is value loss, arithmetic rather than rendering.

3. 3

   Step 3.

   Send the id through a JSON round trip.

   node numbers.mjs json
   

   payload            {"id":1234567890123456789,"idText":"1234567890123456789"}
   parsed.id          1234567890123456800
   parsed.idText      1234567890123456789
   re-serialised      {"id":1234567890123456800,"idText":"1234567890123456789"}
   id survived        false
   idText survived    true
   reviver ctx.source 1234567890123456789
   reviver survived   true

   The bare number is a double before any code sees it, so re-serialising writes wrong digits. The quoted twin survives, and so does the reviver's ctx.source.

4. 4

   Step 4.

   Read what each cell shows.

   "$SOFFICE" "$LOPROFILE" --headless --convert-to html --outdir out ids.csv >/dev/null 2>&1 && grep -o '<td[^>]*>[^<]*</td>' out/ids.html | sed 's/ data-sheets-value="[^"]*"//'
   

   <td height="21" align="left">kind</td>
   <td align="left">value</td>
   <td height="21" align="left">card</td>
   <td align="right" sdval="4111111111111111" sdnum="1058;">4111111111111111</td>
   <td height="21" align="left">snowflake</td>
   <td align="right" sdval="1,23456789012346E+018" sdnum="1058;">1,23456789012346E+018</td>
   <td height="21" align="left">imei</td>
   <td align="right" sdval="490154203237518" sdnum="1058;">490154203237518</td>
   <td height="21" align="left">isbn</td>
   <td align="left">155860832X</td>
   <td height="21" align="left">phone</td>
   <td align="right" sdval="14155552671" sdnum="1058;">14155552671</td>
   <td height="21" align="left">ref</td>
   <td align="left">AB-1001</td>
   <td height="21" align="left">ref</td>
   <td align="left">AB-1002</td>

   The text node is what the cell shows, sdval what Calc stored. Four cells stayed text, AB-1001 among them: type is decided per cell. The phone lost its plus. sdnum="1058;" is the Ukrainian locale.

5. 5

   Step 5.

   Find where the reader stops printing digits.

   "$SOFFICE" "$LOPROFILE" --headless --convert-to html --outdir out boundary.csv >/dev/null 2>&1 && grep -o '<td[^>]*>[^<]*</td>' out/boundary.html | sed 's/ data-sheets-value="[^"]*"//'
   

   <td height="21" align="left">label</td>
   <td align="left">value</td>
   <td height="21" align="left">15 digits</td>
   <td align="right" sdval="923456789123456" sdnum="1058;">923456789123456</td>
   <td height="21" align="left">max safe</td>
   <td align="right" sdval="9007199254740991" sdnum="1058;">9007199254740991</td>
   <td height="21" align="left">max safe + 1</td>
   <td align="right" sdval="9,00719925474099E+015" sdnum="1058;">9,00719925474099E+015</td>
   <td height="21" align="left">card 16 digits</td>
   <td align="right" sdval="4111111111111111" sdnum="1058;">4111111111111111</td>

   Two 16 digit values, two renderings. The switch is not digit count: it falls between 9007199254740991 and 9007199254740992, the boundary step 2 measured. The exponent starts where the double stops holding every integer.

6. 6

   Step 6.

   Save the sheet back to CSV.

   "$SOFFICE" "$LOPROFILE" --headless --convert-to csv --outdir saved ids.csv >/dev/null 2>&1 && node numbers.mjs compare ids.csv saved/ids.csv
   

   kind       before               after the save          same
   card       4111111111111111     4111111111111111        yes
   snowflake  1234567890123456789  1,23456789012346E+018   no
   imei       490154203237518      490154203237518         yes
   isbn       155860832X           155860832X              yes
   phone      +14155552671         14155552671             no
   ref        AB-1001              AB-1001                 yes
   ref        AB-1002              AB-1002                 yes
   fields changed by the save: 2 of 7
   1234567890123456789 now denotes 1234567890123460096, first digit changed at 15

   The before column is read after step 4, so the display conversion left the source bytes alone. This step writes the rendering into the file. The exponent denotes 1234567890123460096, wrong from digit 15; the double was wrong from 18.

7. 7

   Step 7.

   Quote every field and read again.

   "$SOFFICE" "$LOPROFILE" --headless --convert-to html --outdir out ids-quoted.csv >/dev/null 2>&1 && grep -o '<td[^>]*>[^<]*</td>' out/ids-quoted.html | sed 's/ data-sheets-value="[^"]*"//'
   

   <td height="21" align="left">kind</td>
   <td align="left">value</td>
   <td height="21" align="left">card</td>
   <td align="right" sdval="4111111111111111" sdnum="1058;">4111111111111111</td>
   <td height="21" align="left">snowflake</td>
   <td align="right" sdval="1,23456789012346E+018" sdnum="1058;">1,23456789012346E+018</td>
   <td height="21" align="left">imei</td>
   <td align="right" sdval="490154203237518" sdnum="1058;">490154203237518</td>
   <td height="21" align="left">isbn</td>
   <td align="left">155860832X</td>
   <td height="21" align="left">phone</td>
   <td align="right" sdval="14155552671" sdnum="1058;">14155552671</td>
   <td height="21" align="left">ref</td>
   <td align="left">AB-1001</td>
   <td height="21" align="left">ref</td>
   <td align="left">AB-1002</td>

   Cell for cell identical to step 4, exponent and lost plus included. Quotes mark where a field ends and say nothing about its type.

8. 8

   Step 8.

   Prefix each value with an apostrophe.

   "$SOFFICE" "$LOPROFILE" --headless --convert-to html --outdir out ids-apostrophe.csv >/dev/null 2>&1 && grep -o '<td[^>]*>[^<]*</td>' out/ids-apostrophe.html | sed 's/ data-sheets-value="[^"]*"//'
   

   <td height="21" align="left">kind</td>
   <td align="left">value</td>
   <td height="21" align="left">card</td>
   <td align="left">'4111111111111111</td>
   <td height="21" align="left">snowflake</td>
   <td align="left">'1234567890123456789</td>
   <td height="21" align="left">imei</td>
   <td align="left">'490154203237518</td>
   <td height="21" align="left">isbn</td>
   <td align="left">'155860832X</td>
   <td height="21" align="left">phone</td>
   <td align="left">'+14155552671</td>
   <td height="21" align="left">ref</td>
   <td align="left">'AB-1001</td>
   <td height="21" align="left">ref</td>
   <td align="left">'AB-1002</td>

   Every cell is text now, left aligned, no sdval, every digit present. Read the text nodes: the apostrophe sits inside the cell content, not consumed as a marker.

9. 9

   Step 9.

   Save it and count what changed.

   "$SOFFICE" "$LOPROFILE" --headless --convert-to csv --outdir saved ids-apostrophe.csv >/dev/null 2>&1 && node numbers.mjs compare ids.csv saved/ids-apostrophe.csv
   

   kind       before               after the save          same
   card       4111111111111111     '4111111111111111       no
   snowflake  1234567890123456789  '1234567890123456789    no
   imei       490154203237518      '490154203237518        no
   isbn       155860832X           '155860832X             no
   phone      +14155552671         '+14155552671           no
   ref        AB-1001              'AB-1001                no
   ref        AB-1002              'AB-1002                no
   fields changed by the save: 7 of 7

   Seven of seven keys gained a character. The display is right and every value is a different string from the one the system issued, so a lookup misses. This trades a visible loss for an invisible one.

10. 10

    Step 10.

    Declare the column as text on import.

    "$SOFFICE" "$LOPROFILE" --headless --infilter="Text - txt - csv (StarCalc):44,34,76,1,1/1/2/2" --convert-to csv --outdir astext ids.csv >/dev/null 2>&1 && node numbers.mjs compare ids.csv astext/ids.csv
    

    kind       before               after the save          same
    card       4111111111111111     4111111111111111        yes
    snowflake  1234567890123456789  1234567890123456789     yes
    imei       490154203237518      490154203237518         yes
    isbn       155860832X           155860832X              yes
    phone      +14155552671         +14155552671            yes
    ref        AB-1001              AB-1001                 yes
    ref        AB-1002              AB-1002                 yes
    fields changed by the save: 0 of 7

    Zero of seven. In that token 44 is the comma, 34 the quote, 76 the UTF-8 charset, 1 the first row, and 1/1/2/2 types column 1 standard and column 2 text.

11. 11

    Step 11.

    Scan the export before anyone opens it.

    node numbers.mjs detect saved/ids.csv
    

    row  column  value                   why
    2    value   4111111111111111        digits only, length 16 over 15
    3    value   1,23456789012346E+018   already in exponent form
    fields at risk: 2 of 14

    Two rules, row and column named. The first flags a digit string over 15 characters, which a reader may render as an exponent. The second flags damage already done. Run it on the export, not on a file a person returns.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | A cell shows an exponent, the file holds digits | Display change only, so far | Fix the reader's column type. Step 10 does it with zero changed fields. | | The saved file holds 1,23456789012346E+018 | The rendering has become the data | Re-export. Step 6 shows 14 correct digits left of 19. | | Number(value) does not equal the source text | The value passed through a double | Carry the field as a string. Step 3 keeps it with ctx.source. | | A digit string of 16 digits, at most 9007199254740991 | Exact in a double, and printed in full by Calc 24.2.3.2 | Carry it as text anyway, and re-measure on the reader you ship. Step 5 is the boundary. | | A phone number lost its leading plus | The reader stored it as a number, and a sign is not part of one | Store dialling codes as text. Step 4 measures the loss. | | Values stayed text next to a converted neighbour | Type is decided per cell, not per column | Never infer a column's type from one sample row. | | Every field gained a leading apostrophe | A display fix was written into the keys | Remove it and set the column type instead. Step 9 counts 7 of 7. |

Common mistakes

Thresholds

What to check next

• How to check leading zeros in a csv export: the same typing, digits lost from the front.
• How to check the delimiter of a csv file: the decimal comma is also a separator candidate.
• How to escape quotes in csv: what quoting does control.
• How to check formula injection in a csv export: the other prefix a reader acts on.

FAQ

Why is my csv converting to scientific notation?

The reader parsed a digit string as a number. Step 5 measures the trigger on LibreOffice Calc 24.2.3.2: 9007199254740991 prints in full, 9007199254740992 prints as 9,00719925474099E+015.

How do I turn off csv scientific notation?

Not in the file. Step 7 quotes every field and the exponent survives. Set the column type on import, as step 10 does with 1/1/2/2, which changed 0 of 7 fields.

Why do long numbers get truncated when a csv is opened?

A double cannot hold every integer above 9007199254740991. Step 2 shows 1234567890123456789 landing on 1234567890123456768, first wrong at digit 18. Excel is not installed on the machine that produced this page, so nothing here describes Excel; the reader measured is LibreOffice Calc 24.2.3.2.

Verified