Why you shouldn't open .csv files in Excel

PingBack from http://topsy.com/tb/bit.ly/5tPrJ1

I disagree. This is why you shouldn't EDIT csv files in excel. Excel works just fine for viewing the files, and is a good tool for reviewing files. Just don't change anything.

It can get even worse. In Excel 2000 days (and may still occur today), it was possible to load in a CSV file, make NO edits, save it as CSV and then when you tried to reload it into Excel it would fail with invalid format errors.

That's right, the specification that Excel used when SAVING CSV files is actually different to the specification used when LOADING CSV files.

I can't even start to explain my reaction when I found this out the first time.

Yep - I OPEN them, but I don't SAVE them.  I think it's a great tool to review a CSV file, but really quirky sometimes when it comes to re-saving.

Jamie,

I see the issue. Excel drops the text delimiters (guotes) from the first record. But I'm not sure exactly why it's an problem.

Excel removes the text delimiters when the file is opened, rather than when it's saved. When you save the file, a warning is displayed to let you know that if you choose to keep the CSV file format, some contents may be lost. Seems like Excel is providing lots of disclosure here.

It's also worth noting that Excel removes the text delimiters because they aren't needed. Text delimiters in a CSV file are typically used when there are symbols in the value that are also used as field delimiters (i.e., a comma). For instance, when I changed the first record in the sample CSV file to read

a,"This is some really, really, really long text"

and then attempted the experiement, Excel saved the text delimiters exactly as written, because they're important to maintaining the structure of the CSV file.

Is there a reason you'd need to keep the text delimiters in your original example?

There are worse problems than this, due to how Excel does type inference. But the easy work around is to not "Open" the file, but rather "Import" it. Then you have options for configuring correct data types, etc.

Additionally you are moving between having text delimiters in field 1 and not having them in field b, that's a much bigger problem when trying to load a raw file than anything Excel may have "mangled"

This sounsd like a red herring to me.

Hey Cliff, what happens to your CSV date fields when you open them in Excel?  The answer, "Lord only knows"!

Occasionally I'll use Excel to view CSV files when I need to have it easily sorted into columns or rows, otherwise, Notepad2 gets the most use.

wow, my typing is almost as bad as that example

more coffee please.

A user typed CNA, which is a big insurance company, and it was automatically changed to Can - someone decided that they know better than the user what the user intends to type, and that someone was wrong. Also there was some time when I was figuring out why my automated process that was opening and parsing xls files was hanging. When I started that hanging process myself, and when that process opened and xls file, I got a pop up window advertising something related to MS, like Live Search or something - someone decided that that advertisement was more important than our work. Folks got rather annoyed and never even tried the stuff which was advertised so aggressively.

Well, your all right and you are all wrong.

There is NO specification on how a csv file, csv editor, or csv reader should work. If you do not like how Excel handles CSV Files then don't use it. In excel the " double quote is a qualifier for text that contains a comma, so for example if you replaced you example with

a,"This is some text"

b,This is some more text

c,"This is more text, it really is"

the c would then have in the second column

This is more text, it really is

If you didn't have the quotes on there c would have 3 columns now.

Then when you saved this back out to CSV in excel your quotes would be back on there. This, is how excel handles this and why. There are options when you import a CSV that allow you to override this, this double quote is the text qualifier, change it when importing it if you want some different behavior and even specify what type each field is. For example one of the things we do is used CSV exports of part numbers, a part number might be 0030040056 and that is a valid text field, however if you just import into excel it thinks it is a number and then you get only 30040056 so you have to tell excel this column is text before you import it.

Excel is quite a good tool for working with CSV files. Just most people don't understand there is no set specification for what a CSV file is and how it is structured.

If you want everything specified and structures exactly the same everywhere and automatically understands types then don't use CSV. Use XML, it has a standard and a specification and everything is handled consistently.

I have run into this problem of Excel destructively manipulating CSV files. The truncation of leading zeros on numbers was probably the most frustrating event.

I am having difficulty locating the information on the Microsoft web site, but I recall that when opening a CSV file, Excel looks at the first 50 rows and does a best guess on the appropriate data type of a particular column. This is why the leading zeros get truncated from numeric-looking data.

My recommendation is to avoid the use of CSVs in any workflow where users of any kind could open to edit data and/or open to confirm data. In my opinion it is far too easy to open to edit or confirm a CSV file in Excel rather than a text editor. Any processes downstream after an Excel edit could either have invalid inputs or cause the process to break. Any open to confirm data in Excel can yield the wrong view of the data contained within the CSV, plus the tendency to want to edit and save the CSV in Excel is great and should be avoided.

