COPY TO Command
Creates a new file from the contents of the currently selected table.
COPY TO FileName [DATABASE DatabaseName [NAME LongTableName]]
[FIELDS FieldList | FIELDS LIKE Skeleton | FIELDS EXCEPT Skeleton]
[Scope] [FOR lExpression1] [WHILE lExpression2]
[ [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 Delimiter ] ] ] [AS nCodePage]
Parameters
FileName
Specifies the name of the new file to create. If you do not include a file extension, COPY TO assigns the default extension for the specified file type. If you do not specify a file type, COPY TO creates a new Visual FoxPro table and assigns the default .dbf extension to the table file name.DATABASE DatabaseName
Specifies a database to which the new table is added.NAME LongTableName
Specifies a long name for the new table. Long names can contain up to 128 characters and can be used instead of short file names in the database.FIELDS FieldList
Specifies which fields are copied to the new file. If you omit FIELDS FieldList, all fields are copied to the file. If the file you are creating is not a table, memo fields are not copied to the new file, even if memo field names are included in the field list.FIELDS LIKE Skeleton
Specifies that fields from the original table that match the field skeleton Skeleton are included in the new file that COPY TO creates.FIELDS EXCEPT Skeleton
Specifies that all fields except those that match the field skeleton Skeleton are included in the new file that COPY TO creates.Scope
Specifies a range of records to copy to a file. Only the records within the range are copied. The scope clauses are: ALL, NEXTnRecords, RECORDnRecordNumber, and REST. For more information on scope clauses, see Scope Clauses.FOR lExpression1
Specifies that COPY TO copies only the records for which the logical condition lExpression1 evaluates to True (.T.) to the file. To conditionally copy records, include the FORlExpression1 clause to filter out undesired records.WHILE lExpression2
Specifies a condition where records are copied while the logical expression lExpression2 evaluates to True (.T.).[WITH] CDX | [WITH] PRODUCTION
Creates a structural index file for the new table that is identical to the structural index file of the existing table. The tags and index expressions from the original structural index file are copied to the new structural index file. The CDX and PRODUCTION clauses are identical. However, do not include CDX or PRODUCTION if you are copying to a file other than a new Visual FoxPro table.NOOPTIMIZE
Disables Rushmore Query Optimization for COPY TO. For more information, see SET OPTIMIZE Command and Using Rushmore Query Optimization to Speed Data Access.TYPE
Specifies the file type if the file you create is not a Visual FoxPro table. Although you must specify a file type, you do not need to include the TYPE keyword.FOXPLUS
Visual FoxPro memo files have a different structure than Microsoft FoxBASE+™ memo files. If your source Visual FoxPro table contains a memo field, include the FOXPLUS clause to create a table that can be used in FoxBASE+. The Visual FoxPro memo field cannot contain binary data because FoxBASE+ does not support binary data in memo fields.FOX2X
Creates a new table that can be opened in earlier versions of FoxPro (versions 2.0, 2.5, and 2.6).For Numeric, Float, Integer, Double, and Currency type fields, null values in the source table are converted to zero in the new table. For other field types, null values in the source table are converted to blanks in the new table. For further information about blank values, see ISBLANK( ) Function.
The following table lists the Visual FoxPro field types that are converted to different field types in the new table when the FOX2X argument is included.
Visual FoxPro field type
FoxPro 2.x field type
Blob
Memo
Currency
Float
DateTime
Date
Double
Float
Integer
Numeric
Varbinary
Memo
Varchar
Memo
DIF
Creates a VisiCalc® .dif (Data Interchange Format) file. Fields from the Visual FoxPro table become vectors (columns) and records become tuples (rows). The new file name is assigned a .dif extension if you do not include an extension in FileName.MOD
Creates a Microsoft Multiplan® version 4.01 file. The new Microsoft Multiplan file name is assigned a .mod extension if an extension is not included.SDF
Creates an SDF (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 SDF file name is assigned a .txt file extension if you do not include an extension. The SET CENTURY setting is ignored when creating SDF files with COPY TO.If SDF files include date data, it should be in YYYYMMDD format to allow effective reconversion into Visual FoxPro tables.
If date information is stored in ambiguous formats, you should make sure that the dates are in YYYYMMDD format before you perform the COPY TO operation.
SYLK
Creates a SYLK (Symbolic Link) interchange file. SYLK files are used in Microsoft MultiPlan. Each field from the currently selected table becomes a column in the spreadsheet, and each record becomes a row. SYLK file names have no extension.WK1
Creates a Lotus® 1-2-3® version 2.x spreadsheet file. Each field from the currently selected table becomes a column in the spreadsheet and each record becomes a row. A .wk1 file name extension is assigned to the new spreadsheet.WKS
Creates a Lotus 1-2-3 version 1a spreadsheet file. Each field from the currently selected table becomes a column in the spreadsheet and each record becomes a row. A .wks file name extension is assigned to the new spreadsheet.WR1
Creates a Lotus Symphony® version 1.1 or 1.2 spreadsheet file. Each field from the currently selected table becomes a column in the spreadsheet and each record becomes a row. A .wr1 file name extension is assigned to the new spreadsheet.WRK
Creates a Lotus Symphony version 1.0 spreadsheet file. Each field from the currently selected table becomes a column in the spreadsheet and each record becomes a row. A .wr1 file name extension is assigned to the new spreadsheet.CSV
Creates a comma separated value file. A CSV file has the field names as the first line in the file, and the field values in the remainder of the file are separated with commas.XLS
Creates a Microsoft Excel version 2.0 worksheet file. Each field from the currently selected table becomes a column in the spreadsheet, and each record becomes a row. If you do not include a file extension, an .xls extension is assigned to the new worksheet.Note
Though you can export a maximum of 65,535 rows, which includes one row reserved for the field header, versions of Excel earlier than 8.0 (Excel 97) display only the first 16,384 rows and cannot import files containing more than 32,767 rows.
XL5
Creates a Microsoft Excel version 5.0 workbook file. Each field from the currently selected table becomes a column in the spreadsheet, and each record becomes a row. If you do not include a file extension, an .xls extension is assigned to the new workbook.Note
Though you can export a maximum of 65,535 rows, which includes one row reserved for the field header, versions of Excel earlier than 8.0 (Excel 97) display only the first 16,384 rows and cannot import files containing more than 32,767 rows.
DELIMITED
Creates a delimited file. A delimited file is an ASCII text file in which each record ends with a carriage return and line feed. The default field separator is a comma. Because character data can include commas, character fields are additionally delimited with double quotation marks.Unless you specify otherwise, a .txt extension is assigned to all newly created DELIMITED files.
DELIMITED WITH Delimiter
Creates a delimited file with character fields delimited by a character other than a quotation mark. The character that delimits character fields is specified with Delimiter.DELIMITED WITH BLANK
Creates a delimited file with fields separated by spaces instead of commas.DELIMITED WITH TAB
Creates a delimited file with fields separated by tabs instead of commas.DELIMITED WITH CHARACTER Delimiter
Creates a delimited file with all fields enclosed by the character specified with Delimiter. If Delimiter is a semicolon (;), 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.You can combine the WITH Delimiter clause with the WITH CHARACTER clause. For example, the following command creates a text file with character fields enclosed by underscores and all fields delimited from each other with semicolons:
COPY TO mytxt.txt DELIMITED WITH _ WITH CHARACTER ';'AS nCodePage
Specifies the code page for the table or file that COPY TO creates. Visual FoxPro copies the contents of the currently selected table, and, as it copies the data, automatically converts the data to the code page you specify for the new table or file. If possible, Visual FoxPro marks the newly created table or file with the code page you specify.If you omit AS nCodePage, the newly created table or file is converted to the current Visual FoxPro code page.
Remarks
The field skeleton Skeleton supports wildcards. For example, to specify that all fields that begin with the letters A and P are included in the new file, use the following:
COPY TO mytable FIELDS LIKE A*,P*
The LIKE clause can be combined with the EXCEPT clause:
COPY TO mytable FIELDS LIKE A*,P* EXCEPT PARTNO*
Rushmore Query Optimization optimizes COPY TO with a FOR lExpression1 clause if lExpression1 is an optimizable expression. For the best performance, use an optimizable expression in the FOR lExpression1 clause. For information on Rushmore optimizable expressions, see SET OPTIMIZE Command and Using Rushmore Query Optimization to Speed Data Access.
If an index order is set, records are copied in master index order.
If you specify a value for nCodePage that is not supported, Visual FoxPro generates an error message. You can use the GETCP( ) function for nCodePage to display the Code Page dialog box, allowing you to specify a code page for the table or file Visual FoxPro creates.
The COPY TO command preserves autoincrementing settings except under the following conditions:
When the field list specified using COPY TO...FIELDS does not include the field that uses autoincrementing.
When using the TYPE clause in the COPY TO command, regardless of the type specified by the clause.
The target table begins autoincrementing starting with the NextValue of the source table. For example, suppose the NextValue in the source table is 1000 with a Step value of 1. The first row of the target table then has an autoincrementing field value of 1001; the second row has a value of 1002, and so on.
When using the COPY TO command, be aware that the SET VARCHARMAPPING Command will impact calculated fields (character expressions of variable length) set by the SET FIELDS Command.
Example
In the following example, the Customer table is opened and the next three records are copied to a new DELIMITED data file called Temp.txt.
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\testdata')
USE Customer && Opens Customer table.
COPY NEXT 3 TO Temp TYPE DELIMITED
WAIT WINDOW 'This is the delimited text file' NOWAIT
MODIFY FILE Temp.txt
DELETE FILE Temp.txt