Tuesday, October 18, 2005

This past week, I was working on a problem that was a little puzzling. we have this application that exports a set of records to excel. The problem that we were having was that halfway down the export, suddenly, dates and numbers were being converted as text. So we had this wierd mixture of a half number, half text export. - not a very good situation for our users who were using this to key off of for other systems. Why, you ask, were they not just getting a database dump? Well, that's a question for a different time and place where rational thinking holds sway and time is more plentiful than flowing rivers of milk and honey.

Anyway, I found the issue and made the corrections. So now, here's the answer should you ever really find yourself facing the same perplexing situation. What happened was that my export script wasn't trapping for certain types of information. now normally, you might think, hey, i'm exporting stuff into a format that excel can read, normally everthing that's coming out of the system should be text, and it should be easy for excel to understand this and interpret it in a proper fashion.

Well, i gotta tell ya, that thinking will work for about 99% of the cases, but then out of the blue excel pulls a fast one on you. What I found is that if you have special characters at the start of a cell export, it will actually try to interpret that information as a formula. So for example, if you have the unfortunate occurance that one of your users decides to try and add text bullets to their text, excel tries to be helpful and interpret that as a formula.

For example:

- a22 is the id of the new system
or
- i just wanted to add this as a test

Should both be interpreted as a function when importing them into excel. As a consequence, excell doesn't know how to interpret the function, which creates an issue with all the rest of the imported cells that follow the offending text. I'm not sure why this happens, but it does....

Therefore, I offer you this updated version of a function that i found for use on http:///www.cflib.org to export queries to excel to include some filtering information just to strip out the offending characters.

it's not beautiful, but it works... (click link to download code)

No comments: