Why Excel changes your CSV

A CSV records values as text. There are no types in the file: 01234 is five characters, and 3-4 is three. When a spreadsheet opens one it has to decide what each of those means, and it decides by pattern — anything that looks like a number becomes a number, anything that looks like a date becomes a date.

That conversion is where the data goes. A number has no concept of a leading zero, so 01234 becomes 1234. A date has no concept of "3-4", so it becomes the third of April. Neither is recoverable from what is now in the cell.

The file on disk is unchanged until you save. That is the part that catches people: the damage looks cosmetic, and becomes permanent the moment somebody presses Save without thinking about it.

Leading zeros disappear. Postcodes, account numbers, employee IDs and any zero-padded reference are all read as numbers, and the padding is not part of a number.

Long numbers become scientific notation. Past fifteen digits a spreadsheet stores the value as a floating point number, so a sixteen-digit card or reference number displays as 1.23457E+15 and — crucially — its final digits are now actually gone, not merely hidden. Widening the column does not bring them back.

Things that are not dates become dates. 3-4 becomes 3 April, SEP1 becomes a September date, and gene names are the famous case: a whole field of biology had to rename its genes because spreadsheets kept turning MARCH1 into a date.

Text that is not UTF-8 arrives as nonsense. An export from an older system is usually windows-1252, and a spreadsheet that assumes UTF-8 shows é as é, right across the file.

Do not double-click the file. Double-clicking is what applies the guessing. Open the spreadsheet application first, then use its import option — Data, then From Text/CSV in Excel — which offers a preview and lets you set each column's type before anything is converted.

In that preview, set every column that is an identifier rather than a quantity to Text. Postcodes, account numbers, product codes and reference numbers are labels that happen to be made of digits, and nothing arithmetic is ever done to them.

Set the encoding in the same dialog if the accented characters look wrong. Choosing windows-1252 rather than UTF-8 fixes an entire file of é in one step.

If you only need to read the data or send it on, do not open it in a spreadsheet at all — none of this can happen to a file nothing has interpreted.

Check whether the original file is still intact. If nobody has saved over it, the CSV on disk still holds the real values and re-importing properly recovers everything.

If it was saved, leading zeros can sometimes be restored when you know the width — a five-digit postcode padded back to five is safe. Digits lost to scientific notation cannot be restored at all, because they were never stored; that data has to come from the source system again.

Dates are the awkward middle case. 3 April could have been 3-4 or 4-3, and which one depends on the locale that did the conversion. If the column has any values above twelve you can work out the order, and if it does not, you cannot.

If the reason you were opening it was to print it or send a readable version, converting the file directly avoids the whole problem. CSV to PDF here reads the text, works out the separator and the encoding, and draws the values as the characters that are in the file — nothing is parsed as a number or a date on the way through, so there is nothing to lose.

It runs in your browser, which matters more for this format than most: a CSV export is usually the most sensitive thing a system produces — customer rows, transactions, payroll — and it never leaves your device.