Jul 10 2021 03:40 AM
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
Jul 10 2021 03:55 AM
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:
In Excel for Windows, you can override this:
Jul 10 2021 04:42 AM
@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
Jul 10 2021 06:04 AM
SolutionThanks 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.
Jul 10 2021 06:49 AM
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.
Jul 10 2021 02:07 PM
Jul 10 2021 02:08 PM
Jul 10 2021 06:04 AM
SolutionThanks 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.