How to Preserve Leading Zeros in CSV and Excel Imports

A stock code written 00422 reaches an import as one of two values.

00422   →   00422     five characters, all of them still there
00422   →   422       read as a number somewhere along the way

Only one of those two can be repaired by better parsing. The other one lost its zeros before the file was handed over, and no reader recovers them from the value alone. Search the problem and the answers arrive from inside the spreadsheet window, telling you to format a column as text before the file is opened. That stops the second line from happening on your own screen, and does nothing for a file that arrives on it already.

This is a builders' merchant stock export, 184 rows, shipped as a CSV and as a workbook of the same sheet.

1Stock code,Barcode,Bin,Description,Cost centre,Pack qty200401,05012340000000,0123,Copper pipe 15mm x 3m,0115,30⋮6 rows not shown9422,05012340000959,0031,Push-fit tee 15mm,0115,28⋮4 rows not shown1400437,5012340001644,0179,Copper pipe 22mm x 3m,0080,27⋮5 rows not shown2000455,05012340002466,0155,Solvent cement 250ml,'0075,22⋮5 rows not shown2600473,05012340003288,0152,PTFE tape 12mm x 12m,0115,7⋮4 rows not shown3100488,05012340003973,0212,Isolating valve 15mm,"=""0115""",20⋮4 rows not shown3600503,05012340004658,42,Backnut 1/2 inch,0080,5⋮5 rows not shown4200422,05012340005480,0160,Push-fit tee 15mm,0080,31⋮10 rows not shown5300554,05012340006987,0041,Compression stop end 28mm,,20⋮131 rows not shown18500950,05012340025071,0068,Solder ring coupler 28mm,0075,42

Line 2 is the file behaving. Line 9 holds a stock code that is three characters where every other one is five. Line 14 holds a barcode one digit short. Line 20 carries an apostrophe in front of the padding, line 31 wraps it in a formula, line 36 drops the bin down to two digits, and line 42 is the same item as line 9 under a code that still has its zeros.

Where the zeros go

Of the three moments that can take them, only the last one belongs to your code.

Microsoft documents the first. Excel "automatically removes leading zeros, and converts large numbers to scientific notation, like 1.23E+15, in order to allow formulas and math operations to work on them". A system exporting through a spreadsheet engine inherits that.

The second is the file being opened between the export and you. Opening a CSV directly "uses the current default data format settings to interpret how to import each column of data", so 00422 becomes the number 422 on screen. Save that workbook back to CSV and the bytes now hold 422.

The third is your own reader, and it is the easiest to rule out. Our CSV parser runs PapaParse with dynamicTyping left at its default of false, which means every field comes back as a string and a padded value stays padded.

There is a thread on Microsoft's own support site where a person with "part numbers that are numbers stored as text with leading zeros" reports that the zeros disappear when they save as CSV. They tried the text format, the Text to Columns wizard, and a =LEFT(A2,LEN(A2)) formula.

Two different files can produce that report. One holds text cells and writes the padding into the CSV, where it comes back the moment anything other than a spreadsheet opens it. The other lost the zeros before the Text format was applied, because that format "will not change numbers that have already been entered". Nothing on screen separates the two, and the second one is the only one that needs the export fixed. Telling them apart starts inside the cell.

What a spreadsheet holds under what you see

Microsoft's own worked example for a postal code is "00123 with custom number format 00000". The format is what puts the zeros on screen, and the value under it is the number 123. The padding is a rendering instruction.

That gives a workbook two answers per cell, and picking the wrong one throws the padding away. Reading the same file with SheetJS, the library we use for workbooks, shows both.

A2   t=n   v=401              w="00401"
A9   t=n   v=422              w="422"
A26  t=s   v="00473"          w="00473"
B2   t=n   v=5012340000000    w="05012340000000"
B14  t=n   v=5012340001644    w="5.01234E+12"
C36  t=n   v=42               w="42"

v is the raw value, w is the formatted text, and t is the cell type, where n is a number and s is a string. A2 and B2 are numbers wearing a padded format. A9 and C36 are numbers with nothing to pad them, which is what a lost zero looks like from the inside. A26 is the one cell the exporting system wrote as text.

Updog Importer reads w, the text the person saw in the spreadsheet, and falls back to v only where there is no formatted text. So A2 arrives as 00401 and A9 arrives as 422, which is the honest answer for both.

B14 is the exception written into the reader. Its format is General, and a General number that long renders as 5.01234E+12, so the displayed text there would keep six of its thirteen digits. Where the formatted text carries an exponent and the raw value is finite, the raw value wins. B14 arrives as 5012340001644.

Run the CSV and the workbook through the importer and the rows come back the same, down to the apostrophe on line 20 and the formula wrapper on line 31. The one difference is line 53's empty cost centre, which the CSV gives as an empty string and the workbook as null. The workbook carries more information about its cells, and none of it reaches the values. The type waiting on the other side decides what the value becomes.

Which columns are text

A field is a number when someone will do arithmetic on it. A quantity gets added up, and a stock code gets looked up.

Digits alone decide nothing. GS1 makes that visible inside one organisation's own standards. In GS1 XML a trade item number "can only contain 14 numeric characters. Thus, whether the actual number is 13, 12 or 8 digits long, it has to be right justified and filled with zeroes up to 14 digits". In GS1 EANCOM the same identifier is declared n..14 and "all the leading zeroes in the GTIN number have to be suppressed". One identifier, two documents from one body, and the leading zero is required by one and forbidden by the other.

The width belongs to whoever owns the field. Your schema is where it gets written down.

What a number column does to a padded value

A number column leaves the padding alone and then flags every row that has it.

The importer canonicalises a number column into a form JavaScript can parse, and the grammar it works to allows a leading zero only where that zero is the whole integer part. 00401 fails it, and a value that fails comes back exactly as it arrived. The padding survives the normalizer.

The number validator that runs next rejects it.

line 2 · Stock code · "00401" · Invalid number
line 3 · Stock code · "00404" · Invalid number
line 4 · Stock code · "00407" · Invalid number

Measured over this file, a { type: "number" } rule on stockCode flags 183 of 184 rows. The row it passes is line 9, the one whose zeros are already gone. The grid also refuses to group a value like that into 00,123, because grouping a flagged identifier would corrupt the thing the person has to fix.

183 flagged rows are the visible version of this mistake. A column that renumbers every item in the file and reports nothing is the version nobody catches.

How the loss becomes visible

A width is a rule, so write it as one.

{
  id: "stockCode",
  title: "Stock code",
  validators: [
    { type: "required" },
    { type: "regex", pattern: "^\\d{5}$", message: "Five digits" },
  ],
}

^\d{5}$ accepts five digits and nothing else. A padded value passes. A value that lost its padding is three or four characters long and lands on screen with its line number and its own message.

Over the 184 rows, with a width rule on each of the four text columns, five cells stop.

line 9   Stock code    "422"             Five digits
line 14  Barcode       "5012340001644"   Fourteen digits
line 20  Cost centre   "'0075"           Four digits
line 31  Cost centre   "=\"0115\""       Four digits
line 36  Bin           "42"              Four digits

Five out of 184, each one a cell where something took the padding away, and each one now sitting in the grid in front of a person who knows what the code should be. Line 53's cost centre is empty and passes, because every built-in rule except required lets an empty value through.

When a zero can be put back

Three of those five are a character or two short of a width that never changes, and that is enough to rebuild them.

const pad = (width: number) => {
  return (value: unknown): unknown => {
    const text = String(value ?? "").trim();
    return /^\d+$/.test(text) && text.length < width
      ? text.padStart(width, "0")
      : value;
  };
};

transformer runs on the way into the store, so the stored value is the padded one and everything downstream sees it. The guard matters more than the padding. A value holding anything other than digits comes back untouched, so '0075 and ="0115" reach a person exactly as they arrived.

With pad on the three fixed-width columns, the same file fails two width rules instead of five, and both are on the column that has no transformer. The barcode repair follows GS1's own rule, right justified and filled with zeroes up to fourteen digits.

The padded file also reports a duplicate.

line 9    stockCode "422"     →   "00422"
line 42   stockCode "00422"
unique    "00422" appears on lines 9 and 42

Line 9 and line 42 are one item. Under the codes as they arrived they were two keys, and { type: "unique" } had nothing to report. Putting the zeros back made the duplicate reachable.

What padding cannot decide

Nothing in the importer knows how wide your codes are. There is no detector that reads a column and concludes it should be five digits. The width comes from your schema or it does not exist, and pad(5) over a numbering scheme that runs from four digits to six invents a zero and hands you a wrong code that validates.

The tail is worse than the head. Excel "has a maximum precision of 15 significant digits, which means that for any number containing 16 or more digits, such as a credit card number, any numbers past the 15th digit are rounded down to zero". A short value at least tells you how many characters are missing. Sixteen digits with three zeros on the end are still sixteen digits, so a width rule waves them through and the account they name does not exist. That one belongs on the wider list of things a CSV import gets wrong.

An apostrophe in front of a value is a convention that lives in a spreadsheet window. Written into a CSV it is a character like any other, and '0075 reaches the schema as five characters. Same for ="0115", which is an export trying to protect its own padding from the next spreadsheet that opens the file. Both stay flagged on purpose.

And the whole defence rests on the file having kept its zeros. When the export that produced it already dropped them, the width rule tells you which rows are affected and a person tells you what they should be. Fixing the export is the only thing that stops it happening again.

What you write

Four text columns with a width rule, three of them repaired on the way in, and one real number.

import type { DataEditorColumn } from "@updog/data-editor";

export const columns: DataEditorColumn[] = [
  {
    id: "stockCode",
    title: "Stock code",
    transformer: pad(5),
    validators: [
      { type: "required" },
      { type: "regex", pattern: "^\\d{5}$", message: "Five digits" },
      { type: "unique" },
    ],
  },
  {
    id: "barcode",
    title: "Barcode",
    transformer: pad(14),
    validators: [{ type: "regex", pattern: "^\\d{14}$", message: "Fourteen digits" }],
  },
  {
    id: "binRef",
    title: "Bin",
    transformer: pad(4),
    validators: [{ type: "regex", pattern: "^\\d{4}$", message: "Four digits" }],
  },
  { id: "description", title: "Description" },
  {
    id: "costCentre",
    title: "Cost centre",
    validators: [{ type: "regex", pattern: "^\\d{4}$", message: "Four digits" }],
  },
  {
    id: "packQty",
    title: "Pack qty",
    editor: { type: "number" },
    validators: [{ type: "number", min: 1 }],
  },
];

transformer runs first, then the validators run against what it produced, then unique runs against the stored value. Which of the file's headers becomes each of these columns is settled one step earlier, in column matching, and the same shape of problem in a date column is a file that cannot say what order it is written in.

A leading zero is the difference between finding a record and creating a second one. Both files here still carried theirs. Keeping them took a column that was never told to add anything up.