Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: CSV File Opens with Data in a Single Column.

CSV File Opens with Data in a Single Column

by Allen Wyatt
(last updated September 10, 2014)


Jan uses a program to create a CSV file. This file can then be opened in Excel for further analysis. When Jan double-clicks the CSV file in Windows, Excel starts and then loads the file. The problem is that the file, when opened, isn't parsed by Excel. Instead of the comma-separated values being in different columns, every record appears in a single column.

The reason for this behavior is probably quite simple and has to do with the format in which the data is stored in the CSV file by the non-Excel program. To understand that, it is critical to understand how Excel opens CSV files.

When you open a CSV file in Excel (either by double-clicking in Windows or by using File | Open in Excel), the program treats any commas in the file as delimiters. This makes sense; after all, the file is supposed to contain comma-separated values (CSV). You cannot override this automatic filtering when opening the file.

So, how does Excel treat the incoming data? Consider, for a moment, if the CSV file contains the following four records:


As far as Excel is concerned, the first record has five fields, separated by commas. The second record has only two fields, "a,b,c,d" and "e." The inclusion of the quote marks around "a,b,c,d" causes Excel to treat the string as a discrete unit. In other words, Excel ignores any commas that may appear between the quote marks.

Given the effect that quote marks have, you can probably figure out how Excel interprets the third and fourth records. In this case, the third record has only four fields, and the fourth record is interpreted to have only a single field.

What does this have to do with the CSV file that seems to be loading incorrectly? It is very possible that the program creating the CSV file is putting a pair of quote marks around each record. This would cause everything in the record to be treated as a single field by Excel, which means it ends up in a single column when the CSV file is loaded.

There are a couple of ways to verify this. The first is to simply open the CSV file with Notepad and look at each record. (Right-click on the CSV file in Windows, choose Open With | Choose Program, then choose Notepad.)

Another way is to rename the CSV file so that its extension is not .csv but .txt instead. When you choose to open this file within Excel, the Text Import Wizard is started. Choose Delimited, click Next, and then you can see what delimiters are chosen. Pay attention to the Text Qualifier; if you change it, you can immediately see at the bottom of the dialog box how Excel interprets the file's records.

If you find that there are extra quote marks around each record in the CSV file, there are three things you can do. The first is to change the program that creates the CSV file so that it doesn't add the extra quote marks—you'll then be able to import with no problem. The second is to go ahead and load the CSV file into Excel, such that each record is in column A. Note that the surround quote marks are gone, stripped out by the import process. This means that you can now use the Text to Columns wizard to separate the data in column A into individual columns.

Finally, the third thing you can do is to create a macro that will open the CSV file and parse it for you. This is particularly helpful if you will be opening, over time, many CSV files that have the exact same format. Your macro could be as elaborate as desired, even formatting columns and processing data as it is imported. Ways to create macros such as this are found in other issues of ExcelTips.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3002) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: CSV File Opens with Data in a Single Column.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Embedding Your Phone Number in a Workbook

Want to provide a bit of contact information in a workbook? A great place to do it (out of sight, but not inaccessible) ...

Discover More

Introducing the Organizer

The Organizer is a great tool for, well, organizing the information stored in your templates. This tip introduces the ...

Discover More

Counting Open Document Windows

When creating macros, it is sometimes necessary to know how many documents are open in Word. This is relatively easy to ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (menu)

Importing Huge Data Files

Sometimes, when importing data created by other programs, you may find that there is too much for Excel to handle. Here's ...

Discover More

Saving in Two Locations

When you save a workbook to disk, you may want to automatically save a duplicate workbook in a separate location. This ...

Discover More

How Excel Treats Disk Files

Workbooks are loaded from disk files, but workbooks aren't the only type of files that Excel can load. This tip provides ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six minus 6?

2018-09-24 13:03:47

Darlin Cox

This is a last resort...I am not a programmer, I am Human Resources, so some of my research on this is a little difficult to follow. I need to pull a csv file from excel 2013 that has the headers but all the information is in the first column. Does anyone know how I can do that? I do have an example of what I need, but I dont know how to attached it to this message.

I did look at my regional settings and I am not able to change the 'List separator'. It is set at , (comma) and cannot be changed from the drop down options. If it can, I would need instruction on that as well.

Thanks so much for all your help on this.

2014-01-03 22:04:23


