Dumping Data to Excel
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.