FoxPro Append Copmmand

Posted on 2012-08-28
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

    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 ''
    	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

    LVL 3

    Accepted Solution

    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

    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 29

    Expert Comment

    by:Olaf Doschke
    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    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://msd…
    DECT technology has become a popular standard for wireless voice communication. DECT devices are not likely to be affected by other electronic devices and signals because they operate in a separate frequency-band.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now