Trouble with Opening CSV Files with Excel? The Comma and Semicolon Issue in Excel due to Regional Settings for Europe

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.

About these ads

4 Responses to “Trouble with Opening CSV Files with Excel? The Comma and Semicolon Issue in Excel due to Regional Settings for Europe”

  1. Brecht Says:

    Thanks a lot! This saves me a lot of frustration with importing and exporting text data

  2. jakobengblom2 Says:

    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.

  3. dumbxcel Says:

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

  4. Natali Ardianto Says:

    Just add:

    sep=;

    or

    sep=,

    on the first line and you’ll be fine.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: