Keep leading zeros when importing a CSV into Excel

Direct answer

Import the identifier column as Text before any numeric conversion. A code such as 00123 identifies an item; it is not a quantity to add up. Once an import has reduced it to 123, formatting the cell as text cannot tell you how many zeros originally existed.

Work from a copy of the untouched export. In desktop Excel, Microsoft’s documented route uses Data → From Text/CSV, then the query editor to set the relevant column’s data type to Text before loading. Button labels vary by version. This menu procedure is documentation-based; we have not run Excel locally. Microsoft’s import guidance.

A sample where padding gives the wrong answer

Download the original product file. It contains four synthetic products:

Product ID	Product	Quantity
00123	Blue notebook	4
00007	Pencil case	12
01230	Desk tray	2
123	Refill pack	8

The first and last identifiers intentionally differ. A rule that pads every value to five characters would turn 123 into 00123, creating an apparent duplicate between two different products. The correct result preserves all four strings exactly, while quantity remains usable for arithmetic.

Before opening the file in a spreadsheet, inspect it in a plain-text editor. Confirm the source contains 00123 and 123 as separate values. If the original already contains identical codes, the import cannot recover their history: obtain a fresh export from the system that owns the product records.

Import and check the column

1. Create a new workbook and start the text/CSV import from the Data tab. Choose the original file, not a CSV resaved from a damaged workbook.
2. Check that the preview separates product_id, product, and quantity into three columns. A one-column preview is a delimiter issue; solve that before checking data types.
3. Open the transformation editor. Inspect its applied steps for an automatic type conversion. Replace that conversion for product_id with Text, or remove the conversion and set the column to Text while the source strings are still intact. Adding Text after digits have already disappeared does not restore them.
4. Leave quantity numeric, then load the result. Save the working document as a workbook if you need to retain its import configuration.
5. Compare all four IDs against the table above, including the short 123. A sample containing only padded codes would miss this failure.

For a manual entry into an otherwise empty worksheet, preformat the destination column as Text before entering the identifiers. This is a separate workflow from importing an existing file.

Verify the exported result too

A correct-looking worksheet is only one checkpoint. If the next system receives a CSV, save a separate export and inspect its raw text. The receiving system must also treat product_id as text. A CSV does not carry a spreadsheet column’s formatting instructions, and surrounding a field with double quotes does not reliably force spreadsheet text typing.

For this fixture, useful acceptance checks are: four data records; four distinct product IDs; exact lengths 5, 5, 5, 3; and a quantity total of 26. That combination catches both accidental zero removal and a misguided blanket-padding repair.

Limits and testing notes

A display format can be appropriate when a documented specification says every code has a fixed width. It is a poor recovery rule when the width is unknown or mixed, as in this example. Never infer an original identifier from the current appearance alone.

We verified the fixture’s exact strings, lengths, uniqueness, and quantity total with Python’s standard-library CSV reader. That validates the downloadable data, not Excel’s interface or a particular Excel release. The reproducible repository check is python3 scripts/test-import-examples.py.

For values longer than 15 digits, continue with long identifiers and lost precision. If the preview has one column, use identify the CSV delimiter.

Source references

• Microsoft: keeping leading zeros and large numbers
• Python CSV documentation

Verification scope: Python 3 standard-library fixture checks; application UI not tested. See the testing notes above for limitations.