Logo

Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.

APPEND FROM, COPY TO, Import, Export

These commands let you move data in and out of DBF format. APPEND FROM and COPY TO also let you move data between tables.

Usage

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

APPEND FROM and IMPORT bring in data in a foreign format and put it in a FoxPro table. The difference is that APPEND FROM adds data to an existing table, while IMPORT creates a new table, figuring out what fields it should have. (In some cases, the new table’s field names are distinctly stupid.) In addition, APPEND FROM lets you specify the mapping of data to fields and lets you omit some of the incoming data.

APPEND FROM adds the data to the table open in the current work area. IMPORT creates and opens a table, giving it the same name as the source file, but with a DBF extension.

APPEND FROM deals with more file formats than IMPORT. In addition to all the foreign formats, you can use it to move data between two FoxPro tables or from text files into a table. (If you omit the TYPE clause entirely, data is appended from one FoxPro table to another.)

Don't count on a COPY TO and APPEND FROM pair returning the original file in all configurations. SYLK is limited to 9,999 records while APPENDing. COPYing to most of the spreadsheet formats, like DIF and MOD, introduces a header that may create a blank first record while importing. VFP is limited by the number of rows the spreadsheet supports: 8,192 rows for WK1, WKS, WR1 and WRK formats, and 16,383 for Excel. (Although APPEND FROM can import the 65,536 possible rows of Excel 2000 and later, COPY TO can only output 16,383 rows.) Note that for several of the formats, such as WK3, you can use APPEND FROM, but not COPY TO.

We figure if you have data with one of the listed extensions, you know where it came from and what it looks like, so we won’t get into details like “PDOX is Paradox data.” If you really want to know, Help has a complete list.

Instead, let’s look at the ones that don’t correspond to foreign file formats: DELIMITED and SDF. SDF is simpler—it stands for “system data format” and refers to a file where each field takes up a fixed amount of space corresponding to its length. SDF files are also sometimes called “column-separated” files, and are a common way to import mainframe data. Using SDF, any application capable of columnar output files can serve as input to VFP. Even if APPEND FROM didn’t support SDF files, these are pretty easy to read in and parse.

APPEND FROM SDF has a problem with Date fields: It assumes that dates in the data being appended have two-digit years, so anything after the second digit is assumed to be the start of the next field. DateTime fields are treated correctly.

DELIMITED is for files that contain data with delimiters and separators. There’s a lot of confusion about those two, particularly since the command itself mixes them up. Delimiters are characters that surround the data in a field. For example, the word “snazzlefritz” is delimited by quote marks in this sentence. Separators come between two data items. For example, the following list of colors uses commas as separators: “chartreuse, lavender, fuchsia, taupe, teal.” A delimited file normally contains both delimiters and separators. Here’s an example (generated from the Labels.DBF that comes with VFP):

"DATAW","LABELLYT","Avery 4143",F,4869,/  /
"DATAW","LABELLYT","Avery 4144",F,39266,/  /
"DATAW","LABELLYT","Avery 4145",F,24620,/  /
"DATAW","LABELLYT","Avery 4146",F,32961,/  /

Each character field is surrounded by quotes (the delimiters), and fields are separated by commas. This is the default format for a delimited file.

TYPE DELIMITED can handle several other options. DELIMITED WITH BLANK and DELIMITED WITH TAB use quotes for delimiters, and fields are separated by spaces or tabs. DELIMITED WITH DELIMITER lets you specify the delimiter—fields are separated by commas.

VFP 5 introduced the DELIMITED WITH CHARACTER clause that lets you specify the separator. So DELIMITED WITH CHARACTER ! means that there’s an exclamation point between each pair of fields. We can’t see why they couldn’t have improved the situation here by giving this option a useful name like SEPARATED BY. Nonetheless, we’re very grateful to have this option because it increases the number of files we can handle without having to break out the Low-level File Functions.

You can combine DELIMITED WITH and DELIMITED WITH CHARACTER to specify both the delimiter and the separator.

TYPE CSV (“comma-separated values”) is like DELIMITED, except VFP assumes the first line in the file contains field names, so it skips that line.

On the whole, the choices are quite a mess. If you’re still confused by this, we suggest you do what we did to figure all this out. Try COPY TO with each option on a small data sample.

The FOR clause of APPEND FROM looks at each record as if it had already been added to the table in question and evaluates the condition in that situation. In older versions of FoxPro, this was a big issue because the deleted flag of a record didn’t come along on an APPEND, so it could be very difficult to copy all the deleted or all the undeleted records. Starting in VFP 3, the deleted flag comes along, so this issue just doesn’t come up very often. The time you’re most likely to run into this is if the table you’re copying from has a logical field indicating that it should be copied, but the field doesn’t exist in the destination. Say there’s an lArchive field in the source indicating that the record is ready to be archived. When you issue a command like:

USE Archive
APPEND FROM Source FOR Source.lArchive

the lArchive field is evaluated only for the current record of the source, not for each one in turn. So either all records or no records are copied. Setting a filter on the source table doesn’t help; VFP ignores it. The workaround is to use a SQL SELECT statement to select the matching records into a temporary cursor, or COPY the records to a temporary file, and then APPEND FROM there.

The AS clause of both commands lets you specify the code page of the original data so it can be translated on the way in.

