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

None of these solutions work for me, and the "best" solution is not a good solution at all as it only works with Excel, and no product that generates a csv will add that line to the front.

I have a US version of Windows in the USA, and my control panel Regions and Language settings already have the decimal point as (.) and list separator as (,). My CSV has commas to separate fields (it was generated by SQL Server Management Studio) and still imports to Excel all in the first column. In addition, some of the lines are long enough that Excel wraps them into two rows.

Need a real solution.

Mark, is this related to our PRTG Network Monitor? Are the CSV originating from it? CSV is comma-separated-values, one can only try to work with the options Excel offers: http://superuser.com/questions/407082/easiest-way-to-open-csv-with-commas-in-excel

Fourth solution really works fine

Even though the solution proposed might work in this instance, you might corrupt your data in other ways while using this method. I've written an in depth article on why loading CSV files into Excel using the default settings is a bad idea, and what you can do about it:

http://theonemanitdepartment.wordpress.com/2014/12/15/the-absolute-minimum-everyone-working-with-data-absolutely-positively-must-know-about-file-types-encoding-delimiters-and-data-types-no-excuses/

Summary: Never let Excel open csv files automatically, always -always- use import from text, and manually set the encoding, delimiter and encapsulation

The fourth solution would be fine, except that it is not allowed when the file starts with a ByteOrderMark (BOM) to indicate the file contains UTF-8. Is there a way to use both features?

We know no solution for this. Overall, solution #3 is the easiest once set up, however this can mess with other applications (for example when a decimal point is required when before a comma was accepted, too.)

In addition to Solution #3, also ensure that "Use system separators" is checked under Advanced setting.

Check your Excel Advanced setting:

File -> Options -> Advanced

Make sure "Use system separators" is checked.

For me, this setting was not checked. After checking this setting I am able to view csv file.

Thank you

The region and language setting and changing decimal symbol and list separator finally solved the problem I have had for ages. A big thank you for the help here!

To help people with the dotted lines under a column header issue:

STORED PROCEDURE CODE:

BEGIN
    SET NOCOUNT ON;
      select 'sep=','';                                                    -- Separator = comma
      select 'CustomerID','CustomerName' ;          -- Separate select for header row
    SELECT [CustomerID],[CustomerName]
        FROM [WideWorldImporters].[Website].[Customers]
   END;

IN A STEP ON SQL SERVER AGENT JOB YOU NEED THE FOLLOWING:

@query_result_separator = ',',           -- same separator (comma) as in stored procedure (sep=',')
@query_result_header = 0,               -- no query result header


~~~

This results in a csv file as follows:

sep=,
CustomerID,CustomerName
12,Tailspin Toys - Biscay
10,Tailspin Toys - Wimbledon

which opens just fine in Excel