Category Archives: Tips & Tricks

ACL Crash: How to Decide Between Working Copy and Last Saved

An ACL project crashing can be a disappointing moment for any ACL user. Here is a summary of what you will see and some helpful tips to take into consideration when determining which option will be best in your situation. We’ll also show you how to make a copy so you can keep both versions in case you accidentally make the wrong choice.

The Options

When you go to reopen the project you are left with two options: Working Copy or Last-Saved version.


The working copy is a .AC file that is automatically created by ACL for every project and serves as a temporary auto-save file. All unsaved changes to the ACL project are recorded here so that the changes can be recovered if ACL closes unexpectedly.

The last-saved version is the .ACL file, and contains the project at the point in time that you last saved.

WARNING!!! By selecting the working copy, ACL will automatically overwrite the last saved version as soon as it is opened.

Deciding Factors

1-     How large is the project file?

2-     When was the file last modified?

3-     What were you doing?

First, review the file size and last modified date/time. To do this, find where your ACL project is located on your computer/network drive. Sort your folder by Type (so the .AC and .ACL files appear at the top) and compare the file size and modified date/time between the .ACL file (last-saved) and the .AC file (working copy).

June 2

  • Factor 1- File Size

Usually, a larger file size indicates that there is more content in the project. However, the data may be bad if it didn’t complete the analysis/command that ACL was in the middle of processing when it crashed. Depending on what you were doing (see below), it will not always be obvious that the analysis/command was mid-process when the project crashed. In addition, ACL frequently creates large temporary files to execute a command. These files will impact the size of the project, but may not be critical to your results. Temporary files may be misleading if you are relying on the file size to determine which option to select.

  • Factor 2- Last Modified Date/Time

If you know the last time/date you added or modified something that you need, you may want to consider this factor more heavily than the project size when choosing which file to open.

  • Factor 3- What was ACL Doing?

After comparing the file size and last modified date, determine what you were in the middle of doing when ACL crashed. Knowing what ACL was doing when it crashed is likely to be the most critical factor to determining which option to pick.

A few examples of things that you may have been doing are listed below. If none of the options below describe what you were doing, skip ahead to the “When You Don’t Know” selection below.

Importing Data– The larger size file likely has more data, but may be incomplete… you may want to consider the modified time and go with the option that you know is correct/complete.

Performing Analysis with a Command-  This is a tough call. You may want to default to the “When you don’t know” selection below just to be safe.

Writing a Script-  Scripts typically do not significantly impact the file size. The most recent file may be your best bet.

Running an Analysis Script-  This is also a tough call. You may want to default to the “When You Don’t Know” selection below just to be safe.

Running a Clean-Up Script-  The smaller size file is likely the most up to date.

When You Don’t Know- Play it Safe

If you are not 100% sure which one to choose, you can always select cancel and create a copy of both files and save it as a different name. If you discover that the selected copy is not what you want, simply close the project and open the re-named copy that you created. If necessary, you can change the file extension from .AC to .ACL to open your re-named copy.

Using the ACL Command Line

The command line can be a very helpful tool to detect the cause of an error in a script, quickly see results, open a table that may be hard to find in the project navigator folders, or to quickly display the table layout or history. Below we have summarized the steps to use the command line for these procedures, and how to change your display settings to view the command line.

View Command Line

If the Command Line text box is not visible, select Window from the ACL menu bar and select Show Command Line.

May 1

Execute Commands Individually to Find Script Error

To edit and/or re-execute commands recorded in the log file. While you can only enter one command at a time, stepping through the code line by line can be helpful in working backwards and detecting where an issue occurred. This can be done by clicking an entry in the Log, adding it to the command line, and clicking the green play button on the right to run the command.

May 2

May 3

Open a Table

  • To easily find a table that you would like to open in your project, go to the log and click on one of the OPEN commands for the table you would like to open. The command will now be displayed in the command line and will open after running the command.

May 4

  • After running this command in the command line, there is also the option to use the F4 key to get a list of tables in your project.

May 5

Quickly See Results

To quickly evaluate the effects of an expression with the CALCULATE command.

May 6

Quickly View Table Layout or History

  • To quickly view or print the table layout in ACL, type DISPLAY or DIS in the command line. The table layout has useful information about the fields in a table, such as the length of a field, the field type, and the expressions used to create computed fields.

May 10

  • To quickly view the table history in ACL, type DISPLAY HISTORY or DIS HIS in the command line. This command will display the history of the current table you have open.

May 11

Changing Imported Fields

Sometimes the source data in your ACL project may change, or you may realize that a field is missing, or imported incorrectly. One’s natural inclination might be to re-import the data file into ACL through the import wizard. While this approach will certainly work, it can be performed more efficiently by modifying the import from your log.   The steps below summarize how to find your import command in your log and how to modify the command, depending on what you need to change. The field length, type, and name can easily be modified without using the wizard to re-import and define your data file.

  1. Select the log tab on the bottom of the Project Navigation Pane.

Number 1

  1. Find your IMPORT command that you would like to modify. If you do not see it in the project navigator pane, use the search/find to find your most recent or first IMPORT in your log.
  1. To search in the log, right click from within the log and select ‘Find’ (or type CTRL+F).

Number 3

  1. Enter the main word that you would like to find in the log, such as IMPORT or the source file name. Remember, ACL defaults to search up, so if you are at the top of your log change the direction to “down”.

Number 4

  1. Once you have found your import command save it to a script by checking the box next to the IMPORT command, right click and select save selected items, and select script. After saving the import command to a script, uncheck the box next to the IMPORT command.

Number 5

    1. Open the new script and modify the IMPORT command by identifying the field that you would like to modify.
  • To change the field length: change the width number after the WID parameter to the desired length.
  • To change the field type: change the letter after the field name to “C” for character, “D” for date/time, or “N” for numeric. For Date/time fields, you also need to provide the display format (e.g. PIC “YYYY-MM-DD”). For Numeric fields, you need to provide the number of decimals (e.g. DEC 2).
  • To change the source field name, change the field name within the double quotes directly after the FIELD parameter. To change the display of the field name within your table, add the desired name within the double quotes after the AS parameter.

In this example, we want to modify the “Work Dept” field to a length of 5.

Number 6

Number 6.2

NOTE: If you need to keep your old data table, be sure to change the table name in your modified IMPORT command (by modifying the table display name and table file name within the double quotes after the TO parameter).

Bonus Tip: If you ever realize that you do not want to exclude a field in your ACL import, simply put IGNORE and the column number where the field occurs.

  1. After you have made all the desired changes to the new import script click run to re-import your data file.

Tips & Tricks: Creating a Field List Variable

Do you ever end up with your results in a table with more fields than necessary, or the fields are in an illogical order? Here is a simple solution…

An easy way to control the order the fields is to use a variable to define the fields. Create a script to define the variable. Make the variable name something that is easy to identify, such as v_FieldList. Use the ASSIGN command to define the variable with the fields that you want to see in your results, and in the order that makes sense for your results. The picture below provides an example of the script to define the field list variable, and extract these fields to a table.

It is important to remember to enclose the list of fields in (“ ”) double quotes. When the results are extracted to another table, the fields will be put in the order specified in the variable. This makes it easy to change the order of the fields and maintain consistency across multiple results from the same table.



Tips & Tricks: Renaming a Field

When extracting results to a file, you may want to change the name of some fields that are being extracted. I found this particularly useful after running a join and using this tip to help distinguish which fields belonged to what table.

EXTRACT DocumentNo AS ‘Inv_DocumentNo’ … TO “R_Invoices.fil”

The key is to remember to put single, not double, quotes around the new name you want to give your field.

Tips & Tricks: Helpful Naming Conventions

Establishing file naming conventions in ACL is important so that others know where to find specific data and what each file contains. To help us better organize our projects in a consistent and descriptive manner, we have adopted a few naming conventions that eliminate the confusion between a result file and an arbitrary file that was used to create that final file.

Prefix all final files with a “R_” at the beginning of the file name to indicate a result file.

Prefix all temporary files with a “T_” at the beginning of the file name to indicate a temporary table.

Just as important as it is to make sure you are using consistent naming conventions throughout your project, it is also in best practice to prefix all computed fields with “c.” Since all computed fields are not original data, this naming convention will make it easier to identify which ones you created.

Tips & Tricks: Using the Log to Rerun a Join

Did you ever run a complicated join and realized you forgot to add one primary field or that the length of your primary fields did not match? Rerunning a join through point and click can be a tedious task; however the log can be your best friend whenever you need to tweak something.

  1. Select the log so it opens in the display area of the left side.Join_1
  2. Find the last JOIN command that you would like to make changes to. If you have a hard time finding where your last join occurred, you can do a search in the log.
  3. To search in the log, right click and select ‘Find.’Join_2
  4. Enter the main word that you would like to find in the log. In this case, I wanted the log to find anything with the word Join. You can select the direction that you would like to search in as well. Knowing that my Join was done recently, I selected up. This means that it will start looking from the bottom to the top for the word I want it to find.Join_4

After clicking through some of the results, I have now found the Join that I would like to make  changes to.


  1. Click on your Primary Table (in this example the Primary Table is Payroll) and select Run in the Command Line. Do the same thing for the Secondary Table.Join_7
  2. Click on your last JOIN command and double-click the command underlined in blue in the display area.Join_6
  3. Your original JOIN dialog box will appear with all of your previous setting. Make any changes in the dialog box and run.Join_7
  4. After the new Join runs, open the log again and copy and paste it into the script. We recommend doing this to help save time in the future as it will make it easier to refer back to if you ever need to make any other changes.


Bonus Tip: This little trick applies to anything in the log that you might want to add or do differently including commands such as Summarize, Classify, Statistics, and Total.

Number Formats

Are you tired of trying to figure out if 14258769 is 1.4 million or 14 million? ACL, as a default, does not format your numbers in any convenient way. Thankfully, there is a quick SET command that will fix that.

SET PICTURE "format"

Where format is your choice of formatting (duh). A few suggestions, “(9,999,999.99)” gets you negatives in parenthesis. “-9,999,999.99” will produce negatives with a dash/minus sign. And all numbers will have two decimals and commas.

Simple as that. Now you can read your numbers without having to think too much.

Using Notepad++ to Search Projects

When you need to search across multiple scripts in an ACL project, the process of using Find/Replace can get tiresome. Because ACL projects are just text files, they can be opened up in a program like Notepad++. Notepad++ has some great search features, which can track down the variables or tables you’re looking for. The table name will appear next to the ^BATCH notation. You will also see at the top of the ACL file the table layouts. I wouldn’t mess with this stuff, but if you’re feeling adventurous, go for it!

As your scripts grow more modular, this should help ease the fear of finding that one variable that keeps wrecking everything. Good luck!

Beware Excel Cell Formats

Everyone knows them: General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, Fraction, Scientific, Text. The wonderful Excel cell format options. Did you know this can have unintended consequences on your Excel import?

I ran into an issue a few weeks ago where the account number looked something like 1000-00-040. It looked like that in Excel, but not in ACL. I’ll admit, I was a bit confused at first. The Data Definition Wizard is just supposed to work, right? Wrong. Excel pulled a fast one. The client’s system was creating a customer format that took a number and added the dashes in. Because we join back to the trial balance, I needed the format to be in ACL as well. So I did what anyone else would do, import as a character, then hack it back together with a few SUBSTR()'s and "-".

It wasn’t pretty, but it got the job done. Bottom line, beware the Excel cell formats!