EXPORT command

Used to export data from ACL in the specified file format.

Syntax

EXPORT <FIELDS> field_name <AS export_name> <...n> export_type <SCHEMA>
TO {filename|aclgrc_id} <PASSWORD num> <IF test> <WHILE test>
<{FIRST|NEXT} range> <UNICODE> <APPEND> <KEEPTITLE> <SEPARATOR character>
<QUALIFIER character> <WORKSHEET worksheet_name>

Parameters

FIELDS

Indicates the start of the list of fields to export. This keyword can be omitted.

field_name AS export_name

Specifies the name of a field in the table layout to export. You can optionally specify a different name for the field in the export file in export_name. You can repeat this parameter as many times as necessary.

export_type

Specify the output file format using one of the following options:

  • ACCESS – Exports data as a Microsoft Access database file (.mdb). By default, the data is exported as Unicode.

  • ACLGRC – Exports data to the Results Manager module of ACL GRC.

  • ASCII – Exports data as ASCII plain text (.txt).

  • CLIPBOARD – Copies the exported data to the Windows Clipboard.

  • DBASE – Exports data as a dBASE compatible file (.dbf).

  • DELIMITED – Exports data as a delimited text file (.del).

  • EXCEL – Exports data to an Excel file (.xls) compatible with Microsoft Excel 1997 to 2003.

  • JSON – Exports data to a JSON file (.json)

  • LOTUS – Exports data to a Lotus 123 file.

  • WDPF6 – Exports data to a Wordperfect 6 file.

  • WORD – Exports data to a Microsoft Word file (.doc).

  • WP – Exports data to a Wordperfect file.

  • XLS21 – Exports data to a Microsoft Excel version 2.1 file.

  • XLSX – Exports data to an Excel file (.xlsx) file compatible with Microsoft Excel 2007 to 2010. By default, the data is exported as Unicode.

  • XML – Exports data to an XML file (.xml).

SCHEMA

Optional. Applies to XML file output only. Specifies that you want to 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.

TO filename | aclgrc_id
  • Specify TO filename to export the results to a 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.

  • Specify TO aclgrc_id to export the results to the Results Manager module of ACL GRC. The aclgrc_id value must include the control test ID number, and if you are exporting to a data center located outside the United States, the data center code. The control test ID number and the data center code must be separated by the at sign (@) – for example, TO "99@eu".

    The data center code specifies which regional ACL GRC server you are exporting the data to: ap (Asia Pacific), eu (Europe), or us (United States). You can use only the data center code or codes authorized for your organization’s installation of ACL GRC. The US data center is the default, so specifying "@us" is optional.

    The aclgrc_id value must be enclosed in quotation marks.

PASSWORD num

Optional. ACL GRC users only. Specifies the numbered password definition to use for connecting to the Results Manager module of ACL GRC.

Note that you are not specifying the password itself. You are specifying a password definition previously created using either the PASSWORD or the SET PASSWORD command. The num parameter corresponds to the number of the password definition. For example, if you previously created two password definitions in a script, PASSWORD 2 specifies that the second one is used with the EXPORT command.

For more information, see PASSWORD command and SET command.

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.

UNICODE

Optional. Only available in the Unicode edition of ACL. Specifies that the ACL data should be exported with Unicode character encoding applied. You can specify this parameter for data exported to the Windows Clipboard (CLIPBOARD), delimited text files (DELIMITED), Microsoft Excel 2.1 (XLS21), or plain text (ASCII)

APPEND

Optional. Applies to text (ASCII) and delimited text (DELIMITED) file output only. 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, because ACL appends output to an existing file regardless of its structure.

KEEPTITLE

Optional. Applies to text (ASCII) and delimited text file (DELIMITED) output only. Specifies that you want to include the first row of data as field names.

SEPARATOR character

Optional. Applies to delimited text file (DELIMITED) output only. Specifies the character to use as the separator between fields. You must specify the character value as a quoted string. By default, ACL uses a comma.

QUALIFIER character

Optional. Applies to delimited text file (DELIMITED) output only. Specifies the character to use as the text qualifier to identify field values. You must specify the character value as a quoted string. By default, ACL uses double quotation marks.

WORKSHEET worksheet_name

Optional. Applies to Excel 2007 to 2010 (XLSX) output only. Must be placed last in the EXPORT command syntax. Specifies the name of the Excel worksheet created in a new or existing Excel file. If you omit this parameter, ACL uses the name of the ACL table you are exporting.

The worksheet_name value 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.

Remarks

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.

You can export a maximum of 65,536 records to Excel 97 to 2003, and a maximum of 1,048,576 records to Excel 2007 and 2010. ACL tables that exceed these maximums export successfully, but the excess records are ignored and not exported.

If you use the optional WORKSHEET parameter when exporting to Excel 2007 to 2010 files, overwriting files with the same name is disabled and the exported data is added as an additional worksheet to the existing file. If you do not use the WORKSHEET parameter, files with the same name create the standard overwrite situation. Overwriting worksheets or named ranges with the same name is not supported. If the worksheet_name value is identical to the name of an existing worksheet or named range in the target Excel file, the command fails with an error message.

ACL GRC users

The ability to export results to a control test in Results Manager requires a specific ACL GRC role assignment, or administrative privileges:

Individual export operations to ACL GRC are limited to 10,000 records. If you need to export more than 10,000 records to a particular control test, you can use multiple export operations, and the FIRST or NEXT parameters, to export records in blocks of 10,000, up to a maximum of 100,000 records. All exported blocks are appended in the destination table in ACL GRC. Exports are also limited to 50 columns.

If you used the PASSWORD command to create the numbered password definition for connecting to ACL GRC, no password value is specified, so a password prompt is displayed when the script containing the EXPORT command is run.

If you used the SET PASSWORD command to create the numbered password definition for connecting to ACL GRC, a password value is specified, so no password prompt is displayed, which is appropriate for scripts designed to run unattended.

Regardless of which method you use to create the password definition, the required password value is an ACL GRC access token:

Examples

a. Export data to an Excel 2007/2010 file

OPEN Vendor
EXPORT FIELDS Vendor_No Vendor_Name Vendor_City XLSX to "VendorExport"

b. Export data to an Excel 2007/2010 file and specify a worksheet name

OPEN Vendor
EXPORT FIELDS Vendor_No Vendor_Name Vendor_City XLSX to "VendorExport"
WORKSHEET Vendors_US

c. Export data to multiple delimited files in a GROUP command

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


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