Loading
Excel.Tips.Net ExcelTips (Menu Interface)

CSV File Opens with Data in a Single Column

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.

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:

a,b,c,d,e
"a,b,c,d",e
a,"b,c",d,e
"a,b,c,d,e"

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.

Related Tips:

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

 

Comments for this tip:

Bernard    03 Jan 2014, 22:04
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
    Next
End Sub

Function FindLastRow(ByRef obSheet As Object) As Long
    FindLastRow = obSheet.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
End Function
Bernard    03 Jan 2014, 21:33
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
Bernard    03 Jan 2014, 21:09
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.
Jason    02 Jan 2014, 12:51
I have several excel columns that are pulled together by:

=A2&","&B2&","&C2&","&D2&","&E2&","&F2&","&G2&","&H2&","&I2&","&L2&","&N2&","&P2&","&R2&","&S2&","&T2&","&U2

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. - inks-etc.com
PhilP    19 Jun 2013, 15:11
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
Mark STro    18 Jun 2013, 15:03
You can use text to columns also, works great, I'll have to try the other solutions above next time I need them.
Bernard Deham    08 Apr 2013, 04:02
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
pirooz    12 Mar 2013, 04:41
How many numbers in an Excel column into separate units gathered money together
thanks a ;ot
Heba    17 Feb 2013, 06:57
Thank you so much. Exactly what I needed.
Ivo    30 May 2012, 13:26
@Jonas H, great, thank you man !
Jonas H    27 Feb 2012, 10:12
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.
John Polasek    04 Feb 2012, 10:31
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):>

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.