261

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.

Raystafarian
  • 21,963
  • 12
  • 64
  • 91
Borek Bernard
  • 15,019

13 Answers13

230

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

Raystafarian
  • 21,963
  • 12
  • 64
  • 91
213

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.

scottlimmer
  • 103
Krivda
  • 2,231
63

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.

Excellll
  • 12,847
Maxwell Maake
  • 731
  • 5
  • 3
26

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 Control Panel -> Region -> Additional settings
  2. In Excel 2013, I went to Options -> Advanced and unchecked Use system separators (under "Editing Options", which is the first section)
  3. In the same options dialog, 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).

Tomas
  • 8,080
Borek Bernard
  • 15,019
11

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.

Lèse majesté
  • 3,301
11

LibreOffice Calc has a very advanced csv filter that lets you choose separators, formats and encodings. I work extensively with data in various formats and very often need to send these data in Excel format to users. I use LibreOffice Calc to convert csv-files to the desired format, then save as xls.

This may not answer how to do it easily in Excel but it answers how to do it easily, especially as OP in the comments suggests using something other than excel would be an acceptable option.

fsando
  • 119
5

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.

user295069
  • 51
4

A step-by-step guide for the perplexed:

  1. Press the Windows key
  2. Type "intl.cpl" and hit Enter.
  3. Click "Additional Settings".
  4. Go to the "Numbers" tab:
  5. Change the Decimal Symbol to a dot.
  6. Change the "Digit Grouping Symbol" to a space.
  7. Change the "List Separator" to a comma.
  8. Go to the "Currency" tab:
  9. Change the Decimal Symbol to a dot.
  10. Change the "Digit Grouping Symbol" to a space.
Anthony Faull
  • 141
2

I know that an answer has already been accepted, but one item to check is the encoding of the CSV file. I have a Powershell script that generates CSV files. By default, it was encoding them as UCS-2 Little Endian (per Notepad++). It would open the file in a single column in Excel and I'd have to do the Text to Columns conversion to split the columns. Changing the script to encode the same output as "ASCII" (UTF-8 w/o BOM per Notepad++) allowed me to open the CSV directly with the columns split out. You can change the encoding of the CSV in Notepad++ too.

  • Menu Encoding > Convert to UTF-8 without BOM
  • Save the CSV file
  • Open in Excel, columns should be split
Shayne Ephraim
  • 121
1

This from Excel 2003:

  • Go to menu item Data
  • Then Import external data
  • Then Import data
  • For files of type choose All Files (.)
  • Select your CSV file

The wizard helps you from there and is pretty smart:

Import wizard fixed length step 1 of 3

Import wizard fixed length step 2 of 3

Import wizard fixed length step 3 of 3

Jan Doggen
  • 4,657
0

With the newer Excel version 2010 you can open (menu: File, Open, or control-O) a .CSV file as a text file. Then, you directly get the wizard, just as easy as with LibreOffice Calc, where you can select the correct field delimiter.

enter image description here

This only works with files with the proper .CSV extension. My app was to produce CSV output with a .ACC extension, and trying to open that in excel with the method above, leads you to completely different excel corners :-(

Roland
  • 427
0

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

Madhu Rijal
  • 1
0

For my system, the settings were already set to what others here have suggested (Decimal symbol set to . (dot) and List Separator set to , (comma) BUT I was still having the display issues importing a CSV file exported from a different application.

After a little trial and error, I found a solution that opens every CSV file in the correct view.

Here is what worked for me in Excel 2013 with Windows 8:

Control Panel> Clock, Language, and Region> Region> Additional Settings> List separator set to , ; (comma AND semi colon) -> click "apply" and then see if that does the trick.

learn_by_doing
  • 1