I have trouble opening CSV files with Microsoft Excel. Is there a quick way to fix this?

Votes:

0

Your Vote:

Up

Down

Excel does not recognize the fields and displays all data in the first column. How do I set the delimiters correctly?

csv excel microsoft office region windows

Created on Mar 11, 2010 2:52:31 PM by  Daniel Zobel [Paessler Support] (24,941) 3 3

Last change on Mar 11, 2010 3:12:54 PM by  Daniel Zobel [Paessler Support] (24,941) 3 3



Best Answer

Accepted Answer

Votes:

1

Your Vote:

Up

Down

Fourth solution

Add an extra line to your CSV file to tell Excel what the seperator is. Add the folowing line to the top of your CSV file:

sep=;

This will tell Excel to use the ";" character as seperator for the next lines. This first line is concidered an instruction and not shown in your Excel sheet.

Created on Oct 31, 2010 4:12:25 PM by  PRTGToolsFamily (5,544) 3 2

Last change on Nov 2, 2010 11:57:52 AM by  Daniel Zobel [Paessler Support] (24,941) 3 3



9 Replies

Votes:

1

Your Vote:

Up

Down

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

Windows Region and Language Settings - Click link to enlarge

Created on Mar 11, 2010 2:53:02 PM by  Daniel Zobel [Paessler Support] (24,941) 3 3

Last change on Mar 11, 2010 3:47:48 PM by  Daniel Zobel [Paessler Support] (24,941) 3 3



Votes:

0

Your Vote:

Up

Down

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

Created on Oct 30, 2010 11:09:46 AM by  rodeca (0)



Accepted Answer

Votes:

1

Your Vote:

Up

Down

Fourth solution

Add an extra line to your CSV file to tell Excel what the seperator is. Add the folowing line to the top of your CSV file:

sep=;

This will tell Excel to use the ";" character as seperator for the next lines. This first line is concidered an instruction and not shown in your Excel sheet.

Created on Oct 31, 2010 4:12:25 PM by  PRTGToolsFamily (5,544) 3 2

Last change on Nov 2, 2010 11:57:52 AM by  Daniel Zobel [Paessler Support] (24,941) 3 3



Votes:

1

Your Vote:

Up

Down

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

Created on Sep 9, 2011 2:36:08 AM by  konterkariert (10)



Votes:

0

Your Vote:

Up

Down

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.

Created on Sep 12, 2011 9:53:37 AM by  PRTGToolsFamily (5,544) 3 2



Votes:

0

Your Vote:

Up

Down

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

Created on Sep 28, 2012 10:07:02 PM by  NeoisTaken (0)



Votes:

0

Your Vote:

Up

Down

first solution better

Created on Mar 16, 2013 5:18:36 AM by  hary25 (0)



Votes:

0

Your Vote:

Up

Down

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.

Created on Feb 26, 2014 6:55:14 PM by  Mark Murphy (0)



Votes:

0

Your Vote:

Up

Down

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

Created on Feb 27, 2014 5:03:39 PM by  Torsten Lindner [Paessler Support] (19,480) 3 1



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.

PRTG
Network Monitor
Intuitive to Use.
Easy to manage.

150.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Visit
www.paessler.com

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

Top Tags


View all Tags