"APPEND FROM (file) TYPE SDF" and date fields

[teresas]

I have a flat field containing several dates with two-digit years that I would like to import into VFP. I am using the command

APPEND FROM (file) TYPE SDF

but I am having trouble getting the correct century on the date fields automatically.

For instance, the string "04/25/95" which becomes "04/25/0095." Or the string "02/03/01" becomes "02/03/0001".

I have done

SET CENTURY ON

but that doesn't matter.

Is my only option to use code to adjust the dates after import? If so, is there a better command than

GOMONTH()

for adding years to a date?

Thanks for any insight,

Teresa

P.S. I tried to use keyword search but it is down right now. And I didn't see anything on the FAQ page.

 

[ramani]

HI

First import date as a string only and then convert it to Date format using the following code

SET CENTURY ON
myDate = CTOD(RIGHT(myStringDate,6)+"20"+ ;
LEFT(myStringDate,2))

Hope this helps to convert your imported data.
ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[spayne]

I would do something like this:

SET CENTURY OFF
SELECT MyTable
APPEND FROM MyFile TYPE SDF
SET CENTURY ON
GO TOP
FOR lnCnt = 1 TO RECCOUNT()
lcDate = (SUBSTR(MyString, ##, ##))
lcNewDate = DTOC(CTOD(lcDate))
REPLACE MyString WITH STUFF(at_data, ##, ##, lcNewDate)
SKIP
ENDFOR

Good Luck!
Steve

 

[teresas]

ramani,

Thank you for your reply. You suggested "import date as a string only and then convert it to Date format."

I am confused how this would work. When using the

APPEND FROM

command, I have the table structure already created, including date fields. (There are actually several date fields per record.) I am familiar with the

CTOD

function for converting a text variable to a date, but I'm not sure how that applies to a field in a table.

Teresa

 

[teresas]

Spayne (Steve),

Thanks for your reply. In your sample code, I don't follow your use of MyString and at_data . (Do these apply to records in MyTable , or what?)

Teresa

 

[ramani]

Hi Teresa,
If you have come upto ... the full table imported and date field appearing as "04/25/0095"... then ...
you can run the command after the import in your way..

** open file if not already done..
SELECT 0
USE myFile

** convert date field...
SCAN
myStringDate = DTOC(myDateField)
myDate = CTOD(RIGHT(myStringDate,6)+"20"+ ;
LEFT(myStringDate,2))
REPLACE myDateField WITH myDate
** you can convert all date fields this way in this loop
ENDSCAN

Hope this helps you
ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[spayne]

Sorry Teresa,

Replace 'at_data' with 'MyString'. MyString would refer to the character field in MyTable that contains the date information.

I like ramani's code, but what if there was an old date from 1998 or 1999, etc.

 

[DEDMOD]

what if there was an old date from 1998 or 1999, etc.

Yes, you need to set a cut-off year and convert all years above the cutoff year to the previous century. And what year you use may depend on what the particular column of dates contains. If it's birthdates, then you want something low like 10 while if it's something like retirement dates it might need to be rather higher.

Dave Dardinger

 

[CDavis]

You might also consider creating a temporary dbf into which you import the data and do your conversion on it and then import the data into your final table so that you know the data is clean. The temporary dbf could use character fields which would transfer your data without the chance of VFP misinterpreting it. Then do your conversions using character manipulation and then finally move it into your final destination with CTOD().

CDavis

 

[teresas]

Thanks, everyone for your ideas. I have come up with a solution (and a variation) which combines several of your ideas and adds some of my own! Also, I discovered that I did not have to use

GOMONTH()

or

CTOD()

or even

SUBSTR()

!

(In case it wasn't clear in my first post, the strings for the date fields in the flat file contain slashes and two-digit years, e.g., "07/25/01".)

The first solution is to append the data into a cursor that has character fields in place of the date fields, copy to a delimited text file, and then append into the final table (which has date fields). The variation is to append into a table (with character fields) and then use the

ALTER TABLE

command to change the character fields to true date fields. Suprisingly, the years for the dates (which range from 1995 to 2001) turn out okay when all is said and done.

Here is an excerpt of the code for the solution and variation:

CREATE CURSOR MYCURSOR (DISTNBR C(4), BRTH_DATE C(8), ZIPCODE C(5), DTP1 C(8), DTP1STAT C(2), DTP2 C(8), DTP2STAT C(2), DTP3 C(8))
CREATE TABLE MYTABLE (DISTNBR C(4), BRTH_DATE D, ZIPCODE C(5), DTP1 D, DTP1STAT C(2), DTP2 D, DTP2STAT C(2), DTP3 D)
SELECT MYCURSOR
APPEND FROM IMPRTDAT.TXT TYPE SDF
COPY TO DELIMDAT.TXT DELIMITED WITH TAB
SELECT MYTABLE
APPEND FROM DELIMDAT.TXT TYPE DELIMITED WITH TAB
DELETE FILE DELIMDAT.TXT
SELECT MYCURSOR
USE


CREATE TABLE MYTABLE (DISTNBR C(4), BRTH_DATE C(8), ZIPCODE C(5), DTP1 C(8), DTP1STAT C(2), DTP2 C(8), DTP2STAT C(2), DTP3 C(8))
SELECT MYTABLE
APPEND FROM IMPRTDAT.TXT TYPE SDF
SET CENTURY OFF
ALTER TABLE MYTABLE ALTER COLUMN BRTH_DATE D
ALTER TABLE MYTABLE ALTER COLUMN DTP1 D
ALTER TABLE MYTABLE ALTER COLUMN DTP2 D
ALTER TABLE MYTABLE ALTER COLUMN DTP3 D
SET CENTURY ON

Teresa

 

[DEDMOD]

Suprisingly, the years for the dates (which range from 1995 to 2001) turn out okay when all is said and done.

I was going to mention it earlier but I wasn't certain and didn't have FoxPro running. I believe there's a build-in year break on some functions which has a default value which I don't remember; maybe it's 50. So in that case any year after 50 would be be converted to 19xx and any before 50 to 20xx. And there's some way you can change this value in your system. Probably where you save paths to your files and so forth.

But as I indicated earlier, this may not be what you need for a particular specialized use. So it may be more practical long-term to develop a program with a parameter to figure the century break.

Dave Dardinger