Jeff Parker's recommendation of using XML is a very good one. For earlier versions of Excel, the *.xls file extension is actually several different document type formats, including binary and most importantly xml. The xml-flavored *.xls file allows for the defining of column data types and additional formatting. For Excel 2007 you can get more information here: http://msdn.microsoft.com/en-us/library/aa338205.aspx

Bottom Line Suggestion: If you must use CSVs (XML or fixed length is not an option) in some workflow then I strongly recommend *never* opening them with Excel. If you must allow for user editing/confirming of the CSVs then I recommend not allowing access through the file system (and thus Excel) but rather through a WinForms or ASP.NET application or another abstraction.

Jeff -

My point being, if I was dealing with a raw file input with the schema:

a, hello

b, "HELLO"

c, "goodbye"

I'd be more concerned with why my input is mangled BEFORE I inputted it into Excel or any other app. Blaming Excel for unfollowed input schemas is in fact a specious argument.

We are in total agreement with the summation of your findings when you say:

"Excel is quite a good tool for working with CSV files. Just most people don't understand there is no set specification for what a CSV file is and how it is structured.

If you want everything specified and structures exactly the same everywhere and automatically understands types then don't use CSV. Use XML, it has a standard and a specification and everything is handled consistently"

Excel may not be perfect but, if you give it broken input, expect broken output.

Jamie,

 True, but if you have alot of records in your csv, a text editor could be a rough time ahead. What I tend to do, is make a copy of the file in excel as a .xls file (or .xslx in 2007). It's habit for me at this point and prevents issues of messing up the format while I investigate. It also allows me to highlight rows of interest when doing data analysis. Right tool for the right job is different for each of us.

Cliff

Wes,

  Microsoft spins a wheel of corruption and spits out whatever format it lands on. =)

Point taken.

Cliff

Excel does not display leading zeros in numbers. This can be a big misguiding factor at times.

Also I am amazed to see such a heavy discussion on this kind of topic :) Jamie, may be you should convert your blog into a forum :)

Excel does not display leading zeros in numbers. This can be a big misguiding factor at times.

Also I am amazed to see such a heavy discussion on this kind of topic :) Jamie, may be you should convert your blog into a forum :)

Excel does not display leading zeros in numbers. This can be a big misguiding factor at times.

Also I am amazed to see such a heavy discussion on this kind of topic :) Jamie, may be you should convert your blog into a forum :)

Excel has a few issues that I've seen when getting the data into SSIS, some are technical some are users. It's great because it's flexible but this is also one of it's issues when processing data

It's not WSISG.

It's not relational.

It supports mixed data in columns and then provides meta data based on a sample of the first few rows.

It can mangle dates, particularly when switching between different regional settings.

Formatting can make a 3 row sheet look like a 16000 row sheet.

Users like to put extra rows with comments or messages at the top.

Worksheets get moved about and relabelled.

Users put important information into comments or have significance with regards position or colour of cells such as creating calendar data in excel.

The one thing that I've seen SSIS excel with (pun intended) is converting a pivot chart back to relational data. I unsed an unpivot followed by a conditional split and a merge join.

This is but the tip of the "Why Excel is horrible for data" iceberg. Seriously. It's horrible, aweful in at least 10 ways when put to use by users who are trying to manipulate tables of data rather than spreadsheets. But that's what about half of business users do...

This is when it gets really frutrating.

I have a column with text like "c:\filesJan10"

It is excel proper, so I could format the column to the type of my liking, like Text "Text format cells are treated as text even when a number is in the cell. The cell is displayed exactly as entered."

Lets replace c:\files with blank ''. Excel will change the format to date format. But I explicitly told it the column was text.

This bites me even with a proper excel spreadsheet, not just when using a data table. I can't cut and paste from the same spreadsheet/workbook without excel trying to reformat - ignoring my formatting I just specified.. Where is the freakin' "ON" switch so I can switch this behaviour off?

Hmm...  Wish I had found this earlier.  :-(

There is a specification for CSV.  It's called plain text.  Text is text.  MS should not be trying to treat it as anything other than text.  Even when you tell Excel all the fields are text fields it still ends up modifying some of the values.

I agree with the last person.  Where is the bloody off switch.  That's all that's needed.  Treat everything as text and all is well.  Adding/removing quotes around a field is not a big deal because good CSV parsers can handle that.  If in doubt, look at the source version of PHP's.  It's a simple nested loop.  But, stripping leading zeroes, changing date formats, etc., is a BIG problem.

This is a bug in Excel.

Vis,

Once you format a column or entire sheet as Text, excel preserves that setting and works well with the example you have mentioned here.

Also while copy & pasting such content, if you want to preserve the original formatting use Paste Special and select 'All using source theme'. This is make sure the original content and formatting is transferred to new cells.