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.