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.


9 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:




    on the first line and you’ll be fine.

  5. Jan den Ouden Says:

    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.

  6. blooe Says:

    cool…thank u now i can solve my trouble

  7. Fixed: How to open semicolon delimited CSV-files in US-version of Excel #answer #computers #programming | StackCopy Says:

    […] See this article: Trouble with Opening CSV Files with Excel? The Comma and Semicolon Issue in Excel due to Regional Se… […]

  8. rwcarson Says:

    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!

  9. Virdian Pratama Says:

    wow, it works! thanks

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: