Commands

ACLScript commands perform operations on data that are often broad in scope.

For example, the SUMMARIZE command groups records based on identical values in a field, and calculates subtotals and statistical values for each group.

A number of commands output results to a new ACL table. Other commands perform various application tasks.

Command list

A full list of commands available in ACL, organized alphabetically or by category, appears below.

Conventions used in command documentation, and some general usage details, are also provided.

Commands in alphabetical order

Command Description

ACCEPT

Creates a dialog box that interactively prompts users for one or more script input values. Each input value is stored in a named character variable.

ACCESSDATA

Imports data from a variety of ODBC-compliant data sources.

The command takes the form ACCESSDATA64 or ACCESSDATA32 depending on whether you are using a 64-bit or 32-bit ODBC driver.

ACTIVATE

Adds field definitions stored in an ACL workspace to the existing set of field definitions in an ACL table layout.

AGE

Groups records into aging periods based on values in a date or datetime field. Counts the number of records in each period, and also subtotals specified numeric fields for each period.

APPEND

Combines records from two or more ACL tables by appending them in a new ACL table.

ASSIGN

Creates a variable and assigns a value to the variable.

BENFORD

Counts the number of times each leading digit (1–9) or leading digit combination occurs in a field, and compares the actual count to the expected count. The expected count is calculated using the Benford formula.

CALCULATE

Calculates the value of one or more expressions.

CLASSIFY

Groups records based on identical values in a character or numeric field. Counts the number of records in each group, and also subtotals specified numeric fields for each group.

CLOSE

Closes an ACL table, index file, or log file, or ends a Script Recorder session.

COMMENT

Adds an explanatory note to a script without affecting processing.

COUNT

Counts the total number of records in the current view, or only those records that meet the specified condition.

CREATE LAYOUT

Creates an empty ACL table layout, which may be required in certain scripting situations.

CROSSTAB

Groups records based on identical combinations of values in two or more character or numeric fields, and displays the resulting groups in a grid of rows and columns. Counts the number of records in each group, and also subtotals specified numeric fields for each group.

CVSEVALUATE

For classical variables sampling, provides four different methods for projecting the results of sample analysis to the entire population.

CVSPREPARE

Stratifies a population, and calculates a statistically valid sample size for each stratum, for classical variables sampling.

CVSSAMPLE

Draws a sample of records using the classical variables sampling method.

DEFINE COLUMN

Creates and adds one or more columns to an existing view.

DEFINE FIELD

Defines a physical data field in an ACL table layout.

DEFINE FIELD...COMPUTED

Defines a computed field in an ACL table layout.

DEFINE RELATION

Defines a relation between two ACL tables.

DEFINE REPORT

Creates a new view or opens an existing view.

DEFINE TABLE DB

Defines an ACL server table by connecting to a database table using AX Connector. You can connect to a Microsoft SQL Server, Oracle, or DB2 database.

DEFINE VIEW

Defines a new view or overwrites an existing view.

DELETE

Deletes an ACL project item, a field from a table layout, a variable, one or more table history entries, a relation between tables, or a file in a Windows folder. Also removes a column from a view.

DIALOG

Creates a custom dialog box that interactively prompts users for one or more script input values. Each input value is stored in a named variable.

DIRECTORY

Generates a list of files and folders in the specified directory.

DISPLAY

Displays information about an ACL project or the computer ACL is running on. Can also display the result of an expression, or the output of a function.

DO REPORT

Generates the specified ACL report.

DO SCRIPT

Executes a secondary script, or an external script, from within an ACL script.

DUMP

Displays the contents of a file, or the current record, in hexadecimal, ASCII, and EBCDIC character encodings.

DUPLICATES

Detects whether duplicate values or entire duplicate records exist in an ACL table.

ESCAPE

Terminates the script being processed, or all scripts, without exiting ACL.

EVALUATE

For record sampling or monetary unit sampling, projects errors found in sampled data to the entire population, and calculates upper limits on deviation rate, or misstatement amount.

EXECUTE

Executes an application or process external to ACL. Emulates the Windows Run command. Can be used to interact with the Windows command prompt.

EXPORT

Exports data from ACL to the specified file format, or to Results in ACL GRC.

EXTRACT

Extracts data from an ACL table and outputs it to a new ACL table, or appends it to an existing ACL table. You can extract entire records or selected fields.

FIELDSHIFT

Shifts the start position of a field definition in a table layout.

FIND

