APPEND FROM (SDF) and date data

[Kruno]

Hi friends,

Crime scene: Datapump, FPD tables to VFP tables.

COPY TO ... TYPE SDF (in FPD) made TXT-file with date type portion like 20080226. So far so good - format mentioned in VFP HELP of APPEND FROM.

In VFP then I APPEND FROM ... TYPE SDF, but datetime field in my VFP-table remains without any value. Help quote:

"SDF
...
Effective conversion of date data from SDF files to Visual FoxPro tables requires data to be stored in YYYYMMDD format."

I tried with various SET DATE - nothing. I tried DELIMITED with no result either. My VFP table field must be datetime not date.
Also was trying to get some other date format with COPY TO (FPD) in my TXT file.

Any suggestion for doing this?

Kruno Malovic

 

[TamarGranor]

You don't need to go through an intermediary to move data from FP/2.x files to VFP files. First, VFP will work just fine with FP/2.x files, so unless you need to have two copies, don't even both to copy.

Second, if you just want to dump existing data into new tables, use COPY TO without the TYPE keyword.

USE MyFox26Table
COPY TO MyVFPTable

That's it.

Tamar

 

[Mike Lewis]

Kruno,

You said " ... SDF files to Visual FoxPro tables requires data to be stored in YYYYMMDD".

I've had no problem in converting dates stored, for example, in DD/MM/YYYY format, provided the format matches the current SET DATE, SET CENTURY, etc.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Kruno]

Thanks Tamar and Mike

You wander why I am overdoing it. A question is usualy small part of bigger problem. In my case datapump. And here is a story about datapump to learn from:

If the data came from FPD I suddenly need ALLTRIM in WHERE portion of all my SQL statements for one table and field.
If the data came from VFP (user's input) no need for ALLTRIMs, everything works as before.
Yes, I can put those ALLTRIMS but this is a "dirty" solution. What if this occurs in some other table or field?
I will never know when such thing will pop up in the next datapump from some other source.

I needed a real soulution. Thats why I tried to get pure ASCI text to investigate my data, and overdo this COPY TO / APPEND FROM.

Relevant FPD/VFP table fields are the same, except VFP table has an autoincrement field added and datetime instead of date. After your advice I stopped with TXT, use pure COPY TO and turn to VFP part.

VFP table looks fine. Cant find any problem with that field's content (hard to say for sure in thousands of records). That must be some invalid record in the data! And I suspected in some odd COM behaviour too, I deal with along this...

Trying to locate a record which produces failing WHERE I deleted some records from various sides of table. I got it to work sometimes ok, but very stohastic. Cant conclude anything. Then I recalled what I say to my users when something strange and stohasctic start to happen with the data: index.

After APPEND FROM... I put REINDEX and all problems are gone!

The point of my story: Such bulk operation like APPEND FROM does not update indices correctly.

We learn every day. Thanks for your support.

Kruno Malovic