Excel - CSV files do not open correctly

Introduction
Do you open CSV (Comma Separated Values) files in Excel often? Normally you'd expect when you double click on a file for Excel to recognise it as a CSV, open and display the data correctly. But sometimes that doesn't happen, here's a description of this issue with some possible solutions. I hope this helps!


Problem
When you double click a CSV file it opens in Excel but all the data appears in Column A. Excel does not automatically detect that the file is comma delimited.

Incorrect:

You can see that all the data is in Column A. Excel isn't 'seeing' this file as a table of data, it's just seeing it as text. This makes using filters and pivot tables impossible.

Correct:

The above is what you want. The data are in their respective columns. This is perfect for filtering and pivot tables.

NOTE: The above screen shots are of an example CSV file opened in Excel. Of course what you see when you open your CSV file will vary but the general layout will be the same.


Reason
The Windows 'Region and Language' settings has a parameter 'List separator'.
If you are using English then 'List separator' is set to a comma "," and Excel should open CSV files correctly.
If you are not using English you may have a different 'List separator' and that is what is causing the problem.

To see this:

1. Click Start
2. In the Search box type: Region and Language
3. Double click Region and Language (or open Region and Language from Control Panel)
4. On the Formats tab click the Additional settings button
5. The List separator should be a comma, if it is not a comma, there will be a problem with opening CSV files.

Solution 1
Don't double click CSV files to open them. Don't worry about the language setting. Instead, from inside Excel, click File | Open, select the CSV file, it'll ask you for the delimiter, use ",". This will always work.

Or, a variation on this would be to double click to open the CSV but once Excel is open with the data incorrectly shown on the screen, click Data | Text to Columns. Excel will prompt you for the delimiter.


Solution 2
Change your language setting to English.


Solution 3
Keep your language setting but change the 'List separator' to a comma ",".


Solution 4
Add "sep=," (without quotes "") to the first row of the CSV file.

To do this, open the CSV file in Notepad (or another text editor like Notepad++ for example).

Add sep=, as the first row, similar to my example above.

Click File | Save and exit Notepad.

When you open this CSV file by double clicking it, Excel will see that the separator is a comma "," and format the data correctly. The first row (sep=,) will not appear in Excel.

This is probably the best solution. It's also the most elegant because the user doesn't even know about this. It just works for them!


Conclusion
There's no perfect solution but at least there's a number of different things you can do. I prefer the last one (sep=,) it makes the whole issue invisible to users.


Excel Version?
Excel 2013 and 2010, probably earlier versions too - feel free to write in the comments below about your experiences.