Open a CSV in Excel without losing leading zeros, SKUs or long numbers

Why does Excel change my SKU to a date?

Excel changes a SKU like 12-3 or 3/4 into a date because the value matches one of Excel's built-in date patterns, and a CSV gives Excel no type information to contradict the guess. The same guessing engine strips leading zeros from zip codes, expands 1E5 into 100000, and converts the gene symbol SEPT1 into 1-Sep. Every one of these happens silently, with no warning and no undo, while the file is being parsed — which is why reformatting the cell afterwards never brings the value back.

Here is the full list of conversions this tool detects:

How do I stop Excel removing leading zeros from a CSV?

You cannot stop it from inside Excel once the file is open — you have to change what you hand Excel. There are exactly three ways that work: import the CSV through Data → From Text/CSV and set the column type to Text in the preview; format a blank sheet's columns as Text and paste into them; or open an .xlsx that already declares the column as text, which is what the download above produces. Excel drops the zeros because 00501 looks like a number, and a number has no leading zeros — a CSV carries no type flag to say otherwise, whereas an .xlsx stores the type alongside every value.

Renaming the file to .txt also works: Excel then shows the legacy import wizard instead of guessing. It is a real workaround, but you have to repeat it for every file and every colleague you send it to.

Why does Excel turn my long barcode into 1.23457E+15?

Because Excel stores numbers as IEEE 754 binary64 ("double precision") values and then deliberately caps them at 15 significant decimal digits, replacing every digit after the 15th with a zero. The binary64 format itself can carry 15–17 significant decimal digits; the hard 15-digit ceiling is a Microsoft design decision, not something IEEE 754 requires (Microsoft documents this limit). That produces two failures people constantly confuse:

• A display problem. A 13-digit number in a narrow column shows as 1.23457E+12 but the full value is still there. Widen the column and it comes back. Nothing was lost.
• A data problem. A number with more than 15 significant digits — a 16-digit payment card number, an 18-digit tracking code, a 15-digit IMEI plus a check digit — has its tail overwritten with zeros the moment it enters a numeric cell. Widening the column does not help, because those digits no longer exist.

So 1234567890123456 does not merely display as 1.23457E+15 — the value in the cell is now 1234567890123450. This is why payment card numbers, IMEIs and long barcodes must be text from the very first moment. Once a 16-digit barcode has been through a numeric cell the file is no longer a reliable record, and the only fix is to re-export it from the source system. The Repair tab tells you which of your values fall on each side of this line rather than silently guessing.

Why does José become José when Excel opens my CSV?

This one is not type coercion — it is a character-encoding mismatch, and it has a different fix from everything else on this page. Your file is UTF-8. Excel, when you double-click a .csv, does not read it as UTF-8. On Windows it falls back to the system code page, usually Windows-1252, and reads each byte as one character.

In UTF-8 the character é is stored as two bytes, C3 A9. Read one byte at a time as Windows-1252, those become Ã and © — so José appears as José. The mangling is mechanical, which is why it is completely reversible: the original bytes are all still in the file, just misinterpreted. The Repair tab reverses it.

The three fixes, in order of how much you control:

1. Add a UTF-8 byte order mark to the file. Three bytes (EF BB BF) at the very start tell Excel the file is UTF-8, and it then reads it correctly on a plain double-click. This is what the UTF-8 CSV + BOM download above produces. If you control the export, do this and the problem disappears for everyone downstream.
2. Import instead of opening. Use Data → From Text/CSV and set File Origin to 65001: Unicode (UTF-8).
3. Use the .xlsx download. An .xlsx stores text as Unicode by definition, so there is no encoding to guess and no BOM required.

The same failure appears in other formats for the same reason — it is why accented names break in vCard exports, and it is handled in our VCF to CSV converter for exactly that reason.

Why does my CSV open with all the data in one column?

Nothing was destroyed here — this is a separator disagreement, and it is the one problem on this page that a plain re-save will fix. A CSV is comma-separated, but Excel does not necessarily look for a comma. It uses the list separator from your Windows regional settings. In much of Europe and South America that is a semicolon, because the comma is already the decimal separator.

So a comma-separated file opened on a machine configured for semicolons finds no separator at all, and drops every row into column A.

There is a documented shortcut: adding a line reading sep=, as the very first line of the file forces Excel to use that separator regardless of regional settings. It works on a double-click, but it is Excel-specific — other software will read that line as a data row. Use it only when Excel is the sole consumer.

The reliable answer is again the .xlsx download above, because an .xlsx has no delimiter at all. Cells are separate elements in the file format, so there is nothing for a regional setting to get wrong.

Why doesn't formatting the column as Text fix it?

Because formatting changes how a value is displayed, not what the value is — and by the time you can click a cell, Excel has already parsed the file and thrown the original string away. This is the most common answer on every forum and it is backwards. Formatting a cell showing 3-Dec as Text does not give you 12-3 back; it gives you 46359, the serial number Excel uses internally for 3 December 2026 (Excel counts days from 30 December 1899, so the number changes with the year Excel guessed).

