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.
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.