Easiest way to open CSV with commas in Excel (2025)

13 Answers

Reset to default

226

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

Easiest way to open CSV with commas in Excel (1)

Improve this answer

answered Mar 31, 2012 at 20:44

RaystafarianRaystafarian

21.9k1212 gold badges6363 silver badges9191 bronze badges

15

  • 76

    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 Bernard

    Commented Mar 31, 2012 at 20:50

  • 3

    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

    Commented Mar 31, 2012 at 20:52

  • 19

    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 Bernard

    Commented Mar 31, 2012 at 21:20

  • 3

    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

    Commented Jan 31, 2014 at 18:10

  • 2

    This 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

    Commented Jan 16, 2018 at 14:58

| Show 10 more comments

209

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|COLUMN2data,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.

Improve this answer

edited Jun 5, 2014 at 1:53

scottlimmer

10322 bronze badges

answered Mar 18, 2014 at 15:27

KrivdaKrivda

2,19111 gold badge1212 silver badges33 bronze badges

10

  • 12

    For this to work in Excel 2013, you must uncheck Use system separators in Options>Advanced

    Michael Field

    Commented Mar 26, 2015 at 7:06

  • 1

    Can 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.

    stefanbschneider

    Commented Jan 26, 2017 at 8:46

  • 2

    This worked for me, even if I have "Use system separators" checked. No other solution worked for me.

    awe

    Commented Feb 15, 2017 at 13:54

  • Confirming that this works in Excel 2016 with "Use system separators" checked. This is great.

    Bas

    Commented May 15, 2017 at 13:57

  • 1

    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

    Commented Oct 25, 2017 at 11:53

| Show 5 more comments

62

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.

Improve this answer

edited Jun 18, 2013 at 15:28

Excellll

12.8k1111 gold badges5353 silver badges7878 bronze badges

answered Jun 18, 2013 at 15:00

Maxwell MaakeMaxwell Maake

72155 silver badges33 bronze badges

5

  • 1

    Already set like this, and ignored by excel. Still doesn't separate on commas.

    Elliot

    Commented Sep 23, 2014 at 20:45

  • Ir probably requires at least a logoff, logon to take effect.

    Gert van den Berg

    Commented Jan 11, 2016 at 10:49

  • Worked for me after changing the decimal symbol, thanks!

    James Cameron

    Commented Oct 14, 2016 at 9:20

  • 3

    Well, I still think it is outrageus that Excel use regional settings for how to read a file format!

    awe

    Commented Feb 15, 2017 at 13:44

  • This will mess up the computer, it is used for other things than this document??

    Jan Bergström

    Commented Nov 6, 2022 at 17:55

Add a comment |

25

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

Improve this answer

edited Oct 22, 2024 at 9:46

Tomas

7,7271111 gold badges5151 silver badges8383 bronze badges

answered Jul 7, 2014 at 10:45

Borek BernardBorek Bernard

14.9k3434 gold badges8383 silver badges9898 bronze badges

5

  • It does not work with Excel 2010 / cz :-(

    Leos Literak

    Commented Nov 27, 2014 at 8:00

  • @LeosLiterak: I just tried it in Excel 2013 though, and it worked.

    Sk8erPeter

    Commented Jan 24, 2017 at 15:06

  • 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

    Commented Dec 4, 2019 at 14:45

  • It messes up the Excel installation, the setting is global and not for the document??

    Jan Bergström

    Commented Nov 6, 2022 at 17:56

  • Works on Excel 2019 @ Windows 10. However I hate this way of solving things, I also have semicolon separated csv's. Excel should recognize it automatically or let us choose, without having to rename csv to txt and/or use stupid Wizards (unneccessarily complicated for valid csv's).

    Tomas

    Commented Oct 22, 2024 at 9:52

Add a comment |

10

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.

Improve this answer

answered Apr 1, 2012 at 0:38

Lèse majestéLèse majesté

3,28111 gold badge2020 silver badges2323 bronze badges

3

  • 4

    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 Bernard

    Commented Apr 1, 2012 at 16:47

  • 2

    Same here with Excel 2010 and Windows 7. It was already , in my settings, and Excel is still defaulting to tab.

    rhsatrhs

    Commented Nov 30, 2012 at 22:01

  • 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

    Commented Sep 1, 2014 at 8:51

Add a comment |

10

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.

Improve this answer

answered Oct 2, 2014 at 14:17

fsandofsando

10911 silver badge22 bronze badges

Add a comment |

4

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.

Improve this answer

answered Jan 31, 2014 at 18:06

user295069user295069

4111 bronze badge

2

  • 2

    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

    Commented Jan 31, 2014 at 18:25

  • 3

    These settings only seem to influence the number separators, not the list separator.

    Borek Bernard

    Commented Jul 7, 2014 at 10:16

Add a comment |

3

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.

Improve this answer

answered Nov 21, 2014 at 12:19

Anthony FaullAnthony Faull

13122 bronze badges

1

  • 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

    Commented Jul 24, 2017 at 9:10

Add a comment |

1

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

Improve this answer

answered Sep 10, 2015 at 19:59

Shayne EphraimShayne Ephraim

11122 bronze badges

2

  • 1

    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

    Commented Oct 26, 2015 at 13:30

  • This was the actual solution for me too. I've been using -Delimeter `t for 10 years, and only just now discovered that it's due to Excel only following it's own rules if the file is Specifically ASCII-encoded. THANK YOU!

    Hicsy

    Commented May 2, 2023 at 7:08

Add a comment |

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

Improve this answer

answered Apr 25, 2014 at 7:36

Madhu RijalMadhu Rijal

1

1

  • Also on my Bulgarian one

    PeterSvP

    Commented Nov 6, 2014 at 21:41

Add a comment |

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.

Improve this answer

answered Oct 2, 2014 at 13:08

learn_by_doinglearn_by_doing

1

Add a comment |

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.

Easiest way to open CSV with commas in Excel (16)

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 :-(

Improve this answer

edited Feb 16, 2016 at 12:07

answered Feb 16, 2016 at 11:53

RolandRoland

39922 silver badges1515 bronze badges

Add a comment |

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:

Easiest way to open CSV with commas in Excel (18)

Easiest way to open CSV with commas in Excel (19)

Easiest way to open CSV with commas in Excel (20)

Improve this answer

answered Oct 22, 2024 at 19:42

Jan DoggenJan Doggen

4,2961212 gold badges4141 silver badges5454 bronze badges

Add a comment |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged

  • microsoft-excel
  • microsoft-excel-2010
  • csv

.

Easiest way to open CSV with commas in Excel (2025)

References

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Kieth Sipes

Last Updated:

Views: 6159

Rating: 4.7 / 5 (67 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Kieth Sipes

Birthday: 2001-04-14

Address: Suite 492 62479 Champlin Loop, South Catrice, MS 57271

Phone: +9663362133320

Job: District Sales Analyst

Hobby: Digital arts, Dance, Ghost hunting, Worldbuilding, Kayaking, Table tennis, 3D printing

Introduction: My name is Kieth Sipes, I am a zany, rich, courageous, powerful, faithful, jolly, excited person who loves writing and wants to share my knowledge and understanding with you.