asked on

FoxPro Append Copmmand

I have an old FoxPro program that imports a comma-delimited file into a FoxPro table. All was well until the vendor added additional fields to the text file. So what I am trying to figure out if out to use the append command to only bring in the fields I need from the text file. From the "new" text file I only need fields 0,2,3,4,5,8,9,10,11,12,13,14,15.

The original command is
append from C:\jmspay.txt deli with char ,

Is this possible?

Sorry I cannot include the text file but it includes juror information.

Cyril Joudieh

Yes you need to get the values and stream them to another file or select the fields in your import program.

if you call the function below it will give you the field desired.

? extract(cLine, ',', 2)
? extract(cLine, ',', 3)
? extract(cLine, ',', 4)

You are probably used to other languages where they start with 0. Just add 1 and specialy that the delimiter is a comma. This script should work on old FoxPro.

FUNCTION extract
LPARAMETERS cString, cDelimiter, nIndex
IF nIndex = 0
	RETURN ''
ELSE
	RETURN SUBSTR(cDelimiter+cString+cDelimiter,AT(cDelimiter,cDelimiter+cString+cDelimiter,nIndex)+1,AT(cDelimiter,cDelimiter+cString+cDelimiter,nIndex+1)-AT(cDelimiter,cDelimiter+cString+cDelimiter,nIndex)-1)
ENDIF

Open in new window

ASKER CERTIFIED SOLUTION

jharkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jrbbldr

I think that I'd agree with Jack (above) as to the most easy approach to getting rid on the 'extra' fields and ending up with only those fields that you want.

intermediate_file has 20 fields (for example)
final_file has 15 fields

NOTE - this simplistic approach would only work as shown above if the fields you wanted were the first 15 fields - followed by the un-wanted fields

If not, then you might want the secondary Append to be changed to a SQL Query acquiring only those specific fields from intermediate_file that you need.
Example:
SELECT Desired1 AS Wanted1,;
Desired2 AS Wanted2,;
Desired3 AS Wanted3,;
Desired4 AS Wanted4,;
Desired5 AS Wanted5,;
<and so on...>;
FROM intermediate_file;
INTO TABLE final_file

Good Luck

Olaf Doschke

to illustrate the idea once more

assume old text format was
1,text
new text format has a new uinwanted value as 2nd value
1,unwanted,text

Now the import side:

You had and still have an import.dbf with (in this sample case) an ID int and a text char(10) field.

Now add a second dbf with added field: ID int, junk1 char(10), text char(10)
You append the txt into that new dbf, which puts the unwanted value in the junk field. In the second step you append this new.dbf to the normal import dbf and the junk fields are simply skipped. Append from DBF() only appends the fields the destination DBF has and skips others:

create cursor curImport (id i, text c(10))
create cursor curImport2 (id i, junk1 c(10), text c(10))
insert into curImport2 values (1,"unwanted","text") && simulating the append from txt file
select curImport
append from dbf("curImport2")
browse && see, only wanted data was appended into curImport. "unwanted" is skipped, as curImport has no junk1 field.

So the only thing you need is an additional dbf and then do a two staged append to stripe off unwanted fields.

Bye, Olaf.