You export a list of postcodes, open it to check, and 07430 has become 7430. Every value in the column is one character short. The export looked fine when you made it.
Why do leading zeros disappear in CSV
They were read as numbers, and numbers have no leading zeros
CSV stores no types — every field is text, and the program opening it guesses. A run of digits looks like a quantity, so 07430 is loaded as the number 7430 and the zero has nowhere to live. The file on disk is unchanged. Open it in a text editor and the zeros are all still there.
Where the guess happens
Nothing in a CSV says what a column contains. There is no header row that declares this one is text, no schema, no types at all — which is the point of the format and the source of most of its trouble. Every reader that wants to put values in columns has to decide what they are, and the only evidence is what they look like.
A field of digits looks like a number. That guess is right almost always: quantities, prices, counts and ages all arrive as digits and all want to be numbers. It is wrong for the class of values that are digits without being quantities — postcodes, part numbers, dialling codes, account references, anything with a fixed width — and for those the guess destroys the leading zeros as a side effect of being helpful.
The moment the damage becomes real
Opening the file does not change it. The zeros are on disk throughout; you are looking at an interpretation. Close the spreadsheet without saving and nothing has happened at all.
Saving writes the interpretation back. At that point the text 07430 is replaced by the number 7430 in the actual file, and the original is gone. So the first move on noticing the problem is not to fix the display — it is to stop, close without saving, and go back in through an import that does not guess.
The import that does not guess
In Excel the double-click path always guesses. The route that does not is Data → From Text/CSV, which shows a preview and lets you set a column's type to Text before anything loads. Set it there and the zeros survive, because you answered the question instead of leaving it open.
Changing the cell format afterwards does nothing. Formatting decides how a stored value is displayed, and by then the stored value is 7430 — there is no zero to display. This is the single most common wasted hour in this whole problem.
The tricks, and what each one costs
A leading apostrophe tells Excel to treat the cell as text. It also gets written into the cell, so the next export carries it and every other program reads it as part of the value. It fixes a spreadsheet and breaks a file.
Wrapping as ="07430" is better inside Excel — it evaluates to text — and worse everywhere else, since anything that is not Excel reads the equals sign and the quotes as literal characters. Reasonable when Excel is certainly the only reader, and a trap the moment the file is passed on.
Quoting the field in the CSV itself — "07430" — feels like it should work and does not. Quotes tell a reader where a field ends, not what type it is; they are punctuation rather than data, and a spreadsheet strips them and then guesses exactly as before.
The same guess, other victims
Once you see the mechanism, the other symptoms are the same one. A field like 3-4 becomes a date, because it looks like one. A long account number becomes 1.23457E+14, because a number that large gets scientific notation. A value of NA becomes something a spreadsheet thinks is missing.
Human genetics has the best-documented casualty: gene symbols like SEPT2 turned into 2-Sep in so many published datasets that the naming committee eventually renamed the genes. That is how hard this guess is to switch off.
Recovering what is already gone
Padding back works only where the width is a rule. A postcode column that is always five digits can be restored with confidence. A part number of unknown length cannot — 42 could have been 042 or 0042, and nothing in the file distinguishes them.
If you still have the original export, go back to it and re-import properly. The CSV cleaner reads the file as text without deciding anything about types, so it shows you what is actually stored — which is usually enough to establish whether the zeros were ever lost or only ever hidden.
The short version
CSV has no types, so something has to guess, and digits look like numbers. Do not double-click; import and set the column to Text. If you have already opened it, close without saving — the file is probably still fine.