Excel and CSV: leading zeros, scientific notation, dates and accents

Open a CSV in Excel and four things can happen to it before you see a cell. Leading zeros vanish. Long numbers turn into 1.23457E+15 and lose their last digits for good. Dates are read in whichever order Windows is set to. And accented letters come out as Zoë. None of it is in the file. A CSV is plain text, and the damage is done by Excel’s reading of it. It is documented, and most of it can be measured and all of it avoided, which is what I set out to do while building this site’s Excel to CSV converter.

What Excel does to a file it opens

Microsoft lists the conversions on its page about automatic data conversions. In its own words, Excel is:

• “Removing leading zeros from numerical text and converting to a number”, so 00123 becomes 123.
• “Truncating numerical data to 15 digits of precision and converting to a number displayed in scientific notation”, so a 16-digit card number becomes 1.23457E+15, and its sixteenth digit becomes a zero.
• Turning numbers around the letter E into scientific notation, so a part code of 123E5 becomes 1.23E+07.
• “Converting a continuous string of letters and numbers to a date”, so a code of JAN1 becomes 1 January.

Since Excel for Microsoft 365 and Excel 2024 these can be switched off, under File, Options, Data, Automatic Data Conversion on Windows, and Excel, Preferences, Edit on a Mac. Older versions have no switch. There, the only defence is to import the file, with Data, From Text/CSV, rather than open it, and set the columns that matter to Text. Opening by double-click skips every question.

Two more problems are not on Microsoft’s list: the order of a date, and the encoding of a letter.

Long numbers, in detail

Two limits stack up. Excel’s General format, which every cell of an opened CSV gets, displays a whole number of 12 or more digits in scientific notation; 11 digits show in full. And Excel keeps 15 significant digits of any number, so the sixteenth digit onward is replaced with zeros. The first limit is cosmetic and the second is not: once 1234567890123456 has become 1234567890123450, no format brings the 6 back.

The display limit then becomes a file limit, because Excel’s Save as CSV writes cells as they are displayed. A card number that shows as 1.23457E+15 is saved as 1.23457E+15, and the next program to read the file gets a rounded number with six significant digits. I measured the same switch in SheetJS, the library behind many free converters and the one this site’s is built on, which reproduces Excel’s formatting: 12345678901 stays whole, 123456789012 becomes 1.23457E+11, and a 16-digit number in a saved workbook comes out of its default CSV export as 1.23457E+15.

Dates, in two orders

09/10/2026 is 9 October in Britain and 10 September in America, and a CSV cannot say which it means. Excel reads it in the regional order of the machine that opens it. The same ambiguity runs the other way: a date typed into Excel gets its built-in short date format, which Excel displays in each viewer’s own order, so the same workbook saved as CSV on a British machine and an American one gives two different files.

Converters inherit the problem. SheetJS renders that built-in format as m/d/yy for everyone, so a British workbook’s 9 October 2026 comes out of a SheetJS-based converter as 10/9/26. The fix for both directions is the ISO form, 2026-10-09, which every spreadsheet reads as a date and no locale can turn round.

Accents

A CSV written as UTF-8, which is what nearly everything writes now, has no label saying so unless it starts with a byte-order mark, three bytes that Excel looks for. Without them, Excel on Windows opened by double-click assumes the system’s old code page, which in Britain and America is Windows-1252, and decodes the two bytes of ë as two characters: Zoë Smith written as UTF-8 reads back as Zoë Smith.

Excel’s own “CSV UTF-8 (Comma delimited)” format writes the mark, and so does the converter here when the box for it is ticked. A file without one can still be imported correctly, with Data, From Text/CSV and the file origin set to 65001: Unicode (UTF-8).

The SYLK trap

One more, for anyone who writes CSV files for others to open. A CSV whose first two characters are a capital ID, the most natural first column name there is, makes Excel decide the file is in SYLK, a spreadsheet format from the 1980s, and refuse it with an error about the format not being valid. Microsoft’s answer is that “if the first two characters of the .csv file are uppercase ID, Excel assumes it’s an SYLK file type”, and the workaround it offers is to write id instead. Quoting the field works too, which is what the converter here does to any file that starts that way.

One workbook, three converters

To see which of these an export actually triggers, I made a workbook the way Excel writes typed-in values: a date in the built-in short date format, a percentage in a British number format, a 16-digit number in General, and an ID entered as text. Then I converted it three ways.

LibreOffice writes the date in the order of its own locale, an American one on this install, and the long number in full. SheetJS writes the date American for everyone, prints a stray dollar sign for a British number format that has no currency symbol in it, and copies Excel’s scientific notation. The converter here writes the date in the visitor’s own order, strips the stray sign, and writes the number in full; its raw mode writes 2026-10-09 and 0.256 instead.

And the other direction, measured on LibreOffice because Excel will not run here: LibreOffice opening the same trap values from a CSV drops the leading zeros too, 00123 becoming 123, but keeps all sixteen digits of the long number while displaying it as 1.23457E+15, leaves 09/10/2026 as text rather than guessing a date, and reads UTF-8 correctly with or without the mark.

What to do

If you receive CSV files: import them, do not open them, and set ID, code and phone columns to Text. Or, in Excel for Microsoft 365, turn the automatic conversions off once.

If you write them: quote the first field if it is ID, write dates as 2026-10-09, and write the byte-order mark. Python’s utf-8-sig encoding does the last for you.

import csv

# utf-8-sig writes the byte-order mark that tells Excel the file is UTF-8;
# quoting every field keeps a first column called ID from being read as SYLK.
with open("orders.csv", "w", newline="", encoding="utf-8-sig") as f:
    out = csv.writer(f, quoting=csv.QUOTE_ALL)
    out.writerow(["ID", "Customer", "Card", "Ordered"])
    out.writerow(["00123", "Zoë Smith", "1234567890123456", "2026-10-09"])

Quoting does nothing for the zeros or the digits, though. Excel strips the quotes and converts the contents anyway, so 00123 still becomes 123 when someone double-clicks that file. There is no way to write a CSV that Excel will open without converting; there is only the import, and the switch.