ACL AN 13 – Advances in Excel Import and APPEND Command

The latest version of ACL Analytics (AN 13), released back in November last year (with a minor patch a month later), contains several new and enhanced features. In this article I am going to look at two of them:

  • Excel import – enhanced to include multiple worksheets simultaneously
  • New APPEND command with ability to combine multiple ACL tables simultaneously

Excel Import

My starting point is an Excel file with four worksheets containing inventory data, each worksheet relating to a different location. Those of you who remember your ACL 101 training might recall the locations: Boston; Chicago; Denver; and San Francisco.

Out of interest, I imported the data into Excel using the [ODBC] ACL Connector for Data Analytics and Microsoft’s PowerQuery functionality, now standard in Excel under Data: Get and Transform.

PowerQuery has the capability of applying significant data transformations and in a repeatable way. However, this was not necessary because the data was coming from a ‘structured’ source (ie, ACL).








Using the Data Definition Wizard, I selected the Excel file containing the four data-filled worksheets, which presented me with the following screen:

There are two choices for each worksheet:

– the first is the sheet name followed by $ and this represents the whole sheet;
– the second contains additional detail and represents a table or named range within the sheet.

Since the data I’m after is the only data in each sheet, and it always begins in cell A1, I just select the ‘whole sheet’ option for each worksheet. Leaving all other options as they are, just click ‘Next’.

The next screen simply confirms the choices I made in the previous screen. I click ‘Finish’ and the four worksheets are imported into ACL.


Note that because I am importing multiple worksheets at once I don’t get the chance to adjust any individual field settings. ACL will import all fields present in the data and use default data definitions based on the number of records scanned – ie, the first 100 or the entire range.

Hey presto, I’ve now got my four tables imported into ACL in one go.

From a scripting point of view, there are no new commands, parameters or other syntax generated in the log as a result of importing multiple sheets. ACL simply records a separate import command for each worksheet.

APPEND command

Now that I’ve got my data imported to ACL, I want to combine it into a single table. For this, I will use the new APPEND command.

The APPEND command is located under the ‘Data’ menu, and you don’t even need to have a table already open! The APPEND command dialog lists all tables in the project. Just ‘double-click’ each table that you want to append. You also get the choice to only include common fields across all selected tables.

The tables that I have selected do not all have the same structure. Each field has the same data type across all tables but the field lengths differ – this happened during the Excel import when default data definitions were used. ACL copes with this quite easily but I do get this message warning me that some data harmonisation is required.

And the end result is a new ACL table containing all the inventory records from the four separate tables.

The APPEND command looks set to be very useful and is certainly easy to use, but keep the following points in mind:

  • Fields with the same name must also have the same data category – these are ‘common fields’
  • Fields can be in any order, the field order from the first specified table is used in the output table
  • Common fields may be harmonized automatically by ACL to match field length, no. of decimal places, date formats etc
  • Fields not common to all tables may be included (with blank or default values for tables where no such fields exist)
  • There is an option to include only common fields
  • Computed fields are ignored
  • Fields with the same name but different data categories will cause an error and the command will fail.

Wednesday, March 7, 2018 In: Hot Topics, Newsletters Comments (None)