Easiest way to open CSV with commas in Excel

203

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.

Borek Bernard

Posted 2012-03-31T20:39:58.817

Reputation: 11 400

For a number of reasons, beyond just the delimiter, it is a very bad idea to let Excel open CSV (or any other plain text file) using the default settings. Depending on your source file, you -will- corrupt your data. I've written an in depth article on this over here: 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/

– Wouter – 2014-12-16T14:00:42.887

4It is so hard to understand what must be going on inside of Microsoft that Excel still cannot open CSV files just like that. It also breaks numbers etc. in astonishing ways. Even JavaScript does not implicitly convert that badly. – usr – 2017-06-18T09:55:37.713

Sorry to grave dig.. but there's a very good chance the fact your first line doesn't have any commas causes Excel to give up trying – patricktokeeffe – 2017-12-22T00:18:08.560

Just use ; as delimeter and opening file with double-click will separte it in columns in MS Excel. – Hrvoje T – 2019-01-17T19:31:06.630

Answers

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 ;.)

I changed the system List separator to a comma in Region -> Additional settings
In Excel 2013, I went to Options -> Advanced and unchecked Use system separators (under "Editing Options", which is the first section)
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).

Borek Bernard

Posted 2012-03-31T20:39:58.817

Reputation: 11 400

It does not work with Excel 2010 / cz :-( – Leos Literak – 2014-11-27T08:00:32.190

@LeosLiterak: I just tried it in Excel 2013 though, and it worked. – Sk8erPeter – 2017-01-24T15:06:20.910

I just tried it, and it does work for me. @Excel 2013 & Win10 / cz. Thanks.

...The "system List separator to a comma" was necessary to be changed too, indeed. Sad. Pretty deep, in Win10. – Franta – 2019-12-04T14:45:57.730

182

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

Posted 2012-03-31T20:39:58.817

Reputation: 20 384

Problem that I cannot select all file – gstackoverflow – 2014-07-16T11:12:57.013

1@gstackoverflow you can, just select the A column and then do this. – Tom – 2016-02-06T12:24:02.480

How to export this data again in the CVS format we put in? – Tom – 2016-02-06T12:26:31.263

@Tom save as - csv – Raystafarian – 2016-02-08T12:18:08.137

@Raystafarian that won't work as Excel saves that file as a tab seperated CSV instead of comma. – Tom – 2016-02-09T14:44:04.783

@Tom http://superuser.com/q/783060/116196

– Raystafarian – 2016-02-09T14:45:59.730

@Borek - If you think it's a PITA that you have to do this every time again for that same file, then I absolutely agree. Krivda's answer below offers a solution, but it shouldn't be required. MS adds enough wiseguy "features" to its software, but simply looking at a .CSV file and deduce the separator is asking too much. And if they can't do that, they could still remember your choice for this file. (For those who didn't know this: MS has neither the best programmers, nor the best product designers in the world. I know, it's a shock.) – stevenvh – 2017-07-24T09:02:54.740

1This approach is error prone if your data contains characters which are interpreted as separators by default in Excel. E.g. on my machine, Excel splits at every semicolon by default, so your data might look like it is all written into the first column, but that might not be true for all rows. Using this approach will then overwrite that data (although Excel warns me about overwriting something somewhere and than I go and try to figure out what the hell it meant) – Richard Kiefer – 2018-01-16T14:58:48.833

@RichardKiefer it should be based on your system preferences. – Raystafarian – 2018-01-17T00:17:21.963

@Raystafarian I see that, but the issue remains: whatever your default separator setting is, when the .csv file contains it that's were content will be split into multiple cells even when it does not look that way. Easy to look especially for very large files. – Richard Kiefer – 2018-01-18T14:10:13.053

59And 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 Bernard – 2012-03-31T20:50:54.353

3Usually 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 – 2012-03-31T20:52:43.017

14That'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 Bernard – 2012-03-31T21:20:26.530

2Yes 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 – 2014-01-31T18:10:52.687

167

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.

Krivda

Posted 2012-03-31T20:39:58.817

Reputation: 1 771

7For this to work in Excel 2013, you must uncheck Use system separators in Options>Advanced – Michael Field – 2015-03-26T07:06:10.693

1Can you define the decimal separator in a similar way? I want it to be ., e.g., for 1.5 but in my system . is the separator for thousands. – CGFoX – 2017-01-26T08:46:18.920

2This worked for me, even if I have "Use system separators" checked. No other solution worked for me. – awe – 2017-02-15T13:54:22.750

Confirming that this works in Excel 2016 with "Use system separators" checked. This is great. – Bas – 2017-05-15T13:57:37.203

Perfect solution to a stupid problem! (Excel should be able to recognize separator used, or at least asked me, and let me decide!)

Next up: is it possible to force Excel to use another charset as well? All my CSV are in UTF-8, but Excel tries to open them as 'Macintosh', which is creating a lot of problems for me!! – qualbeen – 2017-10-25T11:53:51.813

@MichaelField That's simply not true, al least for Excel 2013. I added sep= header and the CSV is now opening correctly regardless Use system separator is checked. – Teejay – 2018-02-16T09:09:47.863

I just tried it, and it does work for me. @Excel 2013 & Win10 / cz. Thanks. – Franta – 2019-12-04T14:40:24.980

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.

Maxwell Maake

Posted 2012-03-31T20:39:58.817

Reputation: 611

Already set like this, and ignored by excel. Still doesn't separate on commas. – Elliot – 2014-09-23T20:45:07.317

Ir probably requires at least a logoff, logon to take effect. – Gert van den Berg – 2016-01-11T10:49:10.797

Worked for me after changing the decimal symbol, thanks! – James Cameron – 2016-10-14T09:20:13.953

1Well, I still think it is outrageus that Excel use regional settings for how to read a file format! – awe – 2017-02-15T13:44:31.077

Thanks. This setting solved a huge issue for me at work. Excel is so stupid! – Moha – 2018-08-30T12:37:16.707

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é

Posted 2012-03-31T20:39:58.817

Reputation: 3 129

Any idea if we can enter 2 different characters in this box in Control Panel? E.g. ;, to allow both characters as list seaparators? – Dzhuneyt – 2014-09-01T08:51:26.400

3I'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 Bernard – 2012-04-01T16:47:42.683

2Same here with Excel 2010 and Windows 7. It was already , in my settings, and Excel is still defaulting to tab. – rhsatrhs – 2012-11-30T22:01:10.170

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

Posted 2012-03-31T20:39:58.817

Reputation: 71

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

Posted 2012-03-31T20:39:58.817

Reputation: 41

1These settings only seem to influence the number separators, not the list separator. – Borek Bernard – 2014-07-07T10:16:04.713

2Or, 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 – 2014-01-31T18:25:20.237

A step-by-step guide for the perplexed:

Press the Windows key
Type "intl.cpl" and hit Enter.
Click "Additional Settings".
Go to the "Numbers" tab:
Change the Decimal Symbol to a dot.
Change the "Digit Grouping Symbol" to a space.
Change the "List Separator" to a comma.
Go to the "Currency" tab:
Change the Decimal Symbol to a dot.
Change the "Digit Grouping Symbol" to a space.

Anthony Faull

Posted 2012-03-31T20:39:58.817

Reputation: 131

That's fine if you're always using your self-created .CSV files. But it doesn't work when you get a file from someone else who used a comma as separator. – stevenvh – 2017-07-24T09:10:31.130

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.

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

Posted 2012-03-31T20:39:58.817

Reputation: 299

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

Madhu Rijal

Posted 2012-03-31T20:39:58.817

Reputation: 1

Also on my Bulgarian one – Петър Петров – 2014-11-06T21:41:29.633

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

Posted 2012-03-31T20:39:58.817

Reputation: 1

In Excel, DATA tab, in the Get External Data subsection, click "From Text" and import your CSV in the Wizard.

Walter

Posted 2012-03-31T20:39:58.817

Reputation: 19

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

Posted 2012-03-31T20:39:58.817

Reputation: 101

ASCII and UTF-8 w/o BOM are not the same thing. Actually, ASCII fits into UTF-8 w/o BOM seamlessly as UTF-8 has that covered in the standard. As soon as characters not covered in ASCII come around it could just output ANSI or UTF-8 instead... If you output never has anything out of ASCII then it's fine – sinni800 – 2015-10-26T13:30:31.560