How to open a CSV file in Excel correctly: delimiter, encoding and import

You double-click a CSV file and Excel shows everything in a single column, with accented letters turned into strange symbols and zip codes missing their leading zero. The file is not broken: Excel simply guessed wrong about how to read it. A CSV is plain text, and to turn it into rows and columns Excel needs to know three things: which character separates the fields, which encoding the text uses and what type of data each column holds.

In this guide you will see why each problem happens, how to import a CSV step by step so it opens correctly the first time, and how to repair the data if you have already opened it the wrong way. It applies to Excel 365, 2021, 2019 and 2016 on Windows, with notes for older versions.

Why Excel opens a CSV incorrectly

• Field delimiter. In countries that use a comma as the decimal separator (Spain, most of Latin America, France, Italy, Brazil), Excel expects semicolons between fields. A comma-separated file opened there ends up entirely in column A. The reverse also happens: a semicolon-separated CSV opened on a computer set to English stays in a single column too.
• Encoding. Many systems export in UTF-8. If the file does not start with a BOM marker, Excel may read it with the old Windows encoding and you will see ó where there should be an ó.
• Automatic type conversion. Excel turns anything that looks like a number into a number, and anything that looks like a date into a date. That is why 01005 becomes 1005, a product code like 3-4 shows up as a date and an 18-digit number ends up in scientific notation.

This table sums up the most common symptoms and their fix:

Recommended method: Data › From Text/CSV

1. Open a blank workbook and go to Data › Get Data › From File › From Text/CSV. The ribbon also has a direct From Text/CSV button.
2. Select the file and click Import.
3. In the preview, check File Origin. If you see strange characters, choose 65001: Unicode (UTF-8).
4. Under Delimiter, choose the right one: comma, semicolon, tab or custom (for example, the pipe character |).
5. If some columns have leading zeros or long codes, set Data Type Detection to not detect data types, or click Transform Data to adjust each column.
6. Click Load. The data lands on a new sheet as an Excel table.

Adjusting types in the Power Query Editor

If you click Transform Data, the Power Query Editor opens. Click the type icon to the left of each column name and choose Text for codes, phone numbers, ID numbers or account numbers. For amounts or dates written in another regional format, use the Using Locale option in the same menu: you pick the type (decimal number, date) and the file’s country of origin, for example German (Germany) if the decimals come with a comma.

The classic Text Import Wizard

If you prefer the good old wizard, or you use Excel 2010 or 2013 without Power Query, the Text Import Wizard is still available. In Excel 365 it is hidden: turn it on in File › Options › Data by ticking From Text (Legacy) in the section for legacy data import wizards. It will then appear under Data › Get Data › Legacy Wizards.

1. Step 1: choose Delimited and, under File origin, 65001: Unicode (UTF-8). Tick the box if your data has headers.
2. Step 2: tick the delimiter (comma, semicolon, tab or other). The preview should show the columns properly split. Leave the double quote as the text qualifier.
3. Step 3: select each sensitive column and choose Text as the column data format. The Advanced button lets you set the decimal and thousands separators the file uses.
4. Click Finish and choose the destination cell.

Shortcuts to make double-clicking work

If you just want one specific file to open correctly with a double-click, you have three quick options:

• Add a sep= line. Open the CSV in a text editor and type sep=, (or sep=;) as the first line. Excel will use that delimiter when opening it. Be careful: only Excel understands this line, other programs will treat it as just another row, and in some versions it makes Excel ignore the UTF-8 encoding.
• Save with a BOM. In Windows Notepad, go to File › Save As and choose the UTF-8 with BOM encoding. Excel will detect the encoding and accented characters will display correctly.

How to fix a CSV you already opened incorrectly

Everything is in column A

Select column A and use Data › Text to Columns. Choose Delimited, tick the right delimiter and, in the last step, set the columns that need it to Text.

Decimals in a format Excel does not recognize

If your settings use a decimal point and the file contains 1250,75 in cell A2 (a common format in European systems), Excel leaves it as text. Convert it to a number with this formula (Excel 2013 or later):

=NUMBERVALUE(A2,",",".")

The second argument is the decimal separator of the original text and the third is the thousands separator. If the value comes as 1.250,75, the same formula returns 1250.75.

Lost leading zeros

If a five-digit zip code has become 1005, you can restore its original form with:

=TEXT(A2,"00000")

This works when all codes have the same length. Numbers with more than 15 digits, however, cannot be fixed: Excel has already replaced the last digits with zeros. In that case, import the file again with that column as text.

Common mistakes and tips

• Saving the CSV over the original after opening it with a double-click. If Excel has already removed zeros or changed dates, the saved CSV keeps those changes. Work on a copy or save as XLSX.
• Trusting the preview without checking the end. Power Query detects types from the first 200 rows. If further down there are codes with letters in a column it assumed was numeric, errors will appear. Check the notice about rows with errors when loading.

If you do not need to adjust anything and just want an Excel file ready to open, you can use our free tool to convert CSV to Excel: upload the CSV and download an Excel file that opens with a double-click. And if you need to go the other way to send data to another system, there is also the Excel to CSV converter.

Conclusion

A CSV opens incorrectly when Excel guesses the delimiter, the encoding or the data types. The solution is not to let it guess: import with Data › From Text/CSV, choose UTF-8, specify the delimiter and mark code columns as text. If you already opened the file, Text to Columns, NUMBERVALUE and TEXT fix almost everything, except long numbers, which can only be saved by importing again.

Frequently asked questions