Formatting as text only works if you do it before the data arrives:

1. Open a blank workbook and format the destination columns as Text, then paste. Or
2. Use Data → From Text/CSV and set the column type to Text in the import preview. Or
3. Open a file that already declares its types — which is what the .xlsx above does.

Should I use Power Query (Data → From Text/CSV) instead?

Data → From Text/CSV is the official answer and it works. In the preview pane, click Transform Data, select each code column, and set Data Type to Text. Say Replace current when Excel offers to add a conversion step.

Where it falls down, honestly:

• It is roughly a dozen clicks per file, every time.
• Excel infers types from the first 200 rows by default. If the first 200 zip codes happen to start with a non-zero digit, it will still get it wrong.
• It is not available in Excel for the web, and it is awkward on Mac.
• You cannot easily hand the corrected file to a colleague — they have to repeat the whole process.

If you open the same report weekly, learn Power Query — it is genuinely the better tool for a repeating job. For a file you were sent once and need to open now, the .xlsx above is faster and travels with the data.

Is a CSV from untrusted data a security risk?

Yes. A cell beginning with =, +, - or @ is treated as a formula by Excel, LibreOffice Calc and Google Sheets, and evaluated when the sheet opens. OWASP tracks this as CSV Injection (also called formula injection). If your CSV was built from data other people supplied — form submissions, support tickets, uploaded product feeds — a crafted cell can read values out of the sheet and send them to an attacker's URL, and on Windows it can escalate through Dynamic Data Exchange (DDE) to a prompt that runs an external command. It is a vulnerability class, not a formatting nuisance.

The scan above flags every value starting with one of those four characters. Two things worth knowing about the fixes, because most advice on this is wrong:

• The apostrophe prefix is not reliable. It is the standard advice, but the apostrophe is an Excel input convention, not part of the CSV format — write it into a file and Excel shows a literal '. OWASP's recommended mitigation is a tab character (0x09) inside the quoted field, and it notes that even that can be undone when Excel saves and reopens the file.
• The .xlsx download is the only fully safe output here. It writes every text value as an inline string, which Excel cannot evaluate as a formula under any setting. The Armoured CSV option wraps values as ="…", which neutralises the payload — the inner text is returned as a literal string, not executed — but the cell still contains a formula, so it is the weaker option.

This is also why you should never build a CSV export by joining strings with commas.

Why does Excel change gene names like SEPT1 to dates?

Because SEPT1, MARCH1 and DEC1 match Excel's month-abbreviation pattern, so they become 1-Sep, 1-Mar and 1-Dec on import. This got bad enough to change a scientific field's naming conventions. Ziemann, Eren and El-Osta found in Genome Biology (2016) that roughly a fifth of papers with supplementary Excel gene lists contained gene names corrupted this way; a 2021 re-run by Abeysooriya et al. in PLOS Computational Biology put the figure at 30.9% — 3,436 of 11,117 articles, higher than before rather than lower.

In 2020 the HUGO Gene Nomenclature Committee gave up and renamed the genes instead of the software: 27 human gene symbols were changed, with SEPT1 becoming SEPTIN1, MARCH1 becoming MARCHF1 and DEC1 becoming DELEC1, under revised guidelines published in Nature Genetics that explicitly added avoiding "symbols that affect data handling and retrieval" as a naming rule. A spreadsheet's autocorrect changed the names of human genes.

The Repair tab recognises these conversions and maps them back, because the correspondence is one-to-one. If your column contains both gene symbols and product codes it shows you both candidate readings rather than silently picking one.

Should I download the .xlsx or the armoured CSV?

Choose the .xlsx unless something downstream specifically requires a CSV file. It is the only output that keeps code columns as text and leaves quantity and price columns as real numbers you can still add up, and it is the only one that is safe against CSV injection by construction.

How do I export a CSV that Excel will not damage?

Everything above is damage control. If you control the export, these four rules remove the problem at source:

• Write UTF-8 with a byte order mark (EF BB BF). Without it Excel assumes the local codepage, which is what turns José into José.
• Export identifiers as .xlsx with a text type, not as CSV, whenever the consumer is a human with Excel.
• Escape any value starting with = + - @ with a leading tab character (0x09) inside the quoted field, per OWASP — not an apostrophe, which is an Excel typing convention and does not survive into the file.
• Never emit a bare number longer than 15 significant digits that you care about. Quote it, prefix it, or ship .xlsx — something in the chain will eventually round it.

Does this work with Google Sheets, LibreOffice and Numbers?

Yes. The .xlsx opens correctly in Google Sheets, LibreOffice Calc and Apple Numbers, and all three make broadly the same guesses as Excel when importing a plain CSV, so the same protection applies. Google Sheets is slightly less aggressive about long numbers but strips leading zeros and converts SEPT1 exactly like Excel does. LibreOffice Calc is the one program that offers a genuine per-column type dialog on every CSV open, which is why LibreOffice users hit this problem least often.