Excel vs. CSV well, that's silly...

I came across an interesting bug today with Microsoft Excel 2012. Historically, Excel has had this lovely issue of not being able to handle importing CSV data when there is an endline character in the middle of a field. So if your file looks like this:

"coffee","delicious coffee from Brazil
*mud flavor available!","10.99"
"green tea","tea which is green","5.99"

Excel will see the endline in the first record and immediately assume that the end of the line means the end of the record. Creating a second row with the name “*mud flavor available!” and a description of “10.99” which is clearly not what we want.

Today I found out that for whatever god forsaken reason, Microsoft has decided that when you double click the csv file to open it, these situations are handled properly. However if you attempt to do a data import from inside Excel, the behavior changes and Excel is unable to handle the endlines.

Another important thing to note is that Excel will not recognize \n as an endline character, it has to be \r\n.

Excel also appears to be unable to open csv files from the File > Open menu.

Note: this was noted & tested on Excel 2012 for OS X

blog comments powered by Disqus