COPY

COPY ... commands

Purpose

Copy all or part of the active table to another table or file

Syntax

COPY TO <filename> | (<expC1>)

[<scope>]

[DECRYPT | ENCRYPT <expC2>]

[FIELDS <field list>]

[FOR <condition>]

[WHILE <condition>]

[[WITH] CDX | [WITH] PRODUCTION]

Description

The COPY command copies records from the active table to another table or file. The file name can be substituted with a <expC1> enclosed in round brackets that returns a valid filename. If there is a table dictionary or a memo file on the current table, these files will also be copied to the new Lianja table. If the current table is indexed, records will be copied in the indexed order, but the index file itself is not copied to the new table. Format files associated with the current table will not be copied to the new table. If SET FILTER TO is in effect, then only records that satisfy the filter condition are copied. If SET DELETED is ON, then records marked for deletion will not be copied.

To <filename>

The TO file will be created or, if it already exists, will be overwritten. If no file extension is specified for the TO <filename>, tables will default to '.dbf', XML files will default to '.xml', and all other files will default to '.txt'. The <filename> can include an encryption key for encrypted database tables. The three part comma-separated key should be enclosed in angled brackets and appended to the filename, e.g. mytable<key_1,key_2,key_3>. This allows the creation of an encrypted copy of a non-encrypted table or an encrypted copy with a different key to the encrypted source table.

<scope>

If no <scope> is specified, then the default is ALL.

DECRYPT | ENCRYPT <expC2>

The DECRYPT and ENCRYPT clauses can be used to specify whether the target table of a COPY TO operation is encrypted or not. Specifying DECRYPT allows the creation of a non-encrypted copy of an encrypted table.

The ENCRYPT <expC2> clause encrypts the target table using the three part key specified in <expC2>. The <expC2> must contain a three part comma-separated key, each part a maximum of 8 characters, e.g. "key_1,key_2,key_3". Angled brackets may optionally enclose the key, e.g. "<key_1,key_2,key_3>". A .dkf file is created with the same basename as the target table. This allows the creation of an encrypted copy of a non-encrypted table or an encrypted copy with a different key to the encrypted source table. By default, when copying an encrypted table, if the key is not included in the <filename> and neither clause is specified, the target table is encrypted and has the same encryption key as the source table. A .dkf file is created with the same basename as the target table. If the source table is not encrypted and neither clause is specified, the target table will not be encrypted.

FIELDS <field list>

If the FIELDS clause is specified, then only those fields specified will be copied, otherwise all fields will be copied. The <field list> is a comma-separated list of field names. The fields specified can contain alias pointers, allowing copy to retrieve fields from multiple data files to be copied to a single data file. The number of records copied when using alias pointers is determined by the number of records in the table in the workarea from which the copy was initiated.

FOR <condition>

If the FOR clause is specified, then only those records which satisfy the specified <condition> are copied.

WHILE <condition>

If the WHILE option is used, the <scope> defaults to REST. The WHILE clause will copy records so long as the <condition> is true (.T.), and is used to restrict the range of records processed. When used in conjunction with the SEEK or LOCATE commands, it allows a quick way of copying selected records.

WITH CDX | WITH PRODUCTION

The [[WITH] CDX | [WITH] PRODUCTION]] clause causes the currently active multiple index file to be copied along with the table to a .cdx/.dbx file with the same basename as the TO <filename> when the target is a database table.

TYPE FIXED

If the target file type specified is FIXED, then the file will be created containing fixed length records without any record terminating character. This file type is useful for exporting records into a file that can be read by PASCAL, C, FORTRAN, etc.

TYPE SDF

If the target file type specified is SDF then the file will be created containing records as lines of text terminated with a carriage return/linefeed sequence. If any of the fields being copied are binary fields, then the records are created in FIXED format. The maximum length of the text line used with COPY...SDF is 8192 characters.

TYPE XML

The XML clause copies the records to an Extensible Markup Language (XML) file. It also creates a matching Document Type Definition file with a '.dtd' file extension if the XML format is set to RECITAL. The default XML file format is Microsoft® ActiveX® Data Objects (ADO). This default can be set with the command SET XMLFORMAT TO <RECITAL | ADO | EXCEL>.

TYPE CSV

If the CSV clause is specified, then the target file will be created as a text file. Each field will be separated by a comma (,). Any occurrences of " embedded in character or memo fields will be escaped as "". If the optional DELIMITED WITH <delimiter> is specified, then fields will be separated by the <delimiter> instead of a comma. If SET CSVHEADING is ON (default), the first line of the text file will be the field names. If SET CSVHEADING is OFF, the field names will be omitted.

TYPE DELIMITED

If the DELIMITED clause is specified, then the target file will be created as a text file. Each field will be separated by a comma (,), and character fields will be enclosed in double quotes. If DELIMITED WITH BLANK is specified, then fields will be separated by a single space character instead of a comma. Character fields will not be enclosed. If DELIMITED WITH <delimiter> is specified, then the first character in <delimiter> is used to separate the fields instead of a comma. If <delimiter> contains 2 (or more) characters, then the second character replaces the double quotes normally used to enclose character fields. If DELIMITED WITH TAB is specified, then fields will be separated by a tab character instead of a comma. Character fields will not be enclosed. Files created with the COPY TO...DELIMITED command can be appended into other Lianja tables using the APPEND FROM...DELIMITED command.

Example

use patrons index names
copy to ballet for event = "BALLET"
seek "OPERA"
copy to opera rest;
  while event = "OPERA";
  for date = date()
 
// Another example
use payroll
// Copy to a file with today's name
copy to (cdow(date())) for amount > 100
// Make an encrypted copy
copy to encver<key_1,key_2,key_3>