Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

CSV files are automatically associated with Excel but when I open them, all the rows are basically in the first column, like this:

enter image description here

It's probably because when Excel thinks "comma-separated values", it actually searches for some other delimiter (I think it's semicolon but it's not important).

Now when I have already opened this file in Excel, is there a button or something to tell it "reopen this file and use comma as a delimiter"?

I know I can import the data into a new worksheet etc. but I'm asking specifically for a help with situation where I already have a CSV file with commas in it and I want to open it in Excel without creating new workbook or transforming the original file.

share|improve this question
1  
A fast way to open CSV is to use this Add-In. It will add an menu bar button to open/import a CSV the correct way. –  nixda Jul 17 '13 at 9:32
add comment

7 Answers

Go to the Data tab and select text to columns under data tools then select delimited and hit next. Then select comma as the delimiter and hit finish

enter image description here

share|improve this answer
4  
And you have to do that every time after double-clicking *.csv in Windows Explorer? I somehow can't believe there is no simpler way, especially if Excel associates itself with the *.csv file extension. –  Borek Mar 31 '12 at 20:50
1  
Usually a wizard should pop up making you do this process when you open the file, but if it's not, you can do it this way. You can also go to data - get external data - from text and run the import wizard from there. –  Raystafarian Mar 31 '12 at 20:52
2  
That's the problem, I don't want to import any data, I am opening a perfectly valid CSV file that Excel even associated itself with. But OK, if that's how it is I'll probably need to find something else to edit CSV files. –  Borek Mar 31 '12 at 21:20
    
Yes its a PITA, same here with Excel 2003. I also have to use Data\Import. Not really a problem in itself, but I do these imports only twice a year so I regularly forget and try File/Open first ;-( –  Jan Doggen Jan 31 at 18:10
add comment

Apparently Excel uses a Windows 7 regional setting for the default delimiter for CSVs (which is apparently a tab by default). You can change that setting like so.

However, I don't know what other repercussions this will have. I suppose if all the CSV files on your computer are comma separated, then there shouldn't be any problems. But if you have another (likely Microsoft) program that also uses CSV files, and it normally uses CSVs formatted with a different delimiter, then this could be problematic.

E.g. if, say, Outlook uses tab-separated CSVs for importing/exporting contacts, and you receive such CSVs from an outside source (Gmail, another computer, whatever...), then changing this system-wide setting could prevent Outlook from opening these tab-separated CSVs.

share|improve this answer
    
I've tried changing my "regional and language settings" > Formats > Additional settings > List separator, however, it had no effect on opening CVS files in Excel. So unfortunately, it's not a solution. –  Borek Apr 1 '12 at 16:47
    
Same here with Excel 2010 and Windows 7. It was already , in my settings, and Excel is still defaulting to tab. –  rhsatrhs Nov 30 '12 at 22:01
add comment

I don't know if you managed to resolve this issue, but I also had the same problem and sorted it out after some investigations.

Turns out it was a regional setting issue;

Go into your control panel --> Regional Settings --> Advanced Settings and change your list separator to a comma.

Mine was set to semi-colon for South Africa after I changed to Windows 8. After I changed it to a comma, all my CSV's open correctly with Excel 2013.

Hope this helps.

Additional comment:
I used the same steps as Lèse majesté, but I also changed the Decimal symbol from a comma (,) to a fullstop (.) and it fixed my problem.

This is because, by Default, Windows 8 uses a comma as a Decimal symbol and Excel gets confused when it has to use both the characters as separator and Decimal symbol.

share|improve this answer
    
worked for me in excel 2013 –  m0sa Mar 11 at 22:29
add comment

Placing:

sep=<delimeter>

as the first line of your csv file will automatically tell Excel to use that character as the separator instead of the default comma.

E.g:

sep=|
COLUMN1|COLUMN2
data,1|data2
...

will result in pipe (|) being used as the delimeter instead the comma, without the user having to run in through the import wizard first.

share|improve this answer
1  
Could you please give a little more information on this solution. –  Chenmunka Mar 18 at 16:05
    
Thanks, this is what I was looking for. This response needs more support! –  scoota269 Jun 5 at 1:46
add comment

Excel 2010 - In addition to validating your Regional Advanced Settings, be sure to check your Excel Advanced setting:

File -> Options -> Advanced

Make sure "Use system separators" is checked.

share|improve this answer
    
Or, of course, be sure it's not checked if you want to import something that is different from your regional settings. :-) But: it's good to know this setting exists; I never heard of it before. –  Arjan Jan 31 at 18:25
    
These settings only seem to influence the number separators, not the list separator. –  Borek Jul 7 at 10:16
add comment

Changing decimal separator to . (dot) and list separator to , (comma) helped to maintain CSV in normal view in Excel in Norwegian computers.

share|improve this answer
add comment
up vote 0 down vote accepted

This is what worked for me - it is a combination of some of the answers here.

(Note: My PC is using a Czech format settings that format numbers like this: 1 000,00, i.e., comma is used as a decimal separator and space as a thousands separator. The default list separator in system settings is ;.)

  1. I changed the system List separator to a comma in Region -> Additional settings
  2. In Excel 2013, I went to Options -> Advanced and unchecked Use system separators
  3. I set decimal separator in Excel to . and the thousands separator to , (the thousands separator probably doesn't matter but I wanted to make it consistent with the US formatting)

An alternative to steps 2+3 would be to change these settings in the system settings (step 1) but I generally want to have numbers formatted according to a Czech locale.

Downsides: in Excel, I now need to enter decimal numbers using the US locale, i.e. using the dot instead of a comma. That feels a bit unnatural but is an acceptable trade-off for me. Fortunately, the comma key on my num-pad turned to the dot key automatically (and only in Excel - other apps still output a comma).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.