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

Written by Allen Wyatt (last updated April 3, 2020)
This tip applies to Excel 2007 and 2010


17

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

Flashing Cells

Want to draw attention to some information in a particular cell? Make the cell flash, on and off. Here's how you can ...

Discover More

Reversing Cell Contents

Macros are great at working with text. This tip presents an example that shows this versatility by reversing the contents ...

Discover More

Referencing the Last Six Items in a Formula

If you have a list of data in a column, you may want to determine an average of whatever the last few items are in the ...

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)

Correctly Saving Delimited Files

Delimited files are often created through Excel so that your data can be exported to other programs. If the delimited ...

Discover More

Aligning Cells when Importing from CSV

When you import information from a CSV text file, Excel formats the data according to its default settings. Wouldn't it ...

Discover More

Specifying the Number of MRU Files

MRU (most recently used) files can be a great help when you work with a given set of common workbooks. Excel allows you ...

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

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}] (all 7 characters, in the sequence shown) 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 5 - 5?

2020-04-03 11:06:50

Mark

@Paul,

You are absolutely correct and I'm adding this comment to boost your suggestion of using Power Query (or whatever name Microsoft is using today*).

Of the many things of value about Power Query is the fact that it effectively creates a recording of the exact steps the user applies to the incoming data. This makes repeating the process seamless and portable to others. And effectively it creates an audit trail of your data usage.

Thanks for mentioning it.

*In the version of Excel I currently have (Microsoft Excel for Office 365 MSO - looks like V16 to me), Power Query is built in but you use functions on the Data tab to access them with "Get". I've heard that Microsoft will change back to Power Query in the future.


2018-12-20 20:46:39

Al Pecherer

THIS TIP saved me countless hours! Thank you!


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.


This Site

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

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.