EXTRACT command

Used to extract data from an ACL table and output it to a new ACL table, or append it to an existing ACL table. You can extract entire records or selected fields.

Syntax

EXTRACT {RECORD | FIELDS field_name <...n> | ALL} <AS display_name> TO filename
<IF test> <WHILE test> <{FIRST|NEXT} range> <EOF> <APPEND> <OPEN> <LOCAL>

Parameters

RECORD | FIELDS field_name | ALL

Allows you to select the fields or expressions to extract. Specify RECORD or ALL to extract all fields in a table, or specify FIELDS followed by the field names to extract only specific fields.

If you specify RECORD or ALL, the fields are extracted in the order they appear in the table layout. If you specify FIELDS, the fields are extracted in the order you list them.

If you are extracting one or more computed fields, specifying RECORD preserves the extracted fields as computed expressions. Specifying ALL or FIELDS converts the extracted fields to physical fields of the appropriate data type in the destination table – ACL, ASCII (in the non-Unicode edition of ACL), Unicode (in the Unicode edition of ACL), Datetime, or Logical – and populates the fields with the actual computed values.

AS display_name

Optional. Only for use when extracting using the FIELDS parameter. Specifies the display name (alternate column title) for the field in the view in the new ACL table. You must specify the display_name value as a quoted string. Use a semi-colon (;) between words in display_name if you want a line break in the column title. This parameter works only for extracting to a new table. If you are appending to an existing table, the alternate column titles in the existing table take precedence.

If you want the display name to be the same as the field_name, or an existing display name in the source table, do not use the AS parameter.

TO filename

Specifies the name of the ACL data file (.fil) to create, which is also the name of the new ACL table, or specifies the table you are appending the extracted data to. The .fil file extension does not need to be specified and is not part of the ACL table name.

IF test

Optional. Specifies a condition that must be met. The command is executed on only those records that meet the condition.

WHILE test

Optional. Specifies that the command is executed only while a condition is true. The command processing terminates as soon as the specified condition evaluates as false, or the end of the table is reached.

FIRST | NEXT range

Optional. Specify FIRST to start processing at the first record or NEXT to start processing at the currently selected record. Use the range value to specify the number of records to process.

EOF

Optional. Specifies that the command is executed one more time after the end of the file has been reached. This parameter is sometimes necessary to ensure that the final record in the table is processed when you are processing the records with a GROUP command. It should only be used if all fields are computed fields referring to earlier records.

APPEND

Optional. Specifies that command output is appended to the end of an existing file instead of overwriting it. When using this parameter, you must ensure that the structure of the command output and the existing file is identical, if maintaining a consistent file structure is important (for example, when you are appending data to an existing ACL table), because ACL appends output to an existing file regardless of its structure.

OPEN

Optional. Specifies that the table created by the command is opened after the command executes. This parameter is only valid if the command creates an output table.

LOCAL

Specifies that the output file should be saved in the same location as the ACL project. This parameter only applies when you are extracting data from an ACL Server table.

Remarks

The difference between extracting all the records from a table to a new ACL table, and copying a table in the Navigator ( Edit > Copy), is that extracting creates a new source data file (.fil) as well as a new table layout, whereas copying creates only a new table layout that remains associated with the original source data file.

Examples

a. Extract all records in a table to a new table

The following example creates an exact duplicate of the AR_Customer table by extracting all the records to a new ACL table.

OPEN AR_Customer
EXTRACT RECORD TO "AR_Customer_2"

b. Extract a subset of fields from a table to a new table

The following example extracts three fields from the AR_Customer table to a new ACL table.

OPEN AR_Customer
EXTRACT FIELDS Name Due Date TO "AR_Customer_Dates.fil"

c. Create display names for extracted fields

The following example extracts three fields from the AR_Customer table and creates display names for the fields in the new ACL table.

OPEN AR_Customer
EXTRACT FIELDS Name AS "Customer;Name" Due AS "Due;Date" Date AS "Invoice;Date"
TO "AR_Customer_Dates.fil"

d. Extract fields based on a condition

The following example extracts three fields from the AR_Customer table to a new ACL table if the date in the Due column is before July 1, 2014.

OPEN AR_Customer
EXTRACT FIELDS Name Due Date IF Due < `20140701` TO "Overdue.fil"


(C) 2015 ACL Services Ltd. All Rights Reserved.