Blog

Why leading zeros disappear from CSV data

Learn why leading zeros disappear from CSV data, how to check whether the file or spreadsheet changed them, and how to keep IDs intact.

Rachel BennettTools and utilities Tools 4 min read

You open a CSV and find that a code such as 00123 now appears as 123. This often happens when a spreadsheet guesses that a column contains numbers and removes the zeros it considers unnecessary.

First check whether the zeros are missing from the file itself or only from the way an app displays it. The CSV Editor lets you open the file in your browser and inspect the actual values without relying on a spreadsheet's automatic type guesses.

Check the CSV data

  1. Open the CSV Editor and load the CSV file from your device.
  2. Find a row where you expected leading zeros. Check the cell in the affected column, and compare it with the same value in the spreadsheet or app where you noticed the problem.
  3. Look at several examples, not just one. A fixed-width code might always have five digits, while another column may contain IDs with different lengths.
  4. If you can, compare with an untouched copy of the original file. Do not save over your only copy while you are still checking what changed.

A CSV is a plain-text format. It stores characters separated by commas, but it does not say whether a value is a number, a product code, a ZIP code, or an account ID. The app opening it has to guess what each value means.

What the result means

The CSV Editor shows the zeros

The file still contains the original value. The spreadsheet or other app is displaying it as a number, or it may have changed the value in its own copy. Import the CSV with the affected column set to text, rather than opening the file by double-clicking it.

The CSV Editor shows the shorter value too

The zeros have already been removed from this copy of the CSV. A spreadsheet may have converted the value to a number and then saved that shorter value back to the file. You cannot know how many zeros to add unless the ID format has a known, consistent width or you have another source to check.

Zeros appear only in some rows

There may be more than one kind of value in the column, or the IDs may not all have the same length. Do not add zeros to every value just to make the column look even. Check the rules for those IDs first, because a code with the wrong number of digits can point to a different record.

How to keep the zeros

  1. Keep an untouched copy. Duplicate the CSV before importing or editing it. This gives you a source to compare if an app changes values when you save.
  2. Import the file instead of opening it directly. In Excel on Windows 11, use Data › From Text/CSV, then choose Transform Data. In Power Query, select the ID column and set its data type to Text before loading the data. On macOS, use Data › Get Data (Power Query) › Text/CSV where available, then set the column type to Text before loading. Menu names vary across Excel versions.
  3. Check the values before saving. Confirm that codes such as 00123 still have their zeros in the imported data. A cell that merely looks right because of a custom number format may not contain those zeros as part of its value.
  4. Save a new CSV copy. When exporting from a spreadsheet, choose a CSV format and save to a new filename so the original stays unchanged. Reopen the exported copy in the CSV Editor to verify the values survived.
  5. Restore missing zeros only when you know the rule. If every ID is supposed to be exactly five digits, you can restore a value such as 123 to 00123. Use the CSV Editor to edit values if needed, but verify every affected row against the known ID format or a trusted source.

Why changing the cell format may not be enough

There is a difference between the value in a cell and the way a spreadsheet displays it. A number format can show 123 as 00123 without changing the stored value. That may look correct in the spreadsheet, but the exported CSV might contain only 123.

For IDs, codes, postal codes, and other values where every character matters, import the column as text. Text tells the spreadsheet to preserve the value as written, including leading zeros. Use a number type for calculations, not for identifiers that only happen to contain digits.

Adding quotation marks around a CSV value is not a dependable fix. Quotes in CSV mark the boundaries of a field when needed; they do not tell a spreadsheet that the field must be treated as text. The important step is choosing the column type during import.

The CSV Editor processes the file in your browser, and the file stays on your device.

More articles