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)

Tuesday, October 04, 2005

So, it's been a big week. At work, my main man Tarik has decided to leave for greener pastures. The guy is good, and an awesome friend. I'm really going to miss commuting with him and having conversations about the state of the union and such. He's been a huge influence at eBay. If anybody ever needs a top notch web application architect, he's your man.

This blog is supposed to be about toys and web development and such, but you know, at this moment, that stuff isn't on my mind. I've been pondering about a different topic since this morning when I found out about the supreme court nominee that Mr bush has nominated. I gotta say I don't know anything about her qualifications or anything about what she has done, but I keep thinking about two facts. 1. She has worked for the president before and been a lobbyist in Texas. 2. She has never been a judge before. Now, I have a conservative bent to me, no doubt about it, but I definitely don't rubberstamp everything that Mr bush or the republican senators do. And I gotta say that this nomination strikes me as an incredible display of cronyism and favoritism. You'd think bush would have learned his lesson from the Fema affair, but apparently not.

Sheesh George, how about hiring someone who at least has had some judge experience... You know you could probably get whatever justice through that you wanted. I have to think that there are more qualified candidates out there....