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} 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.

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 only executed on records that pass the test.

WHILE test

Optional. Specifies that the command should only be executed while a condition is true. The command processing terminates as soon as the specified test 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 should be 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 should be appended to the end of an existing file instead of overwriting the existing file. You must ensure that the structure of the two files is identical when using this parameter, if maintaining a consistent file structure is important (e.g., 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 should be 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.

Example

The following example extracts three fields from records in the AR_Customer table to a new ACL table if the date in the Due column is before June 30, 2011.

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


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback