When opening standard CSV files in Excel you may find that Excel did not recognize the fields and simply displays all data in the first column.
The problem is:
- The standard field delimiters for CSV files are commas: ,
- And on American Windows version the comma is set as default for the “List Separator”
- But on European Windows versions this character is reserved as the Decimal Symbol and the “List Separator” is set by default to the semicolon: ;
So you have three solutions:
- 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)
- Replace all “,” with “;” in your CSV file(s)
- Change your regional and language settings
The last solution is my preferred one. To change regional settings go to Start>Control Panel>Regional and Language Options. Click “Additional Settings”. Enter “.” for Decimal Symbol and “,” for “List Separator”.
Now when you open a CSV file in Excel it will automatically find the data fields and open it appropriately.
Hotware: Dirk Paessler's Software Blog 
December 18, 2010 at 23:22
Thanks a lot! This saves me a lot of frustration with importing and exporting text data
January 4, 2011 at 15:44
That solution works, but is a bit distasteful as it overrides regional settings that you want in most other programs. Making CSV region-dependent is among the most stupid decisions I have ever seen… at least Excel could provide an option to tell it that “this is the list separator, never mind the system setting”, as indeed you can for thousands and decimal separators.
January 6, 2011 at 0:51
It is absolutely ridiculous, the decision that a standard “COMMA-separated” file can have different delimiters than the standard. Again, Microsoft showing how they deal with standards.
It’s called “Comma-separated” and not “Semicolon-separated” for a reason.
It becomes hard when we have to import/export data to other countries, as we have to replace the files before sending (which can, sometimes, lead to data corruption, as sometimes there are semicolons inside strings that were not supposed to be replaced).
December 16, 2011 at 11:51
Just add:
sep=;
or
sep=,
on the first line and you’ll be fine.
September 8, 2012 at 12:18
I only found out today what happened when I needed to acces the data of ten questionnaires. If the data would have been permanently lost I would have had to start my MA research all over. Thanks for this service.
April 19, 2013 at 12:32
cool…thank u now i can solve my trouble
January 8, 2015 at 12:37
[…] See this article: Trouble with Opening CSV Files with Excel? The Comma and Semicolon Issue in Excel due to Regional Se… […]
January 1, 2016 at 2:50
Thanks a lot for the tip. I have to deal with “CSV”s from a German bank every year and this makes it easy.
The previous comments show the writer’s ethnocentrism. Why doesn’t the rest of the world use periods for decimals, inches and pounds, and show proper respect for all things American!
March 6, 2017 at 17:10
wow, it works! thanks