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:

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.

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

MORE FROM ALLEN

Picking a Chart Type

Microsoft Chart offers several different types of charts you can use to display your data. Here's how to pick the type that ...

Discover More

Turning Off Automatic Hyphenation for Parts of a Document

Word can hyphenate documents automatically, if you want it to. But what about those situations where you want most of a ...

Discover More

Inserting Different Dashes

Excel supports several types of dashes. This tip describes those different types and explains how to enter them in a cell.

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

MORE EXCELTIPS (MENU)

Determining If a File Exists

Before you have your macro open and read a file from disk, you'll want to check to make sure it is really there. Here's how ...

Discover More

Cannot Double-Click to Open a Workbook

When you double-click on a workbook in Windows, the Excel program should be started and the workbook loaded. When this ...

Discover More

Pulling Filenames into a Worksheet

You can use Excel for all types of data processing. You may want to work with filenames in a worksheet, but the first task is ...

Discover More
Subscribe

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

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

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.

Subscribe

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.

Links and Sharing
Share