About

You’re visiting the personal website of Joost Schuttelaar.

Take a look

Contact

You can e-mail me at jelephants @ jstsch.com (anti-spam: please remove the mammal with the big snout).

Twitter feed @jstsch

Sorry, feed is gone. API has been killed by Twitter.

Recent posts

Excel 2007 handling of CSV files

Wednesday October 28 2009

Excel comma semicolon Sometimes you wonder what century we’re living in. Things that should be simple and ‘just work’ often don’t.

I whipped up a quick CSV export function, which results in files such as:

"name","department","phone"
"john smith","accounting","555 1234"

Pretty standard. UTF-8. Double quotes (") in fields repeated ("") to escape them. Works fine in Apple Numbers, Excel for Mac and my Excel 2007 on Windows XP (VMWare).

However, a coworker was unable to open the files. Also using Excel 2007, on Vista in this case. After many fruitless attempts changing the format, such as unquoting certain fields, changing line endings to \r\n/, it appears that a Dutch Excel 2007 handles CSV files differently than a US Excel 2007… The European localized version only accepts fields terminated by semicolons, whilst the US version needs fields terminated by commas.

This makes CSV export for Excel impossible to create reliably, unless you add some sort of radio button to ask if somebody runs a US or European version of Excel. Or if you do some browser header sniffing and hopes that this person doesn’t run the browser in a different language than Excel… (which is quite often the case in The Netherlands).

Only resort – add an extra button: Export to Excel XML. Gahhhhh…

Anyway, I hope this post is helpful for someone who runs into the same problem as I did.

Comments

alessandro @ Saturday May 21 2011 13:13
this sounds like it could be due to your coworker's windows internationalization settings. working with excel and csvs can indeed be a pain..
jstsch @ Saturday May 21 2011 14:59
Yep, it is. An even crazier way, which works: Output a bit of HTML with a <table> in it. Then serve that stream as a .XLS file with the application/vnd.ms-excel mime type.

Disgusting, but it works very reliably... in Excel and OpenOffice. Not in Apple Numbers. Sigh.

(never shown, not required)
(not required)