How to Open a CSV File in Excel Without Breaking Your Data
Why Excel puts everything in one column, turns codes into dates and drops leading zeros, and the reliable ways to open CSV files correctly.
CSV is the most common format for exporting data from shops, banks, CRMs and analytics tools. It is also the format that causes the most frustration in Excel. If you have ever seen all your data squeezed into column A, product codes turned into dates, or accented names turned into strange symbols, this guide is for you.
What a CSV file really is
CSV means comma-separated values. It is plain text: one line per row, values separated by a delimiter, and quotes around values that contain the delimiter. There is no information about data types, column widths, fonts, or even which character set the text uses. Excel has to guess all of that, and its guesses are often wrong.
Problem 1: everything is in one column
Excel uses your computer’s regional settings to decide which delimiter to expect. In the US and UK it expects commas. In most of continental Europe, where the comma is the decimal separator, Excel expects semicolons. Open a comma-separated file on a German or French computer and each row stays in a single cell.
Fix: instead of double-clicking, use Data, From Text/CSV in Excel and choose the delimiter manually, or convert the file with CSV to Excel, which detects the delimiter automatically.
Problem 2: leading zeros disappear
Postal codes, phone numbers, employee IDs and product codes often start with zero. Excel sees 00123, decides it is the number 123, and the zeros are gone forever once you save.
Fix: import the column as Text, or use a converter that keeps values with leading zeros as text. Ours does this automatically.
Problem 3: codes turn into dates
Values like 1-2, MAR1 or 2/3 are converted to dates. This is so common that scientists renamed several human genes because Excel kept turning names like SEPT1 into dates. Once converted, the original value is lost.
Fix: import affected columns as Text. Never open and re-save an important CSV in Excel without checking those columns.
Problem 4: long numbers get rounded
Excel stores numbers with 15 significant digits. A 16-digit card number or a long order ID becomes 1.23457E+15 and then loses its last digits. Treat any identifier longer than 15 digits as text. Our converter does this automatically.
Problem 5: broken accents and symbols
If “Müller” shows up as “Müller”, the file is UTF-8 but Excel opened it as an older Windows encoding. Excel recognises UTF-8 reliably only when the file starts with a byte order mark (BOM).
Fix: import via Data, From Text/CSV and choose “65001: Unicode (UTF-8)”. When exporting CSV for Excel users, add the BOM, as our Excel to CSV tool does by default.
Problem 6: decimal commas and points
1.234 means one thousand two hundred thirty-four in Germany and one point two three four in the US. If amounts look a thousand times too big or small, the file and Excel disagree about the decimal separator. In the import dialog, set the locale of the file or adjust the decimal settings.
The reliable workflow
- Do not double-click CSV files that matter.
- Convert them to XLSX with a tool that detects the delimiter, reads UTF-8 and keeps text columns as text, or use Excel’s Data, From Text/CSV import.
- Check ID, code and phone columns before you start working.
- Keep the original CSV untouched as your source of truth.
Exporting CSV for other systems
When you export from Excel for an import into a shop or CRM, find out what the other system expects: comma or semicolon, with or without BOM, which date format. Many import errors come from a semicolon file sent to a system that expects commas. If the target is a developer tool or API, JSON may be a better format altogether; see our beginner’s guide to JSON.
Our take
CSV is a great exchange format and a terrible working format. Use it to move data between systems, but convert it to a real spreadsheet before you edit it, and never let Excel “helpfully” re-save the only copy. Most data disasters we have seen in small businesses started with someone opening a customer export, making one change and pressing Save.