How to Open CSV in Excel Without Losing Leading Zeros

Double-clicking a .csv file opens Excel, which guesses column types. Numbers like 00457 become 457 — leading zeros disappear. That is fine for math, disastrous for postal codes, invoice IDs, and fixed-width account numbers. The fix is to import as text, not to fight Excel after the fact. The problem shows up after mergers when two departments combine SKU lists — one side double-clicks CSV while the other imports correctly, and VLOOKUP suddenly ‘breaks' because keys no longer match.

Why double-click fails

Excel applies General format during quick open. Anything that looks numeric becomes a number. Scientific notation can appear on long digit strings. The damage is silent until someone merges files and IDs no longer match.

Import wizard (recommended)

On Windows: Data → Get Data → From File → From Text/CSV (wording varies by version). Select the file, set delimiter, then click Transform Data or open Power Query. Set columns that must keep zeros to Text before loading. Power Query on Windows can remember steps; save the query definition in the workbook template your ops team reuses monthly. Classic wizard: Data → From Text/CSV → choose file → Delimited → for each column select Text in the preview. Finish into a new sheet. On Mac Excel, use Data → Import → CSV and assign Text to ID columns in the preview.

Format before export (prevention)

If you control the source spreadsheet, format ID columns as Text before entering data, or prefix with an apostrophe ('0123) in Excel. When exporting to CSV, text cells keep quotes around values that need them. When sending files to accounting, a short README line (€˜Import ZIP as Text') prevents more damage than another round of CSV fixes.

Google Sheets note

Sheets imports often preserve strings if the column looks non-numeric. For mixed data, import via File → Import and set column type to Plain text. E.164 phone fields and national IDs with check digits should never be summarized as numeric — treat as text end to end.

Use csv-to-excel when Excel is awkward

Upload your CSV to /tool/csv-to-excel to produce an XLSX where specified columns are treated as text. Helpful when recipients keep double-clicking and you want a safer handoff file. Still teach them to import properly — XLSX is not foolproof if they convert back to CSV blindly.

ZIP and international IDs

U.S. ZIP+4, Brazilian CEP, and product SKUs with leading zeros all need text treatment. Phone numbers with country codes likewise — never store them as numbers if plus signs and leading zeros matter.

Teach the habit on your team

Add a one-slide note to onboarding: never double-click CSV from email; always import via Data and set identifier columns to Text. When someone sends you a broken file, ask whether they opened and saved it in Excel — re-export from the source system when possible instead of repairing hundreds of rows by hand. For recurring reports, deliver XLSX with text-formatted ID columns via /tool/csv-to-excel when your audience refuses import wizards. Log which columns must stay textual in the data dictionary so engineers use dtype=str in pandas scripts too.

Scientific notation on long numeric strings (E+14) is another Excel auto-convert symptom — import as Text before width exceeds fifteen digits. IBAN and routing numbers need the same treatment as SKUs. When Excel already corrupted a file, ask the source system for a fresh export rather than manual zero-padding unless the count is tiny. Template workbooks with preformatted Text columns on row 2 can guide colleagues: they paste values without touching format. For automated pipelines, prefer Parquet or database loads that preserve types instead of CSV round trips through Excel. Document in your wiki with a screenshot of the import wizard on Windows and Mac because menu labels differ.

FAQ

Can I fix zeros after open? Re-import with Text columns, or use Custom format 00000 if width is fixed — fragile if lengths vary.

Does TEXT() formula help? Yes in the workbook; CSV export may still need text-formatted source cells.

Will FileToolsHub guess column types? Follow the tool prompts; when in doubt, mark identifiers as text.

What about LibreOffice? Use the import dialog and set column type to Text similarly.