Excel 2003 can't export proper CSV

December 14, 2005

Sounds weird doesn’t it? Excel 2003 is supposed to be able to do everything. And mostly it can. But on the CSV export front it sucks tons more than OpenOffice, which has its own deficiencies.

So here’s the story. In OpenOffice, when exporting data into CSV, you can choose both the field separator (typically comma) and content delimiter (typically double quote). Which is a wise thing, because some other systems that eat CSV as their input can be pretty picky about the input format, and for example, may want the field names to be without quotes, but field content with quotes, or something equally silly. And in OpenOffice it’s fine – you can tweak the export format to your liking and then maybe fix the headers with your editor and you’re done.

Now enter Excel 2003. By default, it dumps data with semicolons without any delimiters, separated by ; (semicolon). When looking for help, here’s what you get:

So to change the delimiter, you have to mess with Windows option. Which kinda makes sense in an obscure perverse way. But there’s no way to edit or add content delimiters.

Now I got my stuff done with some searching-replacing in text editor and a 20-second-to-write Perl script that adds and tweaks all the delimiters, but I’m not sure what those people do that are not so much up for scripting.