Move to data-driven decision making with my new NPS survey software.

All my readers receive 10% off for life with code: ian

dumping data to excel

March 20, 2005

I've spent the better part of the last 2 days trying to get a simple CSV formatted file to dump to Excel reliably. What a pain in the ass. I've been doing some updates to a ColdFusion back end system I built for a client a few years back and one update was to do a basic data dump to Excel.

The data isn't nice simple fields but big tables some of which have long text fields with nasties like tabs, commas, and quotes. Anyway I escaped everything, quoted the fields, dealt with the line endings but always something wouldn't work. Often Excel would break fields up incorrectly, etc.

Anyway I found a nice simple solution, at least if you can use Office 2003. It turns out that since Office 2003 there is an XML format that you can use which Excel will open up very nicely. It even lets you specify infinite sheets, workbook info, style info and more. I didn't find any really good details on the MS site though I'm sure they're there. All I did was build a tiny workbook and save as Spreadsheet XML.

The fields were remarkably easy to figure out considering this is a Microsoft thing. So if you're looking to do Excel dumps and you can live with the Office 2003 or newer limitation check it out.

→ Share your thoughts with me on Twitter
Stay In The Loop

I won't bother you with short posts or off topic musings. You'll also receive my ebook on enterprise sales for bootstrappers for free.