EXPORT command

Exports data from Analytics to the specified file format, or to the Results app or the Robots app in HighBond.

Note

You must have 32-bit Microsoft Access Database Engine installed for the EXPORT command to work with older Excel files (*.xls) and Microsoft Access files (*.mdb). For more information, see Exclude optional Microsoft Access Database Engine.

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

  • FIELDS field_name export the specified field or fields

    Separate field names with spaces. Fields are exported in the order that you list them.

    You can optionally include a different name for the field in the export file using AS export_name. Enclose export_name in quotation marks.

    If you are exporting to HighBond Results ( ACLGRC ), it is possible to combine AS with the DISPLAYNAME parameter. For more information, see How DISPLAYNAME interacts with AS when exporting to HighBond Results.

  • FIELDS ALL export all fields in the table

    Fields are exported in the order that they appear in the table layout.

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 files, delimited text files, XML files, and Windows Clipboard output only. ( ASCII , DELIMITED , XML , CLIPBOARD )

Exports Analytics data with Unicode UTF-16 LE character encoding applied.

  • Specify UNICODE if the data you are exporting contains characters that are not supported by extended ASCII (ANSI)
  • Do not specify UNICODE if all the characters in the data you are exporting are supported by extended ASCII (ANSI)

    The exported data is encoded as extended ASCII (ANSI).

    Note

    Any unsupported characters are omitted from the exported file.

For more information, see Diligent Unicode products.

export_type

The output file format or the destination.

Specify one of the following options:

  • ACCESS – Microsoft Access database file (*.mdb)

    By default, the data is exported as Unicode.

  • ACLGRCHighBond Results
  • ASCII – ASCII plain text (*.txt)
  • CLIPBOARD – Windows Clipboard
  • DBASE – dBASE compatible file (*.dbf)
  • DELIMITED – delimited text file (*.del), or comma-separated values file (*.csv)
  • EXCEL – Microsoft Excel file (*.xls) compatible with Excel 1997 to 2003
  • HBDATA – compressed comma-separated values file (*.csv.gz) to a HighBond robot or a Workflow robot in the HighBond Robots app
  • JSON – JSON file (*.json)
  • LOTUS – Lotus 123 file
  • WDPF6 – Wordperfect 6 file
  • WORD – MS Word file (*.doc)
  • WP – Wordperfect file
  • XLS21 – Microsoft Excel version 2.1 file (*.xls)
  • XLSX – Microsoft Excel file (*.xlsx)

    By default, the data is exported as Unicode.

  • XML – XML file (*.xml)

SCHEMA

optional

Applies to XML file output only. ( XML )

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

optional

Applies to HighBond apps only. ( ACLGRC , HBDATA )

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 script, 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 Creating a password definition and specifying a password value.

PASSWORD num may or may not be required, depending on the environment in which the script runs.

Environment where script runs PASSWORD num requirement
Analytics

(online activation)

PASSWORD num is not required.

The current user's HighBond access token, stored in their Windows registry, is automatically used.

Analytics

(offline activation)

PASSWORD num is required.

Robots
TO filename

For exports to a file, the name of the file.

If required, you can include either an absolute or relative file path, but the Windows folder must already exist. You must specify the filename value as a quoted string.

Note

To export to a comma-separated values file (*.csv), you must specify the .csv file extension as part of filename. For example: vendors.csv

TO aclgrc_id

For exports to the HighBond Results app, the destination in Results. ( ACLGRC )

The aclgrc_id value must include the Results control test ID number, and if you are exporting to a data center other than North America (US), the data center code. The aclgrc_id value must be enclosed in quotation marks.

The control test ID number and the data center code must be separated by the at sign (@). For example, TO "99@eu".

If you do not know the control test ID number, use the Analytics user interface to begin an export to Results. Cancel the export once you have identified the control test ID number. For more information, see Exporting exceptions to the Results app in HighBond.

