EXPORT command
Exports data from Analytics to the specified file format, or to HighBond Results.
Syntax
EXPORT {<FIELDS> field_name <AS export_name> <...n>|<FIELDS> ALL <EXCLUDE field_name <...n>>} <UNICODE> export_type <SCHEMA> PASSWORD num TO {filename|aclgrc_id} <OVERWRITE> <IF test> <WHILE test> <{FIRST range|NEXT range}> <APPEND> <KEEPTITLE> <SEPARATOR character> <QUALIFIER character> <WORKSHEET worksheet_name> <DISPLAYNAME>
Parameters
Name | Description | |||||||
---|---|---|---|---|---|---|---|---|
FIELDS field_name AS export_name <...n> | FIELDS ALL |
The fields to export.
|
|||||||
EXCLUDE field_name optional |
Only valid when exporting 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 |
|||||||
UNICODE optional |
Available in the Unicode edition of Analytics only. Applies to text (ASCII), delimited text (DELIMITED), and XML files only, and to Windows Clipboard (CLIPBOARD) output. Exports Analytics data with Unicode UTF-16 LE character encoding applied.
For more information, see Diligent Unicode products. |
|||||||
export_type |
The output file format or destination using one of the following options:
|
|||||||
SCHEMA optional |
Applies to XML file output only. Include the XML schema in the exported XML file. The XML schema contains metadata that describes the structure of the XML file, including the data type of the fields. You can validate the file against the schema once the file has been exported. |
|||||||
PASSWORD num |
Applies to HighBond Results ( ACLGRC ) only. The password definition to use. You do not use PASSWORD num to prompt for, or specify, an actual password. The password definition refers to a password previously supplied or set using the PASSWORD command, the SET PASSWORD command, or the PASSWORD analytic tag. num is the number of the password definition. For example, if two passwords have been previously supplied or set in a script, or when scheduling an analytic, PASSWORD 2 specifies that password #2 is used. For more information about supplying or setting passwords, see: PASSWORD num must be placed immediately before TO, or at the end of the string of command syntax. The required password value is a HighBond access token. For more information, see Exporting to HighBond Results. Note PASSWORD may or may not be required, depending on the environment in which the script runs:
|
|||||||
TO filename | aclgrc_id |
The destination for the export:
|
|||||||
OVERWRITE optional |
Applies to HighBond Results ( ACLGRC ) only.
Any interpretations related to the target control test (table) dynamically update to reflect the imported data, whether you overwrite or append. |
|||||||
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. |
|||||||
APPEND optional |
Applies to text (ASCII) and delimited text (DELIMITED) files only. 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. |
|||||||
KEEPTITLE optional |
Applies only to text files (ASCII), and delimited text and comma-separated values files (DELIMITED). Include the Analytics field names with the exported data. If omitted, no field names appear in the output file. |
|||||||
SEPARATOR character optional |
Applies only to delimited text and comma-separated values files (DELIMITED). The character to use as the separator between fields. You must specify the character as a quoted string. By default, Analytics uses a comma. Do not specify any character other than a comma if you are exporting to a comma-separated values file. |
|||||||
QUALIFIER character optional |
Applies only to delimited text and comma-separated values files (DELIMITED). The character to use as the text qualifier to wrap and identify field values. You must specify the character as a quoted string. By default, Analytics uses double quotation marks. |
|||||||
WORKSHEET worksheet_name optional |
Applies to Microsoft Excel (.xlsx) files only. The name of the Excel worksheet created in a new or existing Excel file. By default, Analytics uses the name of the Analytics table you are exporting as the worksheet name. The worksheet_name can contain only alphanumeric characters or the underscore character ( _ ). The name cannot contain special characters, spaces, or start with a number. Enclosing the value in quotation marks is optional. For details about overwriting Excel workbooks and worksheets when exporting, see The WORKSHEET parameter and overwriting. |
|||||||
DISPLAYNAME optional |
Applies to HighBond Results ( ACLGRC ) only. Exports field names as field names and display names as display names so the display names appear in column headings in Results without affecting the actual field name. It is possible to combine DISPLAYNAME with AS. For more information see How DISPLAYNAME interacts with AS when exporting to HighBond Results. |
Examples
Export data to an Excel .xlsx file
You export specific fields from the Vendor table to an Excel .xlsx file:
OPEN Vendor
EXPORT FIELDS Vendor_No Vendor_Name Vendor_City XLSX TO "VendorExport"
Export data to an Excel .xlsx file and specify a worksheet name
You export specific fields from the Vendor table to a worksheet called Vendors_US in an Excel .xlsx file:
OPEN Vendor
EXPORT FIELDS Vendor_No Vendor_Name Vendor_City XLSX TO "VendorExport" WORKSHEET Vendors_US
Export all fields to a delimited file
You export all fields from the Vendor table to a delimited file:
OPEN Vendor
EXPORT FIELDS ALL DELIMITED TO "VendorExport"
Export a subset of fields to a delimited file
You have two options when exporting a subset of the fields in a table to an external file:
- specify the individual fields to export
- specify FIELDS ALL and specify the fields to exclude from the export
Tip
Use whichever method is the least labor-intensive.
The examples below refer to the Vendor table, which has eight fields:
- vendor number
- vendor name
- vendor street
- vendor city
- vendor state
- vendor ZIP
- last active date
- review date
Specify the fields to export
You export two fields from the Vendor table to a delimited file:
OPEN Vendor
EXPORT FIELDS Vendor_No Vendor_Name DELIMITED TO "Vendors" KEEPTITLE SEPARATOR "|" QUALIFIER '"'
Specify FIELDS ALL and specify the fields to exclude
You export all fields, except the last active date and review date fields, from the Vendor table to a delimited file:
OPEN Vendor
EXPORT FIELDS ALL EXCLUDE Vendor_Last_Active Vendor_Review_Date DELIMITED TO "Vendor_addresses" KEEPTITLE SEPARATOR "|" QUALIFIER '"'
Export all fields to a comma-separated values file
You export all fields from the Vendor table to a comma-separated values file:
OPEN Vendor
EXPORT FIELDS ALL DELIMITED TO "VendorExport.csv"
Export data to multiple delimited files using GROUP
You export specific fields from the Vendor table to two delimited files:
- one file for vendor names from "A" to "M"
- one file for vendor names from "N" to "Z"
Using the GROUP command, you test the vendor name of each record with an IF condition:
GROUP
EXPORT FIELDS Vendor_No Vendor_Name DELIMITED TO "AtoM" IF BETWEEN(UPPER(VENDOR_NAME), "A", "M")
EXPORT FIELDS Vendor_No Vendor_Name DELIMITED TO "NtoZ" IF BETWEEN(UPPER(VENDOR_NAME), "N", "Z")
END
Export data to HighBond Results
You export specific fields from the AR_Exceptions table to HighBond Results. You overwrite existing data in the target control test (table):
OPEN AR_Exceptions
EXPORT FIELDS No Due Date Ref Amount Type ACLGRC PASSWORD 1 TO "10926@us" OVERWRITE
Remarks
For more information about how this command works, see Exporting data.
Using EXPORT with the GROUP command
For most export formats, you can export data into multiple files simultaneously using the GROUP command.
Only one file can be created at a time when you are exporting data to Microsoft Excel and Microsoft Access.
Exporting to Excel
The following limits apply when exporting data to an Excel file:
Number of records |
Analytics tables that exceed these maximums export successfully, but the excess records are ignored and not exported. |
---|---|
Length of fields |
|
Length of field names |
|
The WORKSHEET parameter and overwriting
The result of using or not using the WORKSHEET parameter when exporting from an Analytics table to an Excel file is explained below:
Matching |
Description |
WORKSHEET parameter used | WORKSHEET parameter not used |
---|---|---|---|
No matching Excel file name |
|
A new Excel file is created, with a worksheet with the specified name | A new Excel file is created, with a worksheet that uses the name of the exported Analytics table |
Matching Excel file name No matching worksheet name |
|
A worksheet with the specified name is added to the existing Excel file | The existing Excel file is overwritten by a new Excel file, with a worksheet that uses the name of the exported Analytics table |
Matching Excel file name and worksheet name |
|
A worksheet with the specified name overwrites the existing worksheet if it was originally created from Analytics. An error message appears and the export operation is canceled if the existing worksheet was originally created directly in Excel. |
The existing Excel file is overwritten by a new Excel file, with a worksheet that uses the name of the exported Analytics table |
Exporting to HighBond Results
The table below contains additional information about exporting to a control test in Results.
Item |
Details |
---|---|
Required permissions |
The ability to export results to a control test in Results requires a specific HighBond role assignment, or administrative privileges:
|
Export limits |
The following limits apply when exporting to a control test:
You can export multiple times to the same control test, but you cannot exceed the overall limits. |
Appending fields (OVERWRITE not specified) |
Regardless of their order in an Analytics table, exported fields are appended to existing fields in a control test if they have matching physical field names. In Analytics, the physical field name is the name in the table layout. Exported fields that do not match the name of any existing field are added as additional columns to the table in Results. Display names of fields in Analytics, and in Results, are not considered. However, if you use the optional AS export_name parameter, the export_name value is used as the physical field name if you do not use DISPLAYNAME . When appending data to questionnaire fields, the display name of the column in Results remains the name that is specified in the questionnaire configuration. Appending works differently if the target control test has a primary key field specified. For more information, see Exporting exceptions to HighBond Results. Note If you are round-tripping data between Results and Analytics, and data ends up misaligned in Results, you probably have mismatched field names. For more information, see Field name considerations when importing and exporting Results data. |
Creating a password definition and specifying a password value |
PASSWORD command If you use the PASSWORD command to create the numbered password definition for connecting to HighBond, no password value is specified, so a password prompt is displayed when the script attempts to connect. For more information, see PASSWORD command. SET PASSWORD command If you use the SET PASSWORD command to create the numbered password definition for connecting to HighBond, a password value is specified, so no password prompt is displayed, which is appropriate for scripts designed to run unattended. For more information, see SET PASSWORD command. Acquire a HighBond access token Regardless of which method you use to create the password definition, the required password value is a HighBond access token, which users can generate in Launchpad. Caution The generated access token matches the account used to sign in to Launchpad. As a scriptwriter, specifying your own access token in a script may not be appropriate if the script will be used by other people.
|
How DISPLAYNAME interacts with AS when exporting to HighBond Results
The matrix below shows how the DISPLAYNAME parameter interacts with AS when exporting field names from Analytics to Results.
Without AS | With AS | |
---|---|---|
Without DISPLAYNAME | Field name and display name in Results are the field name from Analytics. | Field name and display name in Results are the display name in the AS parameter. |
With DISPLAYNAME | Field name in Results is the field name from Analytics. Display name in Results is the display name from Analytics. | Field name in Results is the field name from Analytics. Display name in Results is the display name in the AS parameter. |