A CSV file can contain 00123 correctly while a spreadsheet displays 123 after opening it. The file stores text fields; the importing application decides how to interpret them. Preserve identifiers as text at import time, and check the saved output before replacing the original.
An identifier is not necessarily a number
Use arithmetic for quantities such as a count or a price. Treat a postal code, catalogue key or account identifier according to its meaning: its digits may be labels, not a quantity to add. Leading zeros, long digit strings and date-like codes can all be damaged by an unwanted conversion.
Microsoft's guidance on leading zeros and large numbers explains importing appropriate columns as text. It also documents automatic-conversion controls in supported Excel versions. Check your actual version; the same settings are not available in every edition.
An original, fictional CSV example
These are invented records for learning, not real account or customer data. Download the example CSV.
item_id,reference,label,quantity
00123,12345678901234567890,JAN1,7
00007,98765432109876543210,1E10,12
| Column | Intended meaning | Import type |
|---|---|---|
| item_id | A five-character identifier | Text; preserve 00123 and 00007 |
| reference | A long reference string | Text; preserve every digit |
| label | A code, not a date or exponent | Text; retain JAN1 and 1E10 literally |
| quantity | A count | Whole number, after validation |
CSV does not carry this column-type table as a universally enforced schema. Including it in your handoff instructions can prevent another application from guessing differently.
Import deliberately in Excel
- Keep an unchanged copy of the source CSV.
- Use the application's text/CSV import workflow rather than relying on a double-click's automatic guesses.
- Inspect the preview and set identifier-like columns to text before the conversion is applied. In a query workflow, inspect or replace any automatic type-changing step as needed.
- Check the separator, encoding and treatment of date-like values.
- Load the data and compare the exact identifiers with the source.
- After exporting, inspect the resulting CSV in a text editor and import it using the intended column types again.
Microsoft's text-file import/export instructions describe the distinction between opening and importing. Menu labels differ by platform and version. A custom display format such as five zeros can show 123 as 00123, but that is not the same as preserving the original text identifier or recovering digits already lost.
Check the strings with Python
The Python csv documentation describes reading fields as strings by default, with specific exceptions for conversion options. This small check uses only the standard library:
import csv
with open("identifier-example.csv", newline="", encoding="utf-8") as f:
rows = list(csv.DictReader(f))
assert rows[0]["item_id"] == "00123"
assert rows[0]["reference"] == "12345678901234567890"
assert rows[1]["label"] == "1E10"
quantities = [int(row["quantity"]) for row in rows]
assert sum(quantities) == 19
The checks deliberately convert only the quantity column. They demonstrate the sample file's contents, not a guarantee about an arbitrary export or spreadsheet application.
What cannot be recovered automatically
If a long identifier has already been rounded and saved over the original, changing the cell format to text cannot reconstruct the missing digits. Restore from an unchanged source. If 123 might mean either 00123 or 123, padding it is a business-rule decision, not proof of the original value.
Quoting a CSV field is syntax for preserving delimiters and other characters; it does not universally force a spreadsheet to treat the field as text. A useful handoff states the encoding, separator, intended column types and validation rules. For a related document-sharing problem, see Word font availability and portability.