Share via

APPEND FROM Command

  • Article

Adds records to the end of the currently selected table from another file.

APPEND FROM FileName | ?   [FIELDS FieldList] [FOR lExpression]
   [[TYPE] [DELIMITED [WITH Delimiter | WITH BLANK | WITH TAB
      | WITH CHARACTER Delimiter] | DIF | FW2 | MOD | PDOX | RPD |
      SDF | SYLK | WK1 | WK3 | WKS | WR1 | WRK | CSV | XLS | XL5 
      [SHEET cSheetName] | XL8 [SHEET cSheetName]]] [AS nCodePage]

Parameters

  • FileName
    Specifies the name of the file to append from. If you don't include a file name extension, a Visual FoxPro table and a default extension .dbf is assumed. If you are appending from a Visual FoxPro table, records in the table that are marked for deletion are appended if the current SET DELETED setting is OFF.

  • ?
    Displays the Open dialog box, from which you can choose a table to append from.

  • FIELDS FieldList
    Specifies to which fields data is appended.

  • FOR lExpression
    Appends a new record for each record in the currently selected table for which lExpression evaluates to True (.T.). Records are appended until the end of the currently selected table is reached. If you omit FOR, the entire source file is appended to the currently selected table.

  • TYPE
    Specifies the source file type of the file you are appending from. Although you must specify a file type if the file you are appending from isn't a Visual FoxPro table, you need not include the TYPE key word. You can append from a wide variety of different file types including delimited ASCII text files, in which you can specify a field delimiter.

    If the source file you are appending from doesn't have the usual default file extension for that type of file, the source file name must include the file's extension. For example, Microsoft Excel worksheets normally have an .xls extension. If the Microsoft Excel worksheet you append from has an extension other than the expected .xls, be sure to specify the extension.

    Note   When you are appending from a worksheet, the data in the worksheet must be stored in a row-major order rather than a column-major order. This allows the appended worksheet data to match the table structure.

  • DELIMITED
    Specifies that the source file from which data is appended to the current Visual FoxPro table is a delimited file. A delimited file is an ASCII text file in which each record ends with a carriage return and line feed. Field contents are by default assumed to be separated from each other by commas (do not include extra spaces before or after the commas), and character field values to be additionally delimited by double quotation marks. For example:

    "Smith",9999999,"TELEPHONE"

    The file extension is assumed to be .txt for all delimited files.

    You can import dates from delimited files if the dates are in proper date format. The date format defaults to mm/dd/yy. Including the century portion of a date is optional. Visual FoxPro will import a date, such as 12/25/95, that doesn't include the century and assumes the date is in the twentieth century. Date delimiters can be any non-numeric character except the delimiter that separates the fields in the delimited file.

    Dates in other formats can be imported if their formats match date formats available in SET DATE. To import dates that are not in the default format, issue SET DATE with the proper date format before using APPEND FROM. To test whether a date format can be successfully imported, use it with CTOD( ). If the date is acceptable to CTOD( ), the date will import properly.

  • DELIMITED WITH Delimiter
    Indicates that character fields are separated by a character other than the quotation mark.

  • DELIMITED WITH BLANK
    Specifies files that contain fields separated by spaces instead of commas.

  • DELIMITED WITH TAB
    Specifies files that contain fields separated by tabs rather than commas.

  • DELIMITED WITH CHARACTER Delimiter
    Specifies files that contain fields all enclosed by the character specified with Delimiter. If Delimiter is a semicolon (the character used in Visual FoxPro to indicate command line continuation), enclose the semicolon in quotation marks. You can also specify the BLANK and TAB keywords for Delimiter.

    The WITH Delimiter clause can be combined with the WITH CHARACTER clause. For example, the following command adds records from a text file with character fields enclosed by underscores and all fields delimited from each other with asterisks:

    APPEND FROM mytxt.txt DELIMITED WITH _ WITH CHARACTER *

  • DIF
    Include DIF to import data from a VisiCalc .dif (Data Interchange Format) file. Vectors (columns) become fields in the currently selected table and tuples (rows) become records. DIF file names are assumed to have a .dif extension.

  • FW2
    Include FW2 to import data from a file created by Framework II. FW2 file names are assumed to have a .fw2 extension.

  • MOD
    Include MOD to import data from a Microsoft Multiplan version 4.01 file. MOD files are created by Microsoft Multiplan version 4.01, and are assumed to have a .mod extension.

  • PDOX
    Include PDOX to import data from a Paradox version 3.5 or 4.0 database file. Paradox file names are assumed to have a .db extension.

  • RPD
    Include RPD to import data from a file created by RapidFile version 1.2. RapidFile file names are assumed to have an .rpd extension.

  • SDF
    Include SDF to import data from a System Data Format file. An SDF file is an ASCII text file in which records have a fixed length and end with a carriage return and line feed. Fields are not delimited. The file name extension is assumed to be .txt for SDF files.

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

    If date information is stored in ambiguous formats, you should map the date column to a character column of appropriate width so you can inspect the value then apply the correct conversion routine to create correctly formatted date data.

  • SYLK
    Include SYLK to import data from a SYLK (Symbolic Link) interchange format file. SYLK files are used in Microsoft MultiPlan. Columns in the SYLK file become fields in the Visual FoxPro table and rows become records. SYLK file names have no extension.

  • WK1
    Include WK1 to import data from a Lotus 1-2-3 version 2.x spreadsheet. Each column from the spreadsheet becomes a field in the table; each spreadsheet row becomes a record in the table. A .WK1 file name extension is assigned to a spreadsheet created in Lotus 1-2-3 revision 2.x.

  • WK3
    Include WK3 to import data from a Lotus 1-2-3 spreadsheet. Each column from the spreadsheet becomes a field in the table; each spreadsheet row becomes a record in the table. A .wk3 file name extension is assigned to a spreadsheet created in Lotus 1-2-3 revision 3.x.

  • WKS
    Include WKS to import data from a Lotus 1-2-3 revision 1-A spreadsheet. Each column from the spreadsheet becomes a field in the table; each spreadsheet row becomes a record in the table. A .wks file name extension is assigned to a spreadsheet created in Lotus 1-2-3 revision 1-A.

  • WR1
    Include WR1 to import data from a Lotus Symphony version 1.1 or 1.2 spreadsheet. Each column from the spreadsheet becomes a field in the table and each spreadsheet row becomes a record in the table. A .wr1 file name extension is assigned to a spreadsheet created in Symphony versions 1.1 or 1.2.

  • WRK
    Include WRK to import data from a Lotus Symphony version 1.0 spreadsheet. Each column from the spreadsheet becomes a field in the table and each spreadsheet row becomes a record in the table. A .wrk file name extension is assigned to a spreadsheet created in Symphony version 1.0.

  • CSV
    Include CSV to import data from a comma separated value file. A CSV file has field names as the first line in the file; the field names are ignored when the file is imported.

  • XLS
    Include XLS to import data from a Microsoft Excel worksheet. Each column from the worksheet becomes a field in the table and each worksheet row becomes a record in the table. Worksheet files created in Microsoft Excel are given an .xls file name extension.

  • XL5
    Include XL5 to import data from Microsoft Excel version 5.0. Columns from the worksheet become fields in the table; the worksheet rows become records in the table. Worksheet files created in Microsoft Excel have an .xls extension.

    If you omit the SHEET clause, the data in Sheet1 is imported. To import data from a specific sheet, include the SHEET keyword and specify the sheet name with cSheetName.

  • XL8
    Include XL8 to import data from Microsoft Excel 97. Columns from the worksheet become fields in the table; the worksheet rows become records in the table. Worksheet files created in Microsoft Excel have an .xls extension.

    If you omit the SHEET clause, the data in Sheet1 is imported. To import data from a specific sheet, include the SHEET keyword and specify the sheet name with cSheetName.

  • AS nCodePage
    Specifies the code page of the source table or file. Visual FoxPro copies the contents of the source table or file and, as it copies the data, automatically converts the data to the code page of the current table.

    If you specify a value for nCodePage that is not supported, Visual FoxPro generates an error message. You can use GETCP( ) for nCodePage to display the Code Page dialog box, making it possible for you to specify a code page for the appended table or file.

    If you omit AS nCodePage and Visual FoxPro cannot determine the code page of the source table or file, Visual FoxPro copies the contents of the source table or file. As it copies the data, it automatically converts the data to the current Visual FoxPro code page. If SET CPDIALOG is ON, the table in the currently selected work area is marked with a code page. If you're appending from a table not marked with a code page, the Code Page dialog is displayed, making it possible for you to choose the code page of the table from which you're appending. The current Visual FoxPro code page can be determined with CPCURRENT( ).

    If you omit AS nCodePage and Visual FoxPro can determine the code page of the table or file being appended, Visual FoxPro copies the contents of the appended table or file. As it copies the data, it automatically converts the data to the code page of the currently selected table.

    If nCodePage is 0, Visual FoxPro assumes that the code page of the table or file being appended is the same as the code page of the currently selected table. No conversion to the current Visual FoxPro code page occurs.

