Open a csv file in Excel

[Pmaris]

Hello,

I have an issue when opening a csv file in Excel. Before, a csv file opened automatically correctly in excel. Now, I get a file with the content of all the fields in one field, separated by ",".  Any idea why and how to solve this? Is there a setting or an option in Excel that I should change? I am using Microsoft 365, Excel version 2106.

Thanks a lot in advance for useful tips!

Kind regards,

Paul

[#2]

@Pmaris

Excel uses the list separator specified in your system settings. This is usually a comma if you use point as decimal separator, and a semicolon if you use comma as decimal separator.

If you are on Windows:

• Click the Start button or press the Windows key.
• Type intl.cpl and press Enter.
• Click Additional Settings...

In Excel for Windows, you can override this:

• Select File > Options.
• Click Advanced.
• In the Editing options section, there is a Use system separators check box.
• If this check box is clear, you can specify a different decimal separator than the one in your system settings; this in its turn changes the list separator.

[#3]

@Hans Vogelaar Thanks for your reply, Hans. In my system settings, the list separator is a semicolon and I use a comma as decimal separator. In Excel, the 'Use system separators' check box is checked.

In the csv file, the fields are separated by ","  Should I change something?

Kind regards,

Paul

[#4 · Accepted solution]

@Pmaris 

Thanks for checking. That explains the behavior that you describe.

You have the following choices:

1) You might set point as decimal separator and comma as thousands separator and as list separator in Windows. The csv file will then open correctly for you. This will, of course, affect all applications, and might be very inconvenient depending on where you live.

2) Leave the system settings as they are. Change the extension of the csv file to .txt.

Excel will then display the Text Import Wizard when you open the file. You can specify the delimiter there.

[#5]

@Pmaris 

In addition, you may specify delimiter directly in csv file. Open it in any text editor, e.g. Notepad, and add first line, which defines the delimiter, as

Now Excel opens it correctly.

[#6]

Thank you so much, Hans, this works!
Have a great evening and Sunday!
Kind regards,
Paul

[#7]

This also works, thank you so much Sergei!

Have a great evening and a nice Sunday!
Kind regards,
Paul