EXTRACT command
Extracts data from an Analytics table and outputs it to a new Analytics table, or appends it to an existing Analytics table. You can extract entire records or selected fields.
Syntax
EXTRACT {RECORD|FIELDS field_name <AS display_name> <...n>|FIELDS ALL <EXCLUDE field_name <...n>>} TO table_name <LOCAL> <IF test> <WHILE test> <FIRST range|NEXT range> <EOF> <APPEND> <OPEN>
Parameters
| Name | Description |
|---|---|
| RECORD | FIELDS field_name | FIELDS ALL |
The fields to include in the output:
|
| AS display_name optional |
Only valid when extracting using FIELDS field_name. The display name (alternate column title) for the field in the view in the new Analytics table. 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 AS. Specify display_name as a quoted string. Use a semi-colon (;) between words if you want a line break in the column title. Note
AS works only when extracting to a new table. If you are appending to an existing table, the alternate column titles in the existing table take precedence. |
| EXCLUDE field_name optional |
Only valid when extracting using FIELDS ALL. The field or fields to exclude from the command. EXCLUDE allows you to fine-tune FIELDS ALL, by excluding the specified fields. EXCLUDE must immediately follow FIELDS ALL. For example: FIELDS ALL EXCLUDE field_1 field_2 |
| TO table_name |
The location to send the results of the command to:
|
|
LOCAL optional |
Saves the output file in the same location as the Analytics project. Note Applicable only when running the command against a server table with an output file that is an Analytics table. The LOCAL parameter must immediately follow the TO parameter. |
|
IF test optional |
A conditional expression that must be true in order to process each record. The command is executed on only those records that satisfy the condition. Note The IF parameter is evaluated against only the records remaining in a table after any scope parameters have been applied (WHILE, FIRST, NEXT). |
|
WHILE test optional |
A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached. Note If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached. |
| FIRST range | NEXT range optional |
The number of records to process:
Use range to specify the number of records to process. If you omit FIRST and NEXT, all records are processed by default. |
|
EOF optional |
Execute the command one more time after the end of the file has been reached. This ensures that the final record in the table is processed when inside a GROUP command. Only use EOF if all fields are computed fields referring to earlier records. |
|
APPEND optional |
Appends the command output to the end of an existing file instead of overwriting it. Note You must ensure that the structure of the command output and the existing file are identical:
Analytics appends output to an existing file regardless of its structure. If the structure of the output and the existing file do not match, jumbled, missing, or inaccurate data can result. |
|
OPEN optional |
Opens the table created by the command after the command executes. Only valid if the command creates an output table. |
Examples
Extracting all records in a table to a new table
You create an exact duplicate of the AR_Customer table by extracting all the records to a new Analytics table. Any computed fields are preserved as computed fields:
OPEN AR_Customer EXTRACT RECORD TO "AR_Customer_2"
Extracting all fields in a table to a new table
You extract all defined fields in the AR_Customer table to a new Analytics table. Any computed fields are converted to physical fields and populated with the actual computed values:
OPEN AR_Customer EXTRACT FIELDS ALL TO "AR_Customer_2"
Extracting all records in a table and appending them to an existing table
You extract all the records in the AR_Customer table and append them as a group to the end of the AR_Customer_Master table:
OPEN AR_Customer EXTRACT RECORD TO "AR_Customer_Master" APPEND
Extracting all records in a table and appending them to an existing table in a different folder
You extract all the records in the AR_Customer table and append them as a group to the end of the AR_Customer_Master table, which is in a folder other than the Analytics project folder:
OPEN AR_Customer EXTRACT RECORD TO "C:\Users\Customer Data\AR_Customer_Master" APPEND
Extracting a subset of fields from a table to a new table
You have two options when extracting a subset of the fields in a table:
- specify the individual fields to extract
- specify FIELDS ALL and specify the fields to exclude from extraction
Tip
Use whichever method is the least labor-intensive.
The examples below refer to the AR_Customer table, which has seven fields:
- reference number
- customer number
- customer name
- transaction type
- invoice date
- due date
- invoice amount
Specify the fields to extract
You extract three fields from the AR_Customer table to a new Analytics table:
OPEN AR_Customer EXTRACT FIELDS Name Due Date TO "AR_Customer_Dates.fil"
Specify FIELDS ALL and specify the fields to exclude
You extract all fields, except the Reference_num field, from the AR_Customer table to a new Analytics table:
OPEN AR_Customer EXTRACT FIELDS ALL EXCLUDE Reference_num TO "AR_Customer_Dates.fil"
Creating display names for extracted fields
You extract three fields from the AR_Customer table and create display names for the fields in the new Analytics table:
OPEN AR_Customer EXTRACT FIELDS Name AS "Customer;Name" Due AS "Due;Date" Date AS "Invoice;Date" TO "AR_Customer_Dates.fil"
Extracting fields based on a condition
You extract three fields from the AR_Customer table to a new Analytics table if the date in the Due field is before July 1, 2014:
OPEN AR_Customer EXTRACT FIELDS Name Due Date IF Due < `20140701` TO "Overdue.fil"
Remarks
For more information about how this command works, see Extracting data or Extracting and appending data.
EXTRACT vs Copying a table
EXTRACT creates a new source data file (.fil) as well as a new table layout.
Copying a table using the Navigator () creates a new table layout that remains associated with the original source data file. It does not create a new data file.