Remarks

If the file from which you append is a Visual FoxPro table or a table created in an earlier version of FoxPro, a .dbf extension is assumed. If the Visual FoxPro table or the table created in an earlier version of FoxPro doesn't have a .dbf extension, you must specify its extension. If the file is not a Visual FoxPro table or a table created in an earlier version of FoxPro, you must specify the type of file from which you append.

Before you can append from a table created in dBASE IV or dBASE V that contains a memo field, you must first open the table in Visual FoxPro with USE. When you are prompted to convert the file, choose Yes.

If you append from a Visual FoxPro table or a table created in an earlier version of FoxPro, the table you append from can be open in another work area. Records marked for deletion in the table you are appending from are unmarked once the records are appended.

Use the DBF( ) function to append from a temporary read-only cursor created by a SELECT - SQL command. Include the name of the cursor in the DBF( ) function as in the following example:

APPEND FROM DBF('<Cursor Name>')

If the target table uses autoincrementing, APPEND FROM fails if AUTOINCERROR is set to ON, unless the FIELDS option is used to omit the AUTOINC column. Setting AUTOINCERROR to OFF or turning off autoincrementing in the target table by using CURSORSETPROP( ) allows the APPEND FROM to succeed. The target table's autoincrementing field or fields are incremented according to the values specified, and the values in source table are not applied.

Example

In the following example, the customer table is opened, its structure is copied to a table called backup, and backup is then opened. Visual FoxPro then appends all records from Finland from the customer table. These records are then copied to a new delimited file called TEMP.TXT.

CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\testdata')
USE customer  && Open customer table
COPY STRUCTURE TO backup
USE backup
APPEND FROM customer FOR country = 'Finland'
COPY TO temp TYPE DELIMITED
MODIFY FILE temp.txt
USE
DELETE FILE backup.dbf
DELETE FILE temp.txt

See Also

COPY FILE | COPY TO | EXPORT | GETCP( ) | IMPORT | SET DELETED