A Bug’s Not a Bug until It Bites You

Ow! I have just been bitten. No, not by a bug, but by my own stupidity. Stupidity may be a bit harsh since the problem was a result of something I have never experienced before. I have been working with FoxPro for over 18 years and in that time I have used the IMPORT command on many an occasion to take data from (among other things) an Excel spreadsheet and place it into a table for further processing. I would guess that most of you have done the same thing. Once you have done it several times, doing it again is not a big deal. Until yesterday!

What happened yesterday has never happened to me before in all my years of application development. Thinking about it, I am amazed that it took this long to have it happen. I am going to speak generically about the incident. Depending on your experience you may think that I “should have known better”. OK, wise guy, you do not have to read any further. Those of you who might want to avoid the trap I fell into may continue to read.

Before yesterday, I have always thought (based on my IMPORTing experience) that when importing data from a spreadsheet, the resulting field data types are all characters. To the best of my knowledge, EVERYTIME I have ever done this, all fields were a character type regardless of the information contained in them. Thus, in order to further process the table, I usually had to use one or more of the VFP character string functions to convert and manipulate the newly imported “character” data. How many of you thought the same thing? Namely when you IMPORT data from a spreadsheet it all gets pulled into character fields. Those of you who raised your hands, thank you, but you are wrong, grasshoppers.

All these years, the reason that all of the resulting fields are of a character type is because (usually) the spreadsheet that is being IMPORTed has a header row and the IMPORT process interprets that first row as a character type and thus, all subsequent records are the same type. As a sidebar, almost always, part of the “cleanup” process for the table was to DELETE that first row because it was “bogus” data and by the time I wrote the routine, I didn’t need it because I knew what data each column contained and how I wanted to manipulate the contents.

To continue this saga... for the past four months, spreadsheets I receive on a monthly basis have always processed flawlessly, but yesterday, when attempting to process the same spreadsheet I got all sorts of data type mismatch errors. How was that possible? Ah, you are way ahead of me, aren’t you? Well, you are right. THIS month the person sending the spreadsheet (who had been taught not to add, delete, or move columns) decided to “help” and removed the first row – the row of column headers. The removal of that row, made the first row actually contain viable data. Some numeric (SSNs, phone numbers, numbers) and some character strings. Now, when my flawless routine (to convert and manipulate) kicked in, it blew up because I was trying to VAL() some (now) numeric fields that had (always) come in as a character strings and it also tried to perform some manipulations on (what were character strings – i.e. the social security number) numeric fields because during the IMPORT process the column contained only numbers. What a mess!

So, should I have known? A quick glance at VFP help did not indicate (I could have missed something) that the IMPORT process attempted to assign data types to the fields of the new table, BUT IT DOES! Bottom line, some day, perfectly good, working code may fail for this reason.

For now, my immediate fix (to be tested over the next few months) is to check the second column of an AFIELDS() array for the character type of select fields (that could either be a character or a number) depending on whether or not the first row in the spreadsheet contains column headers. Then depending on the results, fork the process to manipulate data in the required manner.

If this particular blog helps just one person, I know my life on this planet was not for naught. Ya learn something new everyday or as my sainted mother always says, “You grow ‘til you go.” By the way, this was in VFP9, so if you are using any other version, your mileage may vary. Incidentally, that is the last time in my life I will ever use that phrase; it has become too trite.

Southwest Fox 2007 Conference – Stream of Consciousness

Visual FoxPro is not dead; hell, it isn’t even breathing hard. For anyone who cares, I am suggesting that the NEXT version of VFP be codenamed, “Lazarus” because what I saw at the Southwest Fox 2007 Conference, makes me believe that VFP is rising from the dead (if, indeed, it ever was dead). How apropos that the conference was held so close to a city named Phoenix.

In the coming days, I will be blogging about my experience at the most recent developer’s conference for Microsoft’s Visual FoxPro and giving more detailed accounts of sessions I attended. Today, however, I am just going to give you some “three dot” journalism and some random thoughts and tid bits.

Bucking the current trend in FoxPro developer conferences, SWFox 2007 set a conference attendance record. With three walk-on registrants the day the conference began, the total number of attendees was 151 (plus speakers) which was MORE THAN DOUBLE (albeit, just by one) last year’s attendance.

I loved Whil Hentzen’s line about getting older and forgetting more often when it comes to syntax. He suggested that he is in the “Write once, forget often” mode.

Andy Kramek was giving a presentation about SQL when someone shouted out, “There’s a bug in your code.” Andy immediately asked, “Where?” To which the attendee responded, “Well, not IN your code, it’s ON your code.” Indeed there was, an actual, giant BUG (of some type) was crawling down the screen where Andy was projecting his code.

The weather was PERFECT! The conference staff did a great job as did the hotel/conference center personnel. There was very little to complain about. With the overflow crowd, it was, at times, a bit difficult to navigate through the crowds. Actually, it would have been a lot easier if there weren’t so many back packs. This made everyone wearing them twice as big. Trying to squeeze between to back-packed attendees would have been a lot easier if they were not wearing the backpacks. On the other hand, it might have been just as easy if I had made it a point to drop some gross (in every sense of the word) tonnage before the conference. I wouldn’t want people to complain about MY “bellypack”.

Speaking of complaints, I do have one. It is more of a pet peeve, but still... what is it about “PUT YOUR PHONES ON VIBRATE” that some attendees don’t get? Every speaker reminded attendees to make sure their phones/pagers/Buck Rogers Atomic ray guns were all set to vibrate (or stun). Yet, in the sessions that I attended there were at least a half a dozen cell phones that rang. I guess those people have the card in their wallet that say, “but me” as in “The rules apply to everyone...” Inconsiderate, very inconsiderate. OK, I am getting down from the soap box.

Another interesting sign was that (VERY subjectively) I felt that the percentage of female attendees was way up from what it usually is. I have no figures, but it would not surprise me if females accounted for 15+% of the attendees, which, in the world of geeks and “Pocket Protector Pow Wows” is a pretty high number. This is a good sign for the distaff side.

Yes, the conference was wonderful. Fun. Informative. Exhilarating. “Brain-baking” and definitely worth the price. I am wondering if it is too early to sign up for next year’s conference. Keep checking back as I will be elaborating, in detail, on some of the sessions that really opened up my eyes and, in some cases, have caused me to reconsider my opinions on several issues.