How to Open a CSV File Without Excel Breaking It

Nov 19, 2026
6 min read
How to Open a CSV File Without Excel Breaking It

A CSV file is plain text: one row per line, values separated by commas. Any program can open it, so opening is never the problem. The problem is what the program does to your data once it is open. Excel in particular guesses what every cell is, and some of those guesses cannot be undone.

What Excel does when you double-click a CSV

Double-clicking hands the file to Excel's automatic import, which converts values as it reads them. These are the usual casualties.

  • Leading zeros disappear. A ZIP code such as 02134 becomes 2134, and a product code such as 00417 becomes 417.
  • Long numbers are rounded. Excel keeps 15 significant digits. A 20-digit ID shows as 1.23457E+19, and every digit after the fifteenth is replaced with a zero once you save.
  • Text that looks like a date becomes one. Gene names such as SEPT1 and MARCH1 turn into 1-Sep and 1-Mar. It was common enough in research data that the gene-naming committee renamed the genes in 2020.
  • Accents turn into garbage. A UTF-8 file with no byte order mark can show é as é, depending on your version of Excel.
  • Everything lands in column A. Excel splits on your computer's list separator. Where decimals are written with a comma, that separator is a semicolon, so a comma-separated file stays in one column. The reverse happens to a semicolon file on a computer set to US formats.
  • Rows past 1,048,576 are dropped. Excel warns that the file was not loaded completely.

Once you save the damaged sheet, the original values are gone. Keep the original CSV and work on a copy.

Open it properly in Excel

In Excel 2019 and Microsoft 365, do not double-click the file. Open a blank workbook and use Data, Get Data, From File, From Text/CSV.

  • Pick the file. Excel shows a preview of the first rows.
  • Check File Origin. If accents look wrong, change it to 65001: Unicode (UTF-8).
  • Check Delimiter. Try comma, semicolon or tab until the preview splits into columns.
  • Set Data Type Detection to Do not detect data types. Every column then loads as text, so zeros and long IDs are untouched.
  • Click Load. Change a column to Number or Date afterwards, only where you want it.

On older versions the route is Data, From Text (Legacy). The wizard's last step lets you set each column to Text.

Open it in Google Sheets

Use File, Import, Upload, and pick the file. In the import box, set the separator type if the columns did not split, and untick "Convert text to numbers, dates and formulas" so codes stay as typed. A Google Sheet holds up to 10 million cells in total, so a very large file will not fit.

Read it as plain text

To see what is really in the file, open it in Notepad, in TextEdit set to plain text, or in VS Code. You see the raw delimiter, the quoting and any odd characters, which settles whether a problem is in the file or in the program that opened it. Do not save the file from a word processor.

Convert it to an Excel file first

If you would rather hand Excel a finished workbook than fight its import, the CSV to Excel converter does the import for you. We ran it on test files to see exactly what comes out.

  • Every cell is kept as text. A test row holding 02134, a 20-digit ID, 1200.50, 2026-03-04 and SEPT1 came back exactly as typed. The zero, the ID and the gene name survived, and nothing turned into a date.
  • The catch is that numbers are text too. They will not add up. Excel marks them with a green triangle, and the warning icon offers Convert to Number for a selected column. Dates stay as text until you convert them.
  • The delimiter is detected. Comma, semicolon, tab and pipe are tried. A semicolon file split into the right columns.
  • Quoted cells stay whole. A cell containing a comma and a cell containing a line break each stayed in one cell.
  • Accents survive. UTF-8 files, UTF-8 files with a byte order mark, and the Windows files Excel writes as "CSV (Comma delimited)" all kept their accented letters.
  • Nothing else is added. The result is one sheet called Sheet1, with no formulas, no column widths and no formatting.

Limits

Conversions are limited to 2 a day as a guest, 5 a day and 10 a month with a free account. Files can be up to 50 MB as a guest and 100 MB with a free account.

Which method to use

  • You need to analyse the numbers. Use Excel's Get Data route and set the column types yourself.
  • Codes, IDs or ZIP codes must not change. Load those columns as text, or use the converter.
  • You only want to check what is inside. Open it in a plain text editor.
  • The file has more than a million rows. Excel is the wrong tool. Use a database or a scripting language.

Do it now

Free tools for everything covered in this guide

Browse All Posts