FoxPro Append Copmmand

Posted on 2012-08-28
Medium Priority
Last Modified: 2012-09-11
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.
Question by:spacoit
LVL 27

Expert Comment

ID: 38341148
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 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)

Open in new window


Accepted Solution

jharkins earned 2000 total points
ID: 38341167
I would create an intermediate file that contains all the fields in the new format supplied by the vendor. Give all of the fields that you want to keep the same name as they have in the final file you want to create. You can name the fields you don't want to keep anything, such as junk1, junk2, etc.

Then you just need to append to the intermediate file first, and then to the final from the intermediate file such as:

Select intermediate_file
APPEND FROM c:\jmspay.txt deli with char
Select final file
APPEND FROM intermediate_file

This will append all fields into the intermediate file, and only the fields you want end up in the final file.

Have fun!

LVL 12

Expert Comment

ID: 38341837
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.
   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
LVL 30

Expert Comment

by:Olaf Doschke
ID: 38342074
to illustrate the idea once more

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

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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
If anyone asked you to network diagram of the internet, it was drawn in the form of a fluffy cloud which further became known as cloud computing. Popularly cloud computing is defined as workloads that run over the internet in a commercial provider’s…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question