Searches an indexed character field for the first value that matches the specified character string.

FUZZYDUP

Detects nearly identical values (fuzzy duplicates) in a character field.

GAPS

Detects whether a numeric or datetime field in an ACL table contains one or more gaps in sequential data.

GROUP

Executes one or more ACLScript commands on a record before moving to the next record in the table, with only one pass through the table. Command execution can be controlled by conditions.

HELP

Launches the ACL Analytics Help Docs in a browser.

HISTOGRAM

Groups records based on values in a character or numeric field, counts the number of records in each group, and displays the groups and counts in a bar chart.

IF

Specifies a condition that must evaluate to true in order to execute a command.

IMPORT ACCESS

Creates an ACL table by defining and importing a Microsoft Access database file.

IMPORT DELIMITED

Creates an ACL table by defining and importing a delimited text file.

IMPORT EXCEL

Creates an ACL table by defining and importing a Microsoft Excel worksheet or named range.

IMPORT GRCPROJECT

Creates an ACL table by importing an ACL GRC Projects table.

IMPORT GRCRESULTS

Creates an ACL table by importing an ACL GRC Results table or interpretation.

IMPORT LAYOUT

Imports an external table layout file (.layout) to an ACL project.

IMPORT ODBC

Creates an ACL table by defining and importing data from an ODBC data source.

ODBC stands for Open Database Connectivity, a standard method for accessing databases.

IMPORT PDF

Creates an ACL table by defining and importing an Adobe PDF file.

IMPORT PRINT

Creates an ACL table by defining and importing a Print Image (Report) file.

IMPORT SAP

Creates an ACL table by importing data from an SAP system using Direct Link.

IMPORT XBRL

Creates an ACL table by defining and importing an XBRL file.

IMPORT XML

Creates an ACL table by defining and importing an XML file.

INDEX

Creates an index for an ACL table that allows access to the records in a sequential order rather than a physical order.

JOIN

Combines fields from two ACL tables into a new, single ACL table.

LIST

Outputs the data in one or more fields in an ACL table to a display formatted in columns.

LOCATE

Searches for the first record that matches the specified value or condition, or moves to the specified record number.

LOOP

Executes a series of ACLScript commands repeatedly on a record while a specified condition evaluates to true.

MERGE

Combines records from two sorted ACL tables with an identical structure into a new ACL table that uses the same sort order as the original tables.

NOTES

Creates, modifies, or removes a note associated with an individual record in an ACL table.

NOTIFY

Sends an email notification message.

OPEN

Opens an ACL table and the associated data file.

PASSWORD

Creates a password definition, without a password value, that prompts users for a password while a script is running.

PAUSE

Pauses a script, and displays information in a dialog box for users.

PRINT

Prints a text file, an ACL log file, or an ACL project item that has been exported as an external file – a script (.aclscript), a table layout (.layout), or a workspace (.wsp). You can also print a graph that has been generated by a command.

PROFILE

Generates summary statistics for one or more numeric fields, or numeric expressions, in an ACL table.

QUIT

Ends the current session and closes ACL.

RANDOM

Generates a set of random numbers.

RCOMMAND

Passes an ACL table to an external R script as a data frame and creates a new table in the ACL project using output from the external R script.

REFRESH

Updates the data in an ACL table from its associated data source.

RENAME

Renames an ACL project item or a file.

REPORT

Formats and generates a report based on the open ACL table.

RETRIEVE

Retrieves the result of a Direct Link query submitted for background processing.

SAMPLE

Draws a sample of records using either the record sampling or monetary unit sampling method.

SAVE

Copies an ACL table and saves it with a different name, or saves an ACL project.

SAVE LAYOUT

Saves an ACL table layout to an external table layout file (.layout), or saves table layout metadata to an ACL table.

SAVE LOG

Saves the entire command log, or the log entries for the current ACL session, to an external file.

SAVE TABLELIST

Saves a list of all tables in an ACL project to an ACL table or a CSV file.

SAVE WORKSPACE

Creates and saves a workspace.

SEEK

Searches an indexed character field for the first value that matches the specified character expression or character string.

SEQUENCE

Determines if one or more fields in an ACL table are in sequential order, and identifies out-of-sequence items.

SET

Sets a configurable ACL option.

SIZE

Calculates a statistically valid sample size, and sample interval, for record sampling or monetary unit sampling.

SORT

Sorts records in an ACL table into an ascending or descending sequential order, based on a specified key field or fields. The results are output to a new, physically reordered ACL table.

STATISTICS

Calculates statistics for one or more numeric or datetime fields in an ACL table.

STRATIFY

Groups records into numeric intervals based on values in a numeric field. Counts the number of records in each interval, and also subtotals specified numeric fields for each interval.

SUMMARIZE

Groups records based on identical values in one or more character, numeric, or datetime fields. Counts the number of records in each group, and also subtotals specified numeric fields for each group.

TOP

Moves to the first record in an ACL table.

TOTAL

Calculates the total value of one or more fields in an ACL table.

VERIFY

Checks for data validity errors in one or more fields in an ACL table by verifying that the data is consistent with the field definitions in the table layout.

Import and export data

Command Description
ACCESSDATA

Imports data from a variety of ODBC-compliant data sources.

The command takes the form ACCESSDATA64 or ACCESSDATA32 depending on whether you are using a 64-bit or 32-bit ODBC driver.

DEFINE TABLE DB

Defines an ACL server table by connecting to a database table using AX Connector. You can connect to a Microsoft SQL Server, Oracle, or DB2 database.

EXPORT

Exports data from ACL to the specified file format, or to Results in ACL GRC.

IMPORT ACCESS

Creates an ACL table by defining and importing a Microsoft Access database file.

IMPORT DELIMITED

Creates an ACL table by defining and importing a delimited text file.

IMPORT EXCEL

Creates an ACL table by defining and importing a Microsoft Excel worksheet or named range.

IMPORT GRCPROJECT

Creates an ACL table by importing an ACL GRC Projects table.

IMPORT GRCRESULTS

Creates an ACL table by importing an ACL GRC Results table or interpretation.

IMPORT ODBC

Creates an ACL table by defining and importing data from an ODBC data source.

ODBC stands for Open Database Connectivity, a standard method for accessing databases.

IMPORT PDF

Creates an ACL table by defining and importing an Adobe PDF file.

IMPORT PRINT

Creates an ACL table by defining and importing a Print Image (Report) file.

IMPORT SAP

Creates an ACL table by importing data from an SAP system using Direct Link.

IMPORT XBRL

Creates an ACL table by defining and importing an XBRL file.

IMPORT XML

Creates an ACL table by defining and importing an XML file.

RETRIEVE

Retrieves the result of a Direct Link query submitted for background processing.

Profile and verify data

Command Description

BENFORD

Counts the number of times each leading digit (1–9) or leading digit combination occurs in a field, and compares the actual count to the expected count. The expected count is calculated using the Benford formula.

COUNT

Counts the total number of records in the current view, or only those records that meet the specified condition.

DUPLICATES

Detects whether duplicate values or entire duplicate records exist in an ACL table.

FUZZYDUP

Detects nearly identical values (fuzzy duplicates) in a character field.

GAPS

Detects whether a numeric or datetime field in an ACL table contains one or more gaps in sequential data.

PROFILE

Generates summary statistics for one or more numeric fields, or numeric expressions, in an ACL table.

SEQUENCE

Determines if one or more fields in an ACL table are in sequential order, and identifies out-of-sequence items.

STATISTICS

Calculates statistics for one or more numeric or datetime fields in an ACL table.

TOTAL

Calculates the total value of one or more fields in an ACL table.

VERIFY

Checks for data validity errors in one or more fields in an ACL table by verifying that the data is consistent with the field definitions in the table layout.

Sort data

Command Description

INDEX

Creates an index for an ACL table that allows access to the records in a sequential order rather than a physical order.

SORT

Sorts records in an ACL table into an ascending or descending sequential order, based on a specified key field or fields. The results are output to a new, physically reordered ACL table.

Group data

Command Description

AGE

Groups records into aging periods based on values in a date or datetime field. Counts the number of records in each period, and also subtotals specified numeric fields for each period.

CLASSIFY

Groups records based on identical values in a character or numeric field. Counts the number of records in each group, and also subtotals specified numeric fields for each group.

CROSSTAB

Groups records based on identical combinations of values in two or more character or numeric fields, and displays the resulting groups in a grid of rows and columns. Counts the number of records in each group, and also subtotals specified numeric fields for each group.

HISTOGRAM

Groups records based on values in a character or numeric field, counts the number of records in each group, and displays the groups and counts in a bar chart.

STRATIFY

Groups records into numeric intervals based on values in a numeric field. Counts the number of records in each interval, and also subtotals specified numeric fields for each interval.

