By Tom Cone
| Tom Cone uses Xbasic to fix a Y2K bug in one of Mr. Gates' products. Bill, you can reach Tom at tcone@ix.netcom.com |
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 Alpha Five (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 (Alpha Five tables). 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 Alpha Five tables had, in fact, become corrupted, possibly with a virus. Testing disproved the presence of any viruses, however. I then tested the integrity of the Alpha Five tables 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 Alpha Five tables 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.
Alpha Five tables, 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 Alpha Five tables 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 Alpha Five table 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 Alpha Five table 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 Alpha Five, dBase and FoxPro DBF 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 a friend of mine 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 Alpha Five, dBase and FoxPro DBF files. It's 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.
But what to do till a 'fix' is released from Microsoft? Fortunately, it was relatively easy for to write an Alpha Five script which opens DBF files and resets the second byte to 99 decimal, thereby making the files "visible" to Outlook again. The script is attached to the OnPush event for a 'Fix Year Last Modified' button. This button is on the 'FixThem' form in the 'FixYear' database which is available for download separately. With only a little effort this script could be added to other scripts you may use to copy records to temporary DBF tables for use with Outlook.
''XBasic
'by Tom Cone
'
'This script checks the year last modified byte in DBF files,
'to determine if they were last modified after 12/31/99. If so,
'this script will change the year last modified byte to 99.
'
'Purpose of script is to modify DBFs created after 12/31/99 so that
'they can be recognized and imported into MicroSoft Outlook.
'
'The year last modified byte is the second byte in the DBF file.
'
'Pseudo Code outline:
' Determines current directory when started
' Warns user to backup data first
' Prompts user for target folder
' Makes target folder the current working folder
' Counts number of DBF files in target folder
' Builds array containing names of all DBF files in target folder
' Checks each DBF file in target folder
' if last modified after 12/31/99, changes year last modified byte to 63h (99d)
' if last modified on or before 12/31/99 no changes occur
' Makes original folder the current working folder
old_directory = dir_get() 'will restore this on exit
code = ui_stop_symbol + ui_yes_no
response = ui_msg_box("Warning",\
"Make safety or backup copies of your data before proceeding."\
+chr(13)+chr(10)+chr(13)+chr(10)+"Are you ready to continue?",code)
if response
ui_yes_selected then 'user wants to quit
goto endit
end if
targetfolder = ui_get_text("Target Folder",\
"Enter complete path to desired folder")
if targetfolder = "" 'user furnishes no input
goto endit
end if
if asc(right(var->targetfolder,1)) = 92 then
targetfolder = left(targetfolder, len(targetfolder) - 1)
'strip backslash
end if
'now count DBF files in the target folder
on error goto invalidfolder
dir_put(targetfolder)
on error goto 0
filecount = 0
files = filefind.first("*.DBF") 'pointer to filelist
while .not. files.eof()
filecount = filecount + 1
files.next()
end while
if filecount = 0 then
ui_msg_box("Sorry", "No DBF files were found in the target folder")
goto endit
else
ui_msg_box("Files Found",str(var->filecount))
end if
dim file_names[filecount] as C 'create array to hold filenames
delete files 'clear pointer
files = filefind.first("*.DBF") 'reset pointer to filelist
counter = 0
while .not. files.eof()
counter = counter + 1
file_names(counter) = files.name()
files.next()
'statusbar.clear()
'statusbar.set_text(file_names(counter))
'sleep(1)
end while
for workingon = 1 to filecount
'check each file in array of filenames
currentfile = targetfolder+chr(92)+file_names(workingon)
statusbar.clear()
statusbar.set_text(currentfile)
sleep(.5)
on error goto fileinuse
'set error trap if file cannot be opened
filepointer = file.open(var->currentfile, file_rw_exclusive)
on error goto 0
filepointer.seek(1)
'move pointer to 1st byte
byteblob = filepointer.readb(1)
'read next byte
'... this is the year last modified byte
bytevalue = byteblob.peek(1)
'determine its value
if bytevalue > 99 then
byteblob.poke(1,99) 'change blob to 99
filepointer.seek(1) 'reposition pointer in disk file
byteswritten = filepointer.writeb(byteblob)
'write it to disk
'writes to next byte
end if
skipfile:
filepointer.close()
next
goto endit
invalidfolder:
ui_msg_box("Target Folder?",\
"The target folder could not be found."+chr(13)+chr(10)\
+" Check your spelling and try again.")
goto endit
fileinuse:
ui_msg_box("File In Use?",file_names(workingon) + \
" could not be checked."+chr(13)+chr(10) + \
"It may be in use by someone else.")
resume skipfile
endit:
statusbar.clear()
statusbar.set_text("All Done.")
dir_put(old_directory)
end
The script illustrates how to count the number of DBF files in a specified folder. It also shows you how to open a DBF file in binary mode, read information from it, and then store changes back to the same file. It was my first experience dealing with blob data, and once again, Alpha Five had the necessary tools ready and waiting for me.
Scripting got you down? You can download a database containing Tom's code.
1/29/00
Don't forget, we need your feedback to make this site better!