The data center code specifies which regional HighBond server you are exporting the data to:

  • af – Africa (South Africa)
  • ap – Asia Pacific (Singapore)
  • au – Asia Pacific (Australia)
  • ca – North America (Canada)
  • eu – Europe (Germany)
  • jp – Asia Pacific (Tokyo)
  • sa – South America (Brazil)
  • us – North America (US)

You can use only the data center code or codes authorized for your organization's instance of HighBond. The North America (US) data center is the default, so specifying @us is optional.

TO highbond_api_url

For exports to the HighBond Robots app, the destination in Robots. ( HBDATA )

Note

You cannot export to an ACL robot. You must export to a HighBond robot or a Workflow robot.

The highbond_api_url value is the HighBond API request URL for the destination robot. The example below exports a *.csv.gz file to this destination:

  • the robot with ID number 52053

  • in the HighBond org with ID number 1000236

  • in the HighBond North America (US) data center

TO "https://apis-us.highbond.com/v1/orgs/1000236/robots/52053/working_files?env=development"

Use the env query string parameter to specify whether the file is exported to development mode or production mode in the robot:

Development mode

(default)

  • ?env=development

or

  • no query string specified

Production mode
  • ?env=production

OVERWRITE

optional

Applies to HighBond apps only. ( ACLGRC , HBDATA )

  • OVERWRITE specified Exported data overwrites any existing data in the target control test (table), or the robot. You must have a Professional Manager role in the target Collection to overwrite data.

  • OVERWRITE omitted Exported data is appended to any existing data in the target control test (table). For more information, see Exporting to HighBond Results.

    Appending is not supported for *.csv.gz files in Robots.

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:

  • FIRST start processing from the first record until the specified number of records is reached
  • NEXT start processing from the currently selected record until the specified number of records is reached

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 files and delimited text files only. ( ASCII , DELIMITED )

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:

  • the same fields
  • the same field order
  • matching fields are the same length
  • matching fields are the same data type

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 to text files, delimited text files, and comma-separated values files only. ( ASCII , DELIMITED )

Include the Analytics field names with the exported data. If omitted, no field names appear in the output file.

SEPARATOR character

optional

Applies to delimited text files and comma-separated values files only. ( 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 to delimited text files and comma-separated values files only. ( 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. ( XLSX )

The name of the Excel worksheet created in a new or existing Excel file. Specify a maximum of 31 characters.

By default, Analytics uses the name of the Analytics table you are exporting as the worksheet name. Names longer than 31 characters are truncated.

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 only. ( ACLGRC )

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

Excel examples

Delimited file examples

Comma-separated values (CSV) file example

Results app example

Robots app examples

Excel 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

Delimited file examples

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

Comma-separated values (CSV) file example

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"

Results app example

Export data to the Results app in HighBond

Specify the fields to export

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 TO "10926@us" OVERWRITE

Create a password definition required to run the command in Robots

You create a two-part password definition with a PASSWORD analytic tag, and a matching PASSWORD parameter in the EXPORT command.

The //PASSWORD 1 analytic tag creates a password input parameter in a robot task. The matching PASSWORD 1 parameter in the EXPORT command references the stored and encrypted password value in the task. The required password is a valid HighBond access token. For more information, see Creating a password definition and specifying a password value.

COMMENT
//ANALYTIC Export data to Results
//PASSWORD 1 HighBond access token:
//RESULT LOG
END

OPEN AR_Exceptions
EXPORT FIELDS No Due Date Ref Amount Type ACLGRC PASSWORD 1 TO "10926@us" OVERWRITE

Robots app examples

Export data to the Robots app in HighBond

Note

You cannot export to an ACL robot. You must export to a HighBond robot or a Workflow robot.

For more information about how to access the data after exporting it to Robots, see load_working_file() method.

Specify the fields to export

You export specific fields from the Trans_May table to a compressed CSV file (*.csv.gz) in HighBond Robots. If a file with the same name already exists in robot 52053, in development mode, it is overwritten:

OPEN Trans_May
EXPORT FIELDS CARDNUM AS 'CARDNUM' CODES AS 'CODES' DATE AS 'DATE' CUSTNO AS 'CUSTNO' DESCRIPTION AS 'DESCRIPTION' AMOUNT AS 'AMOUNT' HBDATA TO "https://apis-us.highbond.com/v1/orgs/1000236/robots/52053/working_files?env=development" OVERWRITE

Create a password definition required to run the command in Robots

You create a two-part password definition with a PASSWORD analytic tag, and a matching PASSWORD parameter in the EXPORT command.

The //PASSWORD 1 analytic tag creates a password input parameter in a robot task. The matching PASSWORD 1 parameter in the EXPORT command references the stored and encrypted password value in the task. The required password is a valid HighBond access token. For more information, see Creating a password definition and specifying a password value.

COMMENT
//ANALYTIC Export Analytics data to Robots
//PASSWORD 1 HighBond access token:
//RESULT LOG
END

OPEN Trans_May
EXPORT FIELDS CARDNUM AS 'CARDNUM' CODES AS 'CODES' DATE AS 'DATE' CUSTNO AS 'CUSTNO' DESCRIPTION AS 'DESCRIPTION' AMOUNT AS 'AMOUNT' HBDATA PASSWORD 1 TO "https://apis-us.highbond.com/v1/orgs/1000236/robots/52053/working_files?env=development" OVERWRITE

Export all fields

You export all fields from the Trans_May table to a compressed CSV file (*.csv.gz) in HighBond Robots:

OPEN Trans_May
EXPORT FIELDS ALL HBDATA PASSWORD 3 TO "https://apis-us.highbond.com/v1/orgs/1000236/robots/52053/working_files?env=development" OVERWRITE

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 compressed CSV file (*.csv.gz) in HighBond Robots:

OPEN Vendor
EXPORT FIELDS ALL EXCLUDE Vendor_Last_Active Vendor_Review_Date HBDATA PASSWORD 3 TO "https://apis-us.highbond.com/v1/orgs/1000236/robots/52053/working_files?env=development" OVERWRITE

Remarks

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.

Limit Details
Number of records
  • Excel 2007 and later (*.xlsx) a maximum of 1,048,576 records by 16,384 fields (maximum worksheet size supported by Excel)
  • Excel 97 and 2003 a maximum of 65,536 records

Analytics tables that exceed these maximums export successfully, but the excess records are ignored and not exported.

Length of fields
  • no specific field length limit
  • combined field lengths cannot exceed the overall record length limit of 32 KB

    (32,765 characters in non-Unicode Analytics, 16,382 characters in Unicode Analytics)

  • for Excel 2.1, a maximum of 247 characters
Length of field names
  • a maximum of 64 characters
  • for Excel 2.1, a maximum of 248 characters
Length of worksheet names
  • a maximum of 31 characters

If you use an Analytics table name as the worksheet name, the name is truncated when exported to Excel if it exceeds 31 characters.

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
  • TO  filename value does not match any existing 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

  • TO  filename value, and an existing Excel file name, are identical
  • WORKSHEET worksheet_name does not match a worksheet name in the Excel file
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
  • TO  filename value, and an existing Excel file name, are identical
  • WORKSHEET worksheet_name matches a worksheet name in the Excel file

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:

  • Users with a Professional User or Professional Manager role for a Results collection can export results to any control test in the collection.

    Note

    Only users with the Professional Manager role can export and overwrite existing data in a control test.

  • HighBond System Admins and Results admins automatically get a Professional Manager role in all collections in the HighBond organization or organizations they administer.

For more information, see Results app permissions.

Export limits

The following limits apply when exporting to a control test:

  • A maximum of 100,000 records per export
  • A maximum of 100,000 records per control test
  • A maximum of 500 fields per record
  • A maximum of 256 characters per field

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 the Results app in HighBond.

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.

Password requirement

See Creating a password definition and specifying a password value.

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.

Permissions required for exporting to HighBond Robots

The ability to upload a compressed CSV file (*.csv.gz) to the Robots app requires a specific robot role assignment, or administrative privileges:

  • Users with an Editor or Owner role for a robot can upload a compressed CSV file to the robot. Users with a Reviewer role cannot upload.

  • Robots Admins are automatically a collaborator for every robot, with the role of Owner.

  • A HighBond System Admin with a Professional subscription is automatically a Robots Admin.

For more information, see Robots app permissions.

Creating a password definition and specifying a password value

When you run a script in Robots that exports data to the Results app, or uploads a compressed CSV file (*.csv.gz) to the Robots app, you need to include a password definition with the EXPORT command. The same requirement applies to scripts run in Analytics if you used offline activation.

Regardless of which method you use to create a password definition, the required password value is a HighBond access token, which you can generate in Launchpad. For more information, see Acquire a HighBond access token.

Password definition methods

Method Description

PASSWORD analytic tag

(For scripts that run in Robots)

If you use the PASSWORD analytic tag to create the numbered password definition for connecting to HighBond, no password value is specified in the script. When you create a task to run the script in Robots, an input field in the Task Designer allows you or another user to specify the actual password.

For more information, see PASSWORD analytic tag.

PASSWORD command

(For scripts that run in Analytics, offline activation)

If you use the PASSWORD command to create the numbered password definition for connecting to HighBond, no password value is specified in the script. A password prompt is displayed when the script attempts to connect.

For more information, see PASSWORD command.

SET PASSWORD command

(For scripts that run in Analytics, offline activation)

If you use the SET PASSWORD command to create the numbered password definition for connecting to HighBond, a password value is specified in the script, so no password prompt is displayed. This approach is appropriate for scripts designed to run unattended, but it exposes an actual password in clear text in the script, which may not be appropriate for your situation.

For more information, see SET PASSWORD command.

Acquire a HighBond access token

Caution

The generated access token matches the account used to sign in to HighBond. As a scriptwriter, specifying your own access token in a script may not be appropriate if the script will be used by other people.

Safeguard access tokens like any account password.

Use an existing access token unless you have a reason for creating a new one. If the existing token does not work, create a new one. Using an existing token cuts down on the number of tokens you need to manage.

  1. Do one of the following:

    • From the Analytics main menu, select Tools > HighBond Access Token.

    • In the Script Editor, right-click and select Insert > HighBond Token.

    The Manage API tokens page opens in your browser. You may be required to first sign in to HighBond.

    Access to the Manage API tokens page through Analytics is a convenience feature. You can also sign in to HighBond and access the page through your user profile without using Analytics.

  2. Do one of the following:

    • Use an existing token

      1. In the Token column, click the partially masked token that you want to use.

      2. Enter your HighBond account password and click Confirm.

        The unmasked token is displayed.

      3. Click Copy to copy the token.

        Tip

        Do not close the dialog box containing the token until you have successfully pasted the token.

    • Create a new token

      1. Click Add token > Analytics.

      2. In the New Analytics token side panel, specify the following information:

        Field or option Description
        Description

        Enter a description that provides useful information, such as:

        • The purpose of the token
        • Where the token is used – for example, the name and location of the Analytics script, or the name and location of the robot task
        Token expiry
        • Enabled the token expires after the number of days that you specify
        • Disabled the token never expires

        Note

        Your organization may have a security policy that requires tokens to expire after a certain amount of time. Creating tokens with an expiry is a good practice. HighBond sends you an automated email notification in advance of the expiry date.

        Expires in Specify the number of days before the token expires (1 to 365).
        Password Enter your HighBond account password.
      3. Click Generate token.

      4. Click Copy to copy the token.

        Tip

        Do not close the side panel containing the token until you have successfully pasted the token.

  3. Depending on which password definition method you are using, do one of the following:

    • PASSWORD analytic tag In the Task Designer in an ACL robot, paste the copied token into a password parameter field.

    • PASSWORD command In Analytics, paste the copied token into a password prompt that appears during script execution.

    • SET PASSWORD command In Analytics, paste the copied token at the appropriate point in the SET PASSWORD command syntax in a script.

  4. In Launchpad, close the dialog box or the side panel containing the token.

    If you created a new token, a partially masked version of the token is added to the top of your list of tokens.

    For more information, see Creating and managing HighBond access tokens.