SUMMARIZE

Groups records based on identical values in one or more character, numeric, or datetime fields. Counts the number of records in each group, and also subtotals specified numeric fields for each group.

Combine data

Command Description

APPEND

Combines records from two or more ACL tables by appending them in a new ACL table.

DEFINE RELATION

Defines a relation between two ACL tables.

EXTRACT

Extracts data from an ACL table and outputs it to a new ACL table, or appends it to an existing ACL table. You can extract entire records or selected fields.

JOIN

Combines fields from two ACL tables into a new, single ACL table.

MERGE

Combines records from two sorted ACL tables with an identical structure into a new ACL table that uses the same sort order as the original tables.

Sample data

Command Description

CVSPREPARE

Stratifies a population, and calculates a statistically valid sample size for each stratum, for classical variables sampling.

CVSSAMPLE

Draws a sample of records using the classical variables sampling method.

CVSEVALUATE

For classical variables sampling, provides four different methods for projecting the results of sample analysis to the entire population.

SIZE

Calculates a statistically valid sample size, and sample interval, for record sampling or monetary unit sampling.

SAMPLE

Draws a sample of records using either the record sampling or monetary unit sampling method.

EVALUATE

For record sampling or monetary unit sampling, projects errors found in sampled data to the entire population, and calculates upper limits on deviation rate, or misstatement amount.

Field, record, and table

Command Description

ACTIVATE

Adds field definitions stored in an ACL workspace to the existing set of field definitions in an ACL table layout.

CREATE LAYOUT

Creates an empty ACL table layout, which may be required in certain scripting situations.

DEFINE COLUMN

Creates and adds one or more columns to an existing view.

DEFINE FIELD

Defines a physical data field in an ACL table layout.

DEFINE FIELD...COMPUTED

Defines a computed field in an ACL table layout.

DEFINE REPORT

Creates a new view or opens an existing view.

DEFINE VIEW

Defines a new view or overwrites an existing view.

EXTRACT

Extracts data from an ACL table and outputs it to a new ACL table, or appends it to an existing ACL table. You can extract entire records or selected fields.

FIELDSHIFT

Shifts the start position of a field definition in a table layout.

FIND

Searches an indexed character field for the first value that matches the specified character string.

IMPORT LAYOUT

Imports an external table layout file (.layout) to an ACL project.

LIST

Outputs the data in one or more fields in an ACL table to a display formatted in columns.

LOCATE

Searches for the first record that matches the specified value or condition, or moves to the specified record number.

NOTES

Creates, modifies, or removes a note associated with an individual record in an ACL table.

OPEN

Opens an ACL table and the associated data file.

REFRESH

Updates the data in an ACL table from its associated data source.

SAVE

Copies an ACL table and saves it with a different name, or saves an ACL project.

SAVE LAYOUT

Saves an ACL table layout to an external table layout file (.layout), or saves table layout metadata to an ACL table.

SAVE TABLELIST

Saves a list of all tables in an ACL project to an ACL table or a CSV file.

SAVE WORKSPACE

Creates and saves a workspace.

SEEK

Searches an indexed character field for the first value that matches the specified character expression or character string.

TOP

Moves to the first record in an ACL table.

User interaction and general scripting

Command Description

ACCEPT

Creates a dialog box that interactively prompts users for one or more script input values. Each input value is stored in a named character variable.

ASSIGN

Creates a variable and assigns a value to the variable.

CALCULATE

Calculates the value of one or more expressions.

CLOSE

Closes an ACL table, index file, or log file, or ends a Script Recorder session.

COMMENT

Adds an explanatory note to a script without affecting processing.

DELETE

Deletes an ACL project item, a field from a table layout, a variable, one or more table history entries, a relation between tables, or a file in a Windows folder. Also removes a column from a view.

DIALOG

Creates a custom dialog box that interactively prompts users for one or more script input values. Each input value is stored in a named variable.

DO SCRIPT

Executes a secondary script, or an external script, from within an ACL script.

ESCAPE

Terminates the script being processed, or all scripts, without exiting ACL.

EXECUTE

Executes an application or process external to ACL. Emulates the Windows Run command. Can be used to interact with the Windows command prompt.

GROUP

Executes one or more ACLScript commands on a record before moving to the next record in the table, with only one pass through the table. Command execution can be controlled by conditions.

IF

Specifies a condition that must evaluate to true in order to execute a command.

