Alright boys and girls, here is an issue and its solution that had me going NUTS for a couple of hours. I actually wrote and implemented three DIFFERENT solutions trying to find a way around the problem, only to find out in the end that I kept carrying the true issue with me.

The Scenario

My user clicks a link and a window pops up allowing them to save or open some exported data as an Excel spreadsheet. Of course, it all worked fine on my machine (two of them, actually), but when my boss tried it on his, he got an error. Not cool. Back to the drawing board.

The Challenge

My first attempt (which worked fine for me) was to create a true excel spreadsheet by streaming the data in binary to a file (it's a bit complicated, but cool). Excel 2007 was able to open it just fine, but my boss' older 2003 could not. So, I decided I'd just fall back on the old reliable CSV type file rather than try and troubleshoot the first approach in too much depth. I coded my CSV export and for the life of me could NOT get my Excel to open it without first flashing me with two nasty error messages, paraphrased here:

"The file you are trying to open '[file name here]' is in a different format than specified by the file extension."

"Excel has detected that '[filename here]' is a SYLK file, but cannot load it."

What the heck??? All we're talking about is some text separated by a comma. And Excel doesn't recognize the format??? Content headers are right; file extension is right; Open it up in a text editor and it sure looks fine to me; What's going on?

I reworked the code to use tabs as delimiters; same symptoms.

The Solution

After dicking with it for a couple of hours, comparing known working CSVs with the one I generated, comparing non printable character strings such as line feeds (which is what I suspected the issue to possibly be), I finally figured out that the TRUE problem was (and I grit my teeth in frustration when saying this): the name of the first column in my data.

NEVER allow the first column in your data to be called "ID". AUGH!!!! (had to scream). Apparently, using "ID" is a trigger to Excel that your file contains a symbolic link to another spreadsheet somewhere. I know, I know, for every OTHER type of file that Excel opens it relies on the file's EXTENSION to determine the type; but not in this case.

Moral of the story: NEVER allow the first column in your CSV data to be called "ID".

Lesson learned, I'm now one small step closer to Nirvana.