PRB: Problems Importing Dates from Lotus Spreadsheet to FoxPro

• 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, The number 2415019 is the corresponding Julian date. By adding this offset to the Lotus date value, FoxPro can correctly translate the date.

Additional query words: 123 Symphony WR1

Keywords          : FoxDos FoxWin FxinteropSpread KBQ 
Version           : MS-DOS:2.0,2.5,2.5a,2.5b,2.6; WINDOWS:2.5,2.5a,2.5b,2.6
Platform          : MS-DOS WINDOWS 
Issue type        : kbprb 

Last Reviewed: April 26, 1999