PRB: Problems Importing Dates from Lotus Spreadsheet to FoxPro

MS-DOS                     | WINDOWS

The information in this article applies to:

• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a, 2.5b, 2.6
• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6

SYMPTOMS

Under some circumstances, when the IMPORT command is used to load dates from a Lotus spreadsheet into a FoxPro database, FoxPro loads the Lotus date number and does not properly translate the value to a date. The date may be imported as a character value or as a numeric value.

RESOLUTION

To correct this problem, do the following:

1. Use the MODIFY STRUCTURE command to create an additional date field.

2. If the date number was imported as a numeric value, execute the following command to copy the date to the new field:

         REPLACE ALL <new_field> WITH
            CTOD(SYS(10,<old_field>+2415019))
   
      If the date number was imported as a character value, execute the
      following command to copy the date to the new field:
   
         REPLACE ALL <new_fld> WITH
            CTOD(SYS(10,VAL(<old_fld>)+2415019))
   
   

3. Use the MODIFY STRUCTURE command to delete the original (incorrect) date field.

MORE INFORMATION

Lotus stores dates as the (nonnegative) number of days from January 1, 1900. The number 2415019 is the corresponding Julian date. By adding this offset to the Lotus date value, FoxPro can correctly translate the date.