LOOP

Executes a series of ACLScript commands repeatedly on a record while a specified condition evaluates to true.

NOTIFY

Sends an email notification message.

PASSWORD

Creates a password definition, without a password value, that prompts users for a password while a script is running.

PAUSE

Pauses a script, and displays information in a dialog box for users.

RCOMMAND

Passes an ACL table to an external R script as a data frame and creates a new table in the ACL project using output from the external R script.

RENAME

Renames an ACL project item or a file.

SET

Sets a configurable ACL option.

Report

Command Description

DO REPORT

Generates the specified ACL report.

PRINT

Prints a text file, an ACL log file, or an ACL project item that has been exported as an external file – a script (.aclscript), a table layout (.layout), or a workspace (.wsp). You can also print a graph that has been generated by a command.

REPORT

Formats and generates a report based on the open ACL table.

File and system

Command Description

DIRECTORY

Generates a list of files and folders in the specified directory.

DISPLAY

Displays information about an ACL project or the computer ACL is running on. Can also display the result of an expression, or the output of a function.

DUMP

Displays the contents of a file, or the current record, in hexadecimal, ASCII, and EBCDIC character encodings.

HELP

Launches the ACL Analytics Help Docs in a browser.

QUIT

Ends the current session and closes ACL.

RANDOM

Generates a set of random numbers.

SAVE LOG

Saves the entire command log, or the log entries for the current ACL session, to an external file.

Abbreviating command names

Caution

ACL recommends that you do not abbreviate command names in scripts, and that you use the full version of each name.

Abbreviation makes scripts harder to read and to understand. Without complete command names, searching commands in the online help becomes more difficult.

Abbreviation is especially problematic if your scripts will be modified or inherited by someone else who may not be familiar with the abbreviations.

When specifying commands in scripts, you can abbreviate their names. You must include enough leading characters from a command name to uniquely identify the command among all ACL commands.

For example:

  • EXT uniquely identifies the EXTRACT command and therefore is a valid abbreviation.
  • EX does not uniquely identify the EXTRACT command and generates an error message.

You can make an abbreviation as short as you want, provided that it still uniquely identifies the command.

For example, all the following abbreviations are valid for the OPEN command:

  • OPE
  • OP
  • O

Note

As abbreviations get shorter they become harder for other users to recognize.

The order of parameters in commands

Note

As a scripting best practice, ACL script writers should sequence parameters in exactly the same order that they appear in the command log when you run a command through the ACL user interface.

Many ACL commands allow some flexibility in the order of their parameters. For example, these three variations of the same CLASSIFY command all perform an identical operation, and all execute correctly:

CLASSIFY ON CUSTNO SUBTOTAL AMOUNT IF AMOUNT >= 100 TO "Classify_1.FIL" OPEN APPEND KEY CODES STATISTICS
CLASSIFY ON CUSTNO SUBTOTAL AMOUNT KEY CODES IF AMOUNT >= 100 TO "Classify_1.FIL" OPEN APPEND STATISTICS
CLASSIFY ON CUSTNO IF AMOUNT >= 100 SUBTOTAL AMOUNT STATISTICS KEY CODES TO "Classify_1.FIL" APPEND OPEN

A few commands require that one or more parameters appear in a specific order. The required order is stated in the topics for those commands.

Command documentation conventions

Convention

Used for:

UPPERCASE

ACLScript keywords.

In the generic syntax sections, keywords that are not enclosed in angled brackets < > are required syntax items.

Note

Throughout ACL documentation, command and parameter keywords are presented in uppercase, which is simply a formatting convention. ACL does not require that keywords are entered in uppercase.

italic

User-supplied command parameters.

|

(vertical bar)

Separates syntax items enclosed in brackets or braces. You can use only one of the items.

< >

(angled brackets)

Optional syntax items. Do not type the brackets.

{ }

(braces)

Required syntax items. Do not type the braces.

<,...n>

Indicates the preceding item can be repeated n number of times. The occurrences are separated by commas.

<...n>

Indicates the preceding item can be repeated n number of times. The occurrences are separated by blanks.

[label] ::=

The name of a block of syntax.

This convention is used to group and label sections of lengthy syntax or a unit of syntax that can be used in more than one location. Each location in which the block of syntax can be used is indicated with the label enclosed in square brackets. For example: [field_syntax]

[ Back to top ]

(C) ACL Services Ltd. All Rights Reserved. Thursday, October 4, 2018