Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier 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 April 28, 2015)

15

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 (11264) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel 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

Finding the Size of Individual Worksheets

Your workbooks can contain many, many worksheets. Which of those worksheets are the largest, however? Here's some ideas on ...

Discover More

Determining the RGB Value of a Color

Excel allows you to fill a cell's background with just about any color you want. If you need to determine the RGB value of ...

Discover More

Listing the Settings in a Template

Templates allow you to define and collect many formatting settings that control how your documents appear. Getting a report ...

Discover More

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!

MORE EXCELTIPS (RIBBON)

Use Filenames that Sort Properly

When storing your Excel workbook, you need to specify a file name to be used for the workbook. Take a moment to consider how ...

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

Adding a File Path and Filename

If you need to stuff the current workbook's filename and path into a cell or a header or footer, you'll appreciate the ...

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:

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. 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 seven minus 6?

2016-04-13 10:48:31

Scott

I ran into this issue attemting to open a file with encoding set to unicode. After encoding as ascii or ansi, the file would open in columns as expected.


2016-04-04 21:50:25

J D

Ferdie Wamala
Worked perfect thanks!!


2016-01-07 05:24:49

Ferdie Wamala

Found the solution here, its all about your date formats in control panel list separator under Region and language » Formats » Advanced . Yours should now be showing semicolon yet it should be a comma for US and UK DATE FORMATS

http://superuser.com/questions/238944/how-to-force-excel-to-open-csv-files-with-data-arranged-in-columns


2015-09-06 18:15:22

Terry

I have the same experience as Roy Green

I have downloaded CSV files from my bank
Opened them in excel and pasted them into a cashbook
This has worked well for years

I am on Office 365 and have just upgraded the machine to Windows 10.

This month the CSV file opens with all the data in a single column

It is not because the bank has changed its format because I get the same result if I open an old (previous month) bank file that has previuosly worked.

I haven't changed any excel settings.





2015-05-12 17:35:57

Roy Green

My problem is that Excel 2013 has opened it correctly for every previously existing CSV file, UNTIL JUST AN HOUR OR SO AGO, and now it won't open ANY previously existing CSV file correctly.

What happened? Yes, I've checked the system separator (it is still a comma).


2015-04-30 06:04:20

Jeffrey

@ Darwin...

In your first import screen you have the option of setting the first row of import. Figure out what row you need using the preview window in the lower portion of the dialog box. For example: In the files I export the first five rows are all of the data information, the data headers are in row six, and the data begins in row seven. Specifying begin import at row seven elimantes all of the information in rows one thru six that I don't need.


2015-04-28 16:15:32

Andrew

The biggest problem I have with CSV files is that Excel assumes that all dates are in American Format MM/DD/YYYY.

Therefore I always use TXT files except where the source is Excel


2015-04-28 13:11:19

Paul

My approach to this would be Power Query - a free add-in for Excel 2010 and 2013 you can download from Microsoft.

With this, I can import a specific text file, or any text files in a specified folder. Using commands on the Query Editor's ribbon, I can then remove the speech marks and split the column at the comma before loading the result back to Excel as a Table.

Replace the specified file with a new version, or add new files to the specified folder and refresh the query to see the latest data imported.

Search for excelisfun Power Query on YouTube for 12 short videos showing what Power Query can do.


2015-04-28 09:47:44

Darwin Bell

Is there a way to get Excel to ignore headers when importing a .csv or .pdf file?

Thanks
Darwin


2015-03-23 04:41:10

bernieseville

Hi, Can you help me how to format csv files before downloading. I mean,
When I have the windows XP before, the datetime of he data that i download usually from server is like this 3/9/2015 10:54 but when I upgraded my laptop and have MS Office to 2013 I've got this weired datetime 03 9, 15 10:54:21 AM. I just downloading the same thing in the same server.

Thanks for you help


2015-02-06 20:36:40

awyatt

Lionel: No, but you can always run a macro manually after the CSV is loaded.

-Allen


2015-02-06 16:38:59

Lionel

Is there a way to force a macro to run when a CSV is opened by Excel?


2013-03-01 11:12:45

manuel

You say, "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."

Well, you'll have to explain how to do that, because my quote marks still there.


2012-02-04 16:00:41

Bill

A 4th option to enable correct pasting would be to open the CSV file in MS Word (or similar word processor) and use the Search and Replace feature: Search for " (one double quote) and Replace with (blank [not space, just leave the Replace field empty]). Then select and copy the stripped file (CTRL A, CTRL C), move to Excel and paste (CTRL V). There is no need to save the word processing document.


2012-02-04 05:26:43

PB

I hit this problem on a csv file import where 1 field contains date time, while I want to make calculations on the date. So I added an extra column after, then used text to columns to split it into 2 columns ( not forgetting to tell excel the format of the date as part of this). After this, I can make all of the date calculations I want.


Newest Tips
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