How do I get Excel to import my CSV file?


I have a file claiming to be CSV. It contains 10 fields, all of which are surrounded by double quotes (yes, even the dates and numbers.) 2 or 3 of these fields contain commas. But data in these fields is also surrounded by quotes.


When I try to open this file, Excel completely ignores the quotes and assumes that they are part of the data.

It therefore splits the fields containing commas into multiple columns.

This is bad, at least for me.

Example data

"20051", "", "2009 Sep 30 02:53:23", "SOMETEXT", "", "5000", "2000000", "2008 - 99999 - -99999, 2008 - unk - unk", "191 - SOMETEXT - SOMETEXT, 192 - SOMETEXT - SOMETEXT, 193 - SOMETEXT - SOMETEXT, 194 - SOMETEXT - SOMETEXT, 195 - SOMETEXT - SOMETEXT, 196 - SOMETEXT - SOMETEXT", "45 - SOMETEXT - SOMETEXT, 1162 - SOMETEXT - SOMETEXT, 140 - SOMETEXT - SOMETEXT, 141 - SOMETEXT - SOMETEXT"
"20052", "SOMETEXT", "2009 Sep 08 07:56:50", "SOMETEXT", "", "50000", "5000000", "2007 - 99999 - 99999", "146 - SOMETEXT - SOMETEXT, 145 - SOMETEXT - SOMETEXT, 147 - SOMETEXT - SOMETEXT, 148 - SOMETEXT - SOMETEXT", "103 - SOMETEXT - SOMETEXT, 1131 - SOMETEXT - SOMETEXT"

What have I tried?

I have used the import facility to specify delimiters and such, but this does not seem to help.

I have tried switching "Text Delimiter" from a double quote to "{none}" and back again. This appears to only affect the first column. This would be resasonable if it were possible to click the other columns and apply this setting to each. But this is not the case, and it seems this is intended to work across columns.

What else can I try?

  • 1
    Which Excel version? And is it fully patched?
    – harrymc
    Oct 7, 2009 at 10:14
  • I have seen this in 2002 and 2007. I'm not sure about 2007, but I'm pretty sure 2002 was fully up to date Oct 7, 2009 at 10:16
  • Can you provide a link to the CSV? Or a cut down version that demonstrates the problem? Oct 7, 2009 at 10:20
  • Added Example data above Oct 7, 2009 at 11:18

9 Answers 9


I just tried a quick test that replicated your problem.

I created a 1 line CSV in Word (which uses smart quotes) as test.csv “123”,“4,5,6” and it opened in Excel as you described.

Try replacing “ and ” with "

Having played with your sample I notice that Excel does not like the spaces between fields

e.g. instead of

"20051", "", "2009 Sep 30 02:53:23", ...

you want

"20051","","2009 Sep 30 02:53:23",...

a decent Regular Expression replacement should be able to handle it with

Find:    |("[^"]*",) |
Replace: |\1|

(pipe characters for visual cues only)

Or simply modify the .Net code if you have access to it ;-)

Also, as Arjan pointed out, you may also need to convert the file from UTF-8 to ANSI to prevent cell A1 from containing the BOM and its surrounding qoutes.

I have come across the Catch 22 of ANSI encoded CSV not handling international characters and UTF encoded CSV not being propery handled by Excel; and not found a solution while mainting the CSV. If international character support is required, the XML (or native XLS) formats seem the only way to go—at the cost of simplicity.

  • No smart quotes involved. This file was generated to use std quote marks from a program written in .Net. Oct 7, 2009 at 10:50
  • I did not intend to say UTF-8 files should be encoded differently. But when importing UTF-8, then indeed one must tell Excel what the encoding is. And UTF-8 is hidden somewhere below in that long list of encodings.
    – Arjan
    Oct 7, 2009 at 12:14
  • 1
    @lumbarious: You are a STAR! Works perfectly... I cannot believe that Excel can't deal with this :( Oct 7, 2009 at 12:23
  • 3
    Yup, apparently spaces between fields completely confounds Excel 2011. Removing the spaces corrects the issue and also keeps Excel from including the quotation marks as part of the data. My guess is there is a spec somewhere that states that double quotes only delimit data if they are directly adjacent to the comma. Mar 2, 2015 at 5:34
  • 1
    No spaces between fields! This was the clincher for me - thank you.
    – Ed Graham
    May 18, 2021 at 17:02

This problem has been plaguing me for a number of years. I just discovered the solution and it's alluded to in the answers above but not explicitly spelled out.

It's the space after the comma!

This doesn't import into excel;

"1,000", "2,000"




  • YAAAS! The accepted answer has the real answer to the question buried unlike this answer. The op does not use smart quotes.
    – dardub
    Sep 16, 2017 at 0:46

You could also try CSVEd which is free.


When I try to open this file, Excel completely ignores the quotes and assumes that they are part of the data. It therefore splits the fields containing commas into multiple columns.

If you're not accidentally using smart-quotes like lumbarius suggested, and assuming you're on Windows: that might be due to your regional settings. When double-clicking or using File » Open, then Excel does not ask you for any details, but simply uses these settings.

Still, manual invoking the import function (like you did) should have worked, so I doubt changing the regional settings will help...

  • This seems more like a comment rather than an answer...
    – Metafaniel
    May 12, 2017 at 18:43
  • This suggestion solved my issue exactly! Doing a CSV import (Data > From Text/CSV) wasn't working as expected, but if I just did File > Open on the CSV file, it worked. Then I was able to just copy/paste the whole table into my other workbook. Oct 17, 2018 at 16:34

Have you checked the character encoding of your file? Try setting the character encoding to UTF-8.

You can use notepad2 to change the character encoding.

  • Encoding is reported by Notepad2 to be "UTF8 with signature" Oct 7, 2009 at 10:53
  • Then, while importing, you need to scroll down in the encoding list. UTF-8 is hidden somewhere low in the list. (No need to use UTF-8 encoded CSV files, but as your input already is UTF-8 you indeed need to tell Excel what's to be used. Note that a normal quote-character would be encoded the same in ASCII, Western and UTF-8, but maybe the signature (a Byte Order Mark) or some other characters do mess up?)
    – Arjan
    Oct 7, 2009 at 11:01
  • @Peter Why change the official link to Wikipedia? That makes no sense at all? We don't want to send people around too much, and this certainly doesn't warrant an edit — especially on a two year old post.
    – slhck
    Jun 27, 2012 at 11:04

Just adding to the answers here: focus on the SPACES after the COMMAS in your CSV files. Excel does not like these if you have quoted fields in your data.

Excel likes (4 columns of data):

a, b, c, d

But does not like (3 columns of data):

a, "b, c", d

It does like (3 columns of data):

a,"b, c",d
  • I just hit that problem. I was banging my head against the wall until I got to this note. This is simply amazing. I am running Excel 2017 and that silliness still exists ... Mar 16, 2018 at 17:37
  • The insights in this answer were posted years earlier. This answer adds no new value to the page. Jun 28, 2021 at 0:25

I ran across this problem today and also found a solution that worked for me.

My environment / context: I have a web page with some (form) questions that contain both double quotes and commas. Both the questions and the answers are pulled out of the database and written to an Excel file for reporting purposes.

I had the same problem described above: The questions that included both double quotes and commas were being split into multiple columns in Excel.

For my purposes, using two single quotes in place of a double quote did the trick.

--> on the website, two single quotes look like a double quote to site visitors

--> In the Excel file, again, two single quotes look like a double quote for users viewing the report data

I realize that this solution won't work for everyone, but hopefully it will help someone.


I was able to do this using:


which gives four columns:

|  a  |   b   |   c   |   (1,2,3)   |

Another way of doing it:

  1. Open the file in LibreOffice or OpenOffice Calc.
  2. Set the correct import options, like UTF-8, comma-separated, ...
  3. Store the File as ods or xls
  4. Open the new file in Excel.

This way, you can set the correct encoding like UTF-8, which many (all?) versions of Excel can't handle correctly without BOM and you don't have to worry about a regex that may replace some text inside the field and you don't notice it.

You must log in to answer this question.

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