Excel does not recognize the fields and displays all data in the first column. How do I set the delimiters correctly?

Trouble opening CSV files with Excel? The comma and semicolon issue in Excel due to regional settings for Europe

When opening standard CSV (Comma Separated Values) files in Excel you may find that Excel did not recognize the fields and simply displays all data in the first column.

The Problem

The problem is:

• The standard field delimiters for CSV files are commas: ,
• On American Windows versions, the comma is set as default for the "List Separator", which is okay for CSV files.
• On European Windows versions, this character is reserved as the Decimal Symbol and the "List Separator" is set by default to the semicolon: ;

There are four Solutions

Choose one of these possibilities:

1. Change the CSV file extension to TXT (when you open a TXT file with Excel it will start the text file import wizard where you can choose the delimiter)
   or
2. In your CSV file(s), use a text editor to replace all commas (,) with semicolons (;).
   or
3. In your Windows Control Panel, change your system's language settings.
   or
4. Add a line in your CSV file to define separators.

Solution #3: Changing Regional and Language settings

The last solution is our preferred one. To change regional settings,

1. Go to Start | Control Panel | Regional and Language Options
2. Click Additional Settings
3. For Decimal Symbol, enter a dot: .
4. For List Separator, enter a comma: ,

Now, when you open a CSV file in Excel it will automatically find the data fields and open it appropriately.

Windows Region and Language Settings 

Solution #4

Add an extra line to your CSV file to tell Excel what the separator is. Add the following line to the top of your CSV file:

sep=;

This will tell Excel to use the ";" character as separator for the next lines. This first line is considered an instruction and not shown in your Excel sheet.