Why CSV files open incorrectly in Excel, and how to fix them

Delimiters, encodings, disappearing zeros and date surprises: a practical guide to dependable CSV files.

CSV looks like the simplest data format in existence: rows of text, values separated by commas. That simplicity is misleading. There is no single strict standard that all programs follow, so a file exported by one system can appear as a mess in another. Nearly all of those problems fall into a few categories.

What a CSV really is

A CSV file is plain text, and it holds no fonts, colours, formulas, formats or multiple sheets. Each line is a record and a separator marks the boundary between fields. If a value contains the separator or a line break, it should be surrounded by double quotes. A double quote inside such a value is written twice.

Because the file only contains text, the program that opens it has to guess what each piece means. Is 03/04/2026 the 3rd of April or the 4th of March? Is 00123 a number or a code? Most CSV problems are really wrong guesses.

Everything in one column: the delimiter problem

If all the data is squeezed into column A, the spreadsheet is expecting a different separator. In countries where a comma is the decimal mark, including Turkey, Excel generally expects a semicolon in CSV files, following the regional settings of Windows. A comma-separated file will therefore not split as you would expect.

There are several ways to deal with it:

  • Use Excel's data import feature (Data, From Text/CSV) and pick the delimiter yourself.
  • Convert the delimiter in the file to one that matches your region.
  • For files that you generate, agree on a delimiter with whoever receives them and document it.

A tab-separated file avoids much of this ambiguity, as tabs rarely appear in normal text.

Broken letters: the encoding problem

When ş, ğ and ı appear as strange symbols, the file was written in one encoding and read in another. UTF-8 is today's standard, but Excel may treat a double-clicked CSV as a legacy encoding unless the file starts with a byte order mark. The data is usually fine and it is the interpretation that is wrong.

When opening a CSV, use the import dialog and explicitly set the encoding to UTF-8. When saving from Excel, choose "CSV UTF-8" instead of plain "CSV". If you generate files with software, writing UTF-8 with a byte order mark tends to give the best results for people who will double-click the file.

Disappearing zeros and scientific notation

Spreadsheets love to convert things into numbers. This damages data in several well-known ways:

  • A postal code such as 01250 or a phone number starting with 0 loses its leading zero.
  • A 16-digit card or a long identity number turns into 1.23457E+15, and digits after the fifteenth may be permanently replaced with zeros.
  • A product code like 1-2 or MAR1 might become a date.

The fix is to import these columns as Text rather than letting the program detect their type. It has to happen at import time. Once a long number has been stored as a rounded value and the file saved, the lost digits cannot be recovered.

Dates and decimal marks

Dates are a classic trap when data moves between countries. The safest format for exchange is ISO 8601: 2026-03-14, which sorts correctly as text and cannot be read two ways. Decimal values have a similar problem, since 1.234 is about one in some countries and one thousand two hundred thirty-four in others. Agree on one convention, and avoid thousands separators altogether in exported data.

Keeping files clean

  • Put one header row at the top, with unique, simple names.
  • Avoid merged titles, summary rows and blank lines inside the data.
  • Keep one type of value per column.
  • Decide how missing values are represented, usually with an empty field.
  • Trim stray spaces at the start and end of values, because "Ali" and "Ali " count as two different names.

Duplicates deserve a mention here. Deciding what counts as a duplicate is a business question: two rows may share an e-mail address but differ in order date. Choose the key columns before removing anything, and review a sample of what will be deleted.

Large files

Excel has a limit of 1,048,576 rows per sheet. Opening a file with more rows silently leaves out the rest, and saving afterwards makes the loss permanent. For very large CSV files, use a viewer, split the file into parts or load it into a database. Never treat the row count you see in a spreadsheet as proof that all of the data was loaded.

Security: formula injection

A value that begins with =, +, - or @ can be treated as a formula when a CSV opens in a spreadsheet. If your application exports text entered by users, a malicious value could run a formula on the computer of whoever opens the file. Applications that export user data should escape such values, and anyone opening a CSV from an unknown source should keep protected-view warnings switched on.

A dependable routine

Keep the original export untouched. Do your work in a copy, import with explicit types, check row counts before and after, and take a look at the columns most likely to be damaged: identifiers, dates and anything with non-English letters. Those few checks catch most problems before they reach someone else.

Tools mentioned in this guide