Excel favours its own comma-separated value files

Posted by technophile 13/09/2010 at 11:45

I found just now that Excel 2008 for Mac treats CSV files it thinks that it created in a different and better way. Specifically it handles double-quoted strings (containing commas) properly rather than ignoring the quotes and adding a field separator where you don’t want one. So if your scripting task includes creating a CSV file, it might be worth having an empty Excel-generated CSV file withinin your codebase, using the OS to copy that file to a new target, and then writing your data into that. I have not looked to see whether this behaviour is maintained if you work with the file on a different operating system; I suspect it wouldn’t hold up.