Unlike APPEND FROM, IMPORT doesn't set _TALLY to the number of records imported (in our testing, it set _TALLY to 1). Use RECCOUNT() instead.

If you specify a database to which the new table created by IMPORT is added, and that database has database events turned on in VFP 7, you'd expect the BeforeAddTable and AfterAddTable (or perhaps BeforeCreateTable and AfterCreateTable) events to fire. Unfortunately, no events fire.

Example

USE MyTable
APPEND FROM MyOtherTable FOR nAmount>500

IMPORT FROM MailList.XLS TYPE XLS

Usage

COPY TO FileName
        [ DATABASE DBCName [ NAME LongTableName ] ]
        [ FIELDS FieldList
         | FIELDS LIKE Skeleton
         | FIELDS EXCEPT Skeleton ]
        [ Scope ] [ FOR lForCondition ]
        [ WHILE lWhileCondition ]
        [ [ WITH ] CDX | [ WITH ] PRODUCTION ]
        [ NOOPTIMIZE ]
        [ [ TYPE ] FOXPLUS | FOX2X | DIF | MOD | SDF | SYLK
         | WK1 | WKS | WR1 | WRK | CSV | XLS | XL5
         | DELIMITED [ WITH Delimiter | WITH BLANK
         | WITH TAB ] [ WITH CHARACTER Separator ] ]
        [ AS nCodePage ]
EXPORT TO FileName [ FIELDS FieldList ]
        [ Scope ] [ FOR lForCondition ]
        [ WHILE lWhileCondition ]
        [ NOOPTIMIZE ]
        [ TYPE ] DIF | MOD | SYLK | WK1 | WKS | WR1 | WRK
         | XLS | XL5
        [ AS nCodePage ]

Both COPY TO and EXPORT copy selected data to another file. We can’t see much reason to use EXPORT, since COPY TO can do everything it does and more. (In fact, we’re fairly convinced EXPORT exists only because IMPORT does.) Both create a brand-new file containing the specified data.

Because the original source for COPY TO and EXPORT is a table, you have more options as to which records are involved. The usual Scope, FOR and WHILE clauses let you pick and choose among the records. Unlike APPEND FROM, in COPY TO the clauses are evaluated before the data is copied. And, just in case you like to slow things down, there’s NOOPTIMIZE.

See the discussion above for an explanation of SDF and DELIMITED files. COPY TO also includes the FOXPLUS and FOX2X options that let you create tables that are compatible with older Fox products. A FOXPLUS table is really a dBase III Plus table and is compatible with just about everything around.

The behavior of COPY TO ... DELIMITED differs between versions of VFP:

  • DELIMITED WITH BLANK: In FoxPro 2.x, character fields aren't surrounded by quotes. Leading spaces in character fields are removed in VFP 3. All other versions work correctly.
  • DELIMITED WITH "" or DELIMITED WITH []: These commands give an error in versions prior to VFP 7. In VFP 7, they create a text file with no quotes around any field.

Earlier versions of Excel permitted a maximum of 16,384 rows. Although more recent versions of Excel extend that limit to 65,536 rows, neither COPY TO nor EXPORT will create an Excel file with more than 16,384 rows. This is because COPY TO only supports XL5 copying, while APPEND FROM and IMPORT support XL8. This is sort of a "by-design" gotcha, but we sure wish they'd change it.

COPY TO ... TYPE XLS is as much as 10-15 times faster than COPY TO ... TYPE XL5. In addition, XL5 files are significantly larger than their XLS counterparts. However, date fields aren't treated properly in XLS files. You'll see them displayed in Excel as "DD-MMM-YY", and changing the format to "MM/DD/YYYY" doesn't help. You actually have to go into each cell, edit (without making any changes), and then click in another cell to see the date formatted properly. Dates work just fine in XL5 files.

When you’re copying to another Visual FoxPro table, you can make various choices. You can create either a FREE TABLE or a table that’s contained in a database. If you choose to put it into a database, you can specify a long name for it. You can also decide whether to copy the structural CDX file—that’s what WITH is all about.

The AS clause lets you create the new file with a different code page than the original.

Unlike the bug mentioned earlier with IMPORT, the BeforeAddTable and AfterAddTable events do fire if you specify a database in the COPY TO command and if that database has Database Events turned on in VFP 7 and later.

Example

USE Labels
* Check out some of the options.
COPY TO LabelPlus TYPE FOXPLUS
COPY TO Label2x TYPE FOX2X
COPY TO Label.Txt FIELDS Type, Id, Name, CkVal ;
   TYPE DELIMITED
COPY TO Label.Txt FIELDS Type, Id, Name, CkVal;
   TYPE DELIMITED WITH BLANK
COPY TO Label.Txt FIELDS Type, Id, Name, CkVal;
   TYPE DELIMITED WITH TAB
COPY TO Label.Txt FIELDS Type, Id, Name, CkVal;
   TYPE DELIMITED WITH ~
COPY TO Label.Txt FIELDS Type, Id, Name, CkVal;
   TYPE DELIMITED WITH CHARACTER #
COPY TO Label.Txt FIELDS Type, Id, Name, CkVal;
   TYPE DELIMITED WITH ~ WITH CHARACTER #

Take a look at the file created for each of the last six examples to understand the different DELIMITED options.

See Also

Append From Array, AfterAddTable, AfterCreateTable, BeforeAddTable, BeforeCreateTable, Copy File, Set Optimize, _Tally