Hi Jason, Father Christmas is coming tonight, I wrote the whole code snippet for you, hope it helps.
Option Explicit
Private Sub IDontLikeQuotes()
'Bernard E Deham Sriracha-Chonburi-Thailand-3 January 2014
'I've been using VBA since Excel supports it (Excel 5-1995?), it's very effective
Dim strMyString As String
Dim lnJay As Long
For lnJay = 1 To FindLastRow(Me)
strMyString = Me.Cells(lnJay, 1).Value
'I used Me because the VBA module is specific to the sheet where I have datas
'with your active sheet, right-click and select View Code.
'It opens a module where you can copy-paste this code snippet
'macros must be enabled
'put your mouse cursor on Private Sub IdontLikeQuotes;
'left-click and after press F5 to run it
If InStr(strMyString, Chr(34)) > 0 Then
Me.Cells(lnJay, 1).Value = Trim(Replace(strMyString, Chr(34), vbNullString))
End If
End Sub

Function FindLastRow(ByRef obSheet As Object) As Long
FindLastRow = obSheet.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
End Function

2014-01-03 21:33:13


Jason, try this in a VBA module:
Option Explicit
Sub IDontLikeQuotes()
Dim strMyString As String
strMyString = Me.[A1].Value
'could be =Thisworkbook.Sheets("SheetName").Range("A1")
'could be =Thisworkbook.Sheets("SheetName").Cells(1,1)
If InStr(strMyString, Chr(34)) > 0 Then
Me.[A1].Value = Trim(Replace(strMyString, Chr(34), vbNullString))
End If
End Sub
Of course you have to ad a For...Next or Do...Loop to browse all your rows
Good Luck

2014-01-03 21:09:26


Thanks PhilP, but actually the problem was due to parasite characters like line feed carriage return characters -chr(10), chr(13)- added by Excel because I pressed 'enter'. I wrote a simple VBA procedure to clean it up and since then it works fine.

2014-01-02 12:51:17


I have several excel columns that are pulled together by:


Now...I have more than 20,000 rows but ONLY 150 rows are holding on to the "Quote" when I copy the merged column and when I import this to the phpMyAdmin each column becomes EMPTY... Example:

"347,Brother,Ink,Brother MFC5895CW
,Original,2 Pack Black High Yield,LC652PKS;BRT,LC652PKS,LC652PKS,41.95,83.95,125.95,38.95,../InkCartPictures/Brothers/Brother_DR350.jpg,../InkCartPictures/Brothers/Brother_miniLC61BK.jpg,../InkCartPictures/Brothers/Brother_DR200.jpg"
Should be like this...No Quote

348,Brother,Ink,Brother MFC6490CW
,Original,2 Pack Black High Yield,LC652PKS;BRT,LC652PKS,LC652PKS,41.95,83.95,125.95,38.95,../InkCartPictures/Brothers/Brother_DR350.jpg,../InkCartPictures/Brothers/Brother_miniLC61BK.jpg,../InkCartPictures/Brothers/Brother_DR200.jpg

Thank You

Jason Kraft
Inks Etc. -

2013-06-19 15:11:25


The reason that Bernard's import to Access didn't correctly populate is possibly a common issue with CSV files.

For example, if there are five columns A,B,C,D,E in Excel and some of the rows don't use all five columns then CSV thinks the last cell data is the end of the row even if it isn't.

The trick, in this case, is to add a column F for each row containing, for example "X" in each row (even if the last data in that row is in column C). Then when the Excel file is saved as CSV it knows for sure where the last column is.

Please feel free to ask for more info

2013-06-18 15:03:45

Mark STro

You can use text to columns also, works great, I'll have to try the other solutions above next time I need them.

2013-04-08 04:02:55

Bernard Deham

Thanks for this explanation, but my problem is exactly the other way round. I use excel to prepare a csv file that I import in an access table. It worked fine so far, maybe the settings are different on my computer at home and at work... Excel opens the file properly, everything is well ordered in rows and columns but the import in access (with VB6) doesn't work. The last added column in excel appears as a new record in the table, and not under the right header..., in other words csv is not properly generated. What I see on my spreadsheet is not what I have in my csv file. Could you please help me. It affects also some filters on recordsets

2013-03-12 04:41:21


How many numbers in an Excel column into separate units gathered money together
thanks a ;ot

2013-02-17 06:57:31


Thank you so much. Exactly what I needed.

2012-05-30 13:26:00


@Jonas H, great, thank you man !

2012-02-27 10:12:35

Jonas H

If your file contains semicolon separated values, you can go to the Control Panel > Regional Oprions > List separator
Where you can change from "," to ";". This will help for opening correctly in Excel.

2012-02-04 10:31:55

John Polasek

It might help to prominently define CSV as "comma separated value". I was thinking "they can't mean Compuserve" (and they don't-I looked in Google):>

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.