By Tom Cone, Tampa PC Users Group
Some of you may use Microsoft's Outlook application to manage your calendar, contact lists, e-mail mailboxes, and so on. I've bumped into a Y2K problem with Outlook's import and translation utility which you should know about. Even if you don't use Outlook, you might find this interesting, because it illustrates a type of problem you might encounter in other contexts.
I learned during the first week of January that Outlook 97 was unable to recognize dBase tables so that data within those tables could no longer be imported into contact lists inside Outlook. When the import process was started, Outlook returned an error message saying that the source file was either corrupt, or possibly that it wasn't a dBase file in the first place. dBase3 and dBase3+ tables have been around a long time, I daresay since before 1985, possibly longer. These are database files in which each individual record has the same overall length. This particular kind of fixed length format is well understood and is often included in the import routines of other commercial programs. For example, both Microsoft Word and Corel WordPerfect can easily read and import dBase files. Spreadsheet programs like Excel and Quattro Pro can, too. Prior to 1/1/2000, so could Outlook. But not afterwards.
I was asked to troubleshoot the problem to see if the problem could be identified. My first guess was that the dBase files had, in fact, become corrupted, possibly with a virus. Testing disproved the presence of any viruses, however. I then tested the integrity of the dBase files by importing them into a variety of other programs. All worked as expected, except for Outlook. I then speculated that perhaps Outlook 2000, the latest release from MicroSoft, might not have the problem, so I tried it. Oops. Same problem. Both Outlook 97 and Outlook 2000 are unable to import dBase files created after 12/31/1999.
Interestingly, Outlook contains an "export" engine which permits one to export data from contact lists and store them in external files. One of the file types supported by the export routine was dBase, so I exported some into files of this format to see if they could then be immediately imported back into Outlook. Oops. The export routine works, but the import engine continued to complain that it could not recognize the source file as a dBase file, stopping the import process dead in its tracks.
dBase files and early versions of FoxPro tables employ the same file format, so I tried to import some FoxPro DBF files. Oops, again. Outlook cannot import FoxPro files created after 12/31/1999 either.
At this point I was stumped. Testing revealed that if I dialed back the system date on my PC to a date earlier than 1/1/2000 I could create dBase or FoxPro files that Outlook would recognize and import. This confirmed for me at last that I was dealing with a Y2K problem.
I posted some inquiries in relevant newsgroups on the internet and received a helpful response reminding me that, in addition to the data, each dBase file contains a variable length header record which precedes the data itself. When the file is created on disk, the header is written first, and then the records containing actual data follow. The respondent mentioned that one of the elements stored in the header of each dBase file is the date the file was last modified.
Doing a bit of research, I discovered that the second, third and fourth bytes of each dBase file contain numbers representing the year, month and day, respectively, on which the file was last modified. Using a disk editor utility, I opened a few dBase files and examined the values stored in the second, third and fourth bytes at the top of each file. I found that there were numbers present in each position and that these numbers are stored as hexadecimal values. For you non-programmer types, this means that each numeric value was stored as a number using base 16 notation. I compared the "year last modified" byte in files created in 1999 against files created in 2000. It turns out that 1999 is represented as hexadecimal 63 or 63h. This is the same as a decimal 99 or 99d. The "year last modified" byte is stored as 64h if the file was created in the year 2000. The value 64h is the same as 100 decimal.
Using the disk editor I was able to modify the second byte of some dBase files, changing the stored value from 64h to 63h (i.e. from 100 to 99 expressed in decimal notation). When I did this, the files would then be recognized by Outlook as valid dBase files and could be imported without incident, even when the system file date for the dBase file was itself 1/1/2000 or later. This confirmed for me that Outlook was not using the system file date of the DBF file to reject the files. Instead, it suggests strongly that Outlook's import utility was designed to check for "valid" values in the "year last modified" byte in the header of dBase and FoxPro files, using routines which stumble when the value there equates to decimal values greater than 99. The internal logic declares the source file invalid and does not recognize it as a valid dBase or FoxPro file unless the value in the second byte of the DBF file equates to 99 or less.
I mentioned this to one of our members, who asked a pertinent question. Whose fault is this glitch? One might argue that the original dBase file format is flawed by using only one hexadecimal byte value to represent the "year last modified". With only one byte to use, values are limited to a range from zero to 255. The designers of dBase (and FoxPro) must have decided to simply subtract 1900 from the current year and store the resulting difference value in this field. For files last modified in 1985, an 85 would be stored there (55h). For files last modified in 2001, a 101 would be stored there (65h). This design covers all the way to the year 2155 without requiring an additional byte to distinguish the century. It's true that if the designers had dedicated two bytes of storage to express the year last modified, they would have been able to save the decimal equivalent of both the century and the year. However, it's hard to argue that their design is flawed when it reliably covers all years from 1900 to 2155. Remember, this is not data. These values are simply a record of the year the file itself was last modified. Covering file modifications over such a long period of time seems a generous and reasonable approach to me.
So I lay the blame at Microsoft's doorstep. The Outlook import engine is flawed and fails to adequately deal with dBase and FoxPro format files. Its internal "technique" of recognizing dBase and FoxPro files mishandles the second byte of the source file, improperly rejecting it when values there equate to decimal 100 or higher. Whether this is a problem in Outlook, per se, or possibly in the ODBC interface between Outlook and the source tables, I don't know, but it's clear to me that the problem exists and is found within the import routines employed by Outlook.
Fortunately, it was relatively easy for me to write a BASIC program which opens DBF files and resets the second byte to 99 decimal, thereby making the file "visible" to Outlook again. This work around will be awkward but it’s fast and reliable. Unfortunately, it's going to be necessary until Microsoft releases a patch which corrects Outlook. u