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.
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When processing plain text files in a macro, it is often helpful to know how much data the file contains. The normal way ...
Discover MoreWhen you open a workbook in Excel, the Open dialog box always starts within the folder in which you were last working. ...
Discover MoreDelimited files are often created through Excel so that your data can be exported to other programs. If the delimited ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
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: 
Comments