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