How to Open CSV in Excel Without Breaking Data

Knowing how to open CSV in Excel is really knowing which of two routes to take. The obvious one — double-clicking the file — hands your data to Excel's default parser, which strips leading zeros and rewrites anything that looks like a date. Unlike a double-click, importing through the Data tab lets you set each column's type before a single value reaches the sheet.

The damage is quiet, which is what makes it expensive. Nobody sees a dialog saying "your customer IDs have been converted to scientific notation." You notice three days later, when an import fails or a mail merge goes out addressed to the wrong half of your list.

Why double-clicking a CSV file breaks it

A CSV file has no types in it. That is the whole point of the format — and the whole source of the problem.

RFC 4180, the specification that describes CSV, defines a file as records on separate lines, fields separated by commas, an optional header line, and double quotes around any field containing a comma, a quote, or a line break. That is the entire contract. There is no place in the file to say "this column is text" or "this column is a date." Every value is characters.

So a reader has to guess. Microsoft is explicit about what Excel does when you open a CSV directly: it "automatically opens the text file and displays the data in a new workbook" using the current default data format settings (Microsoft, Import or export text files). Default settings mean type inference, and type inference on real business data means three predictable failures:

• Leading zeros vanish. 01234 becomes 1234. Postcodes, ZIP codes, account numbers, and product SKUs all lose their first digits.
• Long digit strings become scientific notation. A 16-digit order ID renders as 1.23E+15, and the trailing digits are gone from the stored value, not only from the display.
• Text that resembles a date becomes a date. 3-5 becomes 3-May. Version strings, gene names, and part numbers are the usual casualties.

None of this is a bug. Microsoft states the reasoning plainly: Excel removes leading zeros and converts large numbers to scientific notation "in order to allow formulas and math operations to work on them" (Microsoft, Keeping leading zeros and large numbers). It is a spreadsheet doing spreadsheet things to a file that never asked to be a spreadsheet. Our primer on what a CSV file actually is covers the format side in more depth.

The safe way to open a CSV in Excel

Import the file instead of opening it. The route takes four clicks and gives you a preview before anything is committed.

1. Open a blank workbook. Do not start from the CSV — start from Excel.
2. Go to Data > From Text/CSV. Microsoft's steps are to click the cell where you want the data, then on the Data tab, in the Get & Transform Data group, click From Text/CSV.
3. Locate the file and click Import. A preview window opens showing the first rows, the detected delimiter, and the file origin (the character encoding).
4. Set the column types, then load. This is the step that matters. Any column holding an identifier rather than a quantity should be Text.

The preview is the whole value of this route. You can see that the delimiter was read correctly, that the header row was recognised, and that the encoding has not turned accented names into mojibake — all before the data exists in a worksheet.

A rule of thumb for step 4: if you would never add two of these values together, the column is Text. Order numbers, phone numbers, ZIP codes, SKUs, VAT numbers, and bank sort codes are all text that happens to be spelled with digits. Prices, quantities, and scores are numbers.

Keep leading zeros and long IDs intact

The Text setting has to be applied during the import. Applying it afterwards does not bring anything back.

Microsoft notes the limitation on the retroactive fix: a custom number format such as 000-00-0000 "will not restore leading zeros that were removed prior to formatting. It will only affect numbers that are entered after the format is applied." The 1234 in your sheet stays 1234 — the format only paints zeros onto its display. Save the workbook and the truncated value is what persists.

The same asymmetry applies to scientific notation. Widening the column or switching the format to Number shows more digits, but the digits beyond Excel's 15-digit precision limit were discarded at parse time. A 16-digit credit reference imported as a number is permanently a different number.

Two practical consequences:

• If a file has already been opened wrong, close it without saving and re-import from the original.
• If you have already saved over the CSV, the original values are gone. You need a new export from whatever system produced it.

When the CSV opens in a single column

This one has a different cause, and a fix that is faster than it looks.

Excel decides where to split a row using your operating system's list separator, not the file's actual delimiter. On a machine configured for a comma separator, a semicolon-separated European export lands entirely in column A — and the reverse happens too. The file is fine; the reader's assumption is wrong.

The import route solves it because the preview exposes a delimiter dropdown you can override. If you need the double-click to work — for a file colleagues will open repeatedly, say — you can add a single line reading sep=, as the very first line of the CSV, which tells Excel the delimiter explicitly. Note that this line is an Excel convention, not part of RFC 4180, so other readers may show it as a stray first row.

If the delimiter itself is the problem across a batch of files, converting once is steadier than fixing the reader every time. Sigmera's CSV to Excel converter writes a real .xlsx with the types already settled, so there is no parsing step left for Excel to get wrong — and its reverse direction writes a clean comma-delimited file back out.

What to do when the file is too big for Excel

Excel has a hard ceiling. A worksheet holds 1,048,576 rows by 16,384 columns, and one cell holds at most 32,767 characters (Microsoft, Excel specifications and limits).

Exports from CRMs, ad platforms, and event logs pass that line regularly. When they do, Excel loads what fits and leaves the rest — and the warning is easy to click past. If you are working with a large export, compare the row count of the source file against the count in the sheet before you draw any conclusion from it. A deduplication run on a truncated file produces a confidently wrong answer.

For files at that scale, do the row-level work outside the spreadsheet and open only the result. Removing duplicates, filtering, and column surgery do not need a formula engine, and doing them first often brings the file back under the ceiling.

Clean the CSV before Excel ever sees it

There is a third route worth knowing: do not open the CSV in Excel at all until it is already correct.

Every problem in this article comes from one source — a spreadsheet applying type inference to plain text. A tool that treats the file as text has nothing to infer. Sigmera reads a CSV verbatim in your browser, so leading zeros stay, long IDs stay, and 3-5 stays 3-5. From there you can remove duplicate rows, format a phone column to E.164, standardise an email column, or split full names, then export a file that Excel has no opportunity to mangle.

The processing happens with the browser's own JavaScript engine on your machine. The file is never uploaded, so a customer list with names, emails, and phone numbers in it never reaches a server — which is the difference between a cleaning step your data protection officer signs off on and one they do not. The rest of the in-browser cleaning tools work the same way.

None of this replaces Excel. Pivot tables, charts, and formulas are what Excel is for. The point is narrower: a CSV is a text file, and the moment you let a spreadsheet guess at its meaning, you have lost information you cannot get back. Set the types on the way in, or settle them before the file gets there.