November 14, 2007
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.
Posted by Dave Aring on November 14, 2007 | Permalink
TrackBack URL for this entry:
Listed below are links to weblogs that reference A Bug’s Not a Bug until It Bites You:
I've found CAST to be good for that problem too.
Posted by: bill drew | Nov 15, 2007 6:23:55 AM
EXCELLENT (and elegant) solution. I am going to give it a try today. Using CAST() as a potential solution never even entered my mind. Of course, those of you who are following along at home should know that the Cast() function is new in VFP9 (yes, you are right, Bill, I DID mention that I was using VFP9). I guess this is another example of "several ways to skin a Fox".
Thanks for the suggestion.
Posted by: Dave Aring | Nov 15, 2007 8:10:12 AM
I can't say why now, but I never liked the IMPORT command. I have always used APPEND FROM ... TYPE XLS. That way you can create your table with the structure that you want.
Posted by: Larry C | Nov 15, 2007 11:37:32 PM
You make a good point. I guess it is a toss up, but I have (whoa! get this... ) made a comment (what a concept!) in my code to consider both yours and Bill's suggestions if and when I have to revisit the source code again.
Thanks for the different perspective.
Posted by: Dave Aring | Nov 16, 2007 11:29:28 AM