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

When opening standard CSV (Comma Separated Values) files in Excel you may find that Excel did not recognize the fields and simply displays all data in the first column.

The Problem

The problem is:

• The standard field delimiters for CSV files are commas: ,
• On American Windows versions, the comma is set as default for the "List Separator", which is okay for CSV files.
• But on European Windows versions this character is reserved as the Decimal Symbol and the "List Separator" is set by default to the semicolon: ;

There are Three Solutions

Choose one of these possiblities:

1. 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)
   -- or --
2. In your CSV file(s), use a text editor to replace all commas , with semicolons ; -- or --
3. In your Windows Control Panel, change your Regional and Language settings

Solution #3: Changing Regional and Language settings

The last solution is our preferred one. To change regional settings,

• go to Start | Control Panel | Regional and Language Options
• Click Additional Settings
• For Decimal Symbol, enter a dot: .
• For List Separator, enter a comma: ,

Now, when you open a CSV file in Excel it will automatically find the data fields and open it appropriately.

Windows Region and Language Settings - Click link to enlarge

Sorry, I think that solution 2 is not a "fully" valid one:

• at least in my reports, some columns (those that are not "RAW") employ "," for decimal ("3,45 kbit/s)"; so when you replace "," with ";" you get a somewhat ugly result ("3;45 kbit/s").
• besides, raw columns use "." (dot) for decimal symbol; so you would replace them with "," (comma)

Instead, I would say:

• replace /","/ (including quotes) with /";"/
• replace /./ with /,/

Problem is that (at least with my settings) RAW columns and "formatted" columns have different symbols for decimal and third digit symbols.

I'm afraid only 1 and 3 would work without further editing.

Regards Rodeca

Disclaimer: I'm an absolute newbie, so may be I'm messing everything (but I'm now fighting with these reports) ;-)

Disclaimer 2: I tried my best, but sure you'll need to translate _my_ English to _true_ English

Great. The only reason I signed up for this site is to give a upvote to the "Fourth solution", but the site is telling me I need more points for that.

The "Fourth solution" works! And it is so simple and it works with a little change. I downloaded a CSV spreadsheet from the US and tried to open it with my MS Excel 2010. It did not work. Solution #2 did not work for me, too.

But solution #4 works if you do the following:

1. Open the CSV file via a simple text edit / note pad editor. 2. Go to the first line and add above that line a new line with sep=, (with a comma, not with a semicolon). In my case it worked this way. My CSV was comma separated. 3. Save the file and open it with Excel 4. Win

Thanks for your feedback and intended upvote. (upvoted your reply instead :-)

The semicolon was only intended as example and can be replaced by any character being the real separation character in your CSV file.

I'm glad this worked for you.

I would give the sep answer an upvote, but I need 15 rep. IMO this is the best solution as it will make your app UNIVERSAL

first solution better