Audit Command Language – Data Import

Welcome to a new post in the series for ‘Audit Command Language Tutorial for Beginners’. In the next couple of posts, we will be covering various ways to import raw data files into an ACL project. Let’s start with something rather straightforward, i.e. delimited files.

As discussed in a previous post, delimited files are generally in a text format, with characters such as a comma, colon, ‘~’ etc. separating the columns in the data file as below:

You can follow the next steps to import a delimited file into ACL:

  1. Go to ‘File’ -> ‘New’ – > ‘Table’
    • step-1
  2. Select ‘Local’
    • step-2
  3. Select ‘Disk’
    • step-3
  4. Browse sample data file from data folder of your project
    • step-5
  5. On the next screen, select the second option
    • step-6
  6. Choose ‘Delimited Text File’ from the list file formats
    • step-7
  7. The next step, select the following:
    • Set top row as field names
    • enter the delimiter (‘|’ or comma or colon etc.)
    • Select the appropriate text qualifier
    • then choose the appropriate length for each field
  8. Save file in project folder in the ‘ACL’ folder
    • step-9
  9. The next window is the section to edit the field properties:
    • Select the type
    • rename the data field if required
    • You may also choose to ignore the field
  10. Review the fields imported with selected ACL data types in the next screen
  11. Enter the name for the table as appropriate
    • step-12

This step by step guide is generally applicable for almost every type of data file that you might encounter. A couple of the above windows might look different depending on the type of inputs files. For instance, with Excel files, you would encounter a window to select the tab in the sheet, which is to be imported.

It is our sincere hope that this post would help you to get started with your projects. Importing any data file is more than half the battle won when working on data analytics projects in the Audit Command Language.

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go to the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.

 

Audit Command Language – ACL Help


Welcome to ‘Audit Command Language Tutorial for Beginners’ data analyst. In this post, we are going to cover an important aspect of learning and using ACL in real time and what kind of resources do you have at your disposal.

In today’s age ACL Audit Command Language has not seen the traction that most other tools seem to have on the internet. There is virtually no discussion thread on any problems related to ACL. even though this tool is still quite extensively used in many organizations, especially firms involved in audit related projects.

Here are the best and most common resources/tips that would help you to successfully complete your ACL Audit Command Language projects and by extension, your analysis.

  1. ACL Help: Most of us, never rely on the built-in help that comes with most tools. In this case, however, it is not really an option. The Audit Command Language help, s the most important resource you can refer to for any doubts. The help is accessible through search on google and in the tool itself. They appear in the same format mostly. The help would cover all topics of course, from functions to scripting. However, it is not very detailed in most cases.
  2. Get hold of sample Script: Assuming you are a beginner to Audit Command Language, it is highly recommended that you get a hold of any templates or past projects from your colleagues to understand coding techniques most commonly used. Make notes of most commonly used scripts used for creating fields, tables, joins, relations, script structures etc. This way, you can start scripting your projects end to end even without the advanced concepts, which can be learnt over time.
  3. Colleagues:  Make friends with colleagues, who have experience in ACL. Experienced ACL folks, just like any other experts tend to be smug but this is rarely preciously knowledge to acquire.

It is our sincere hope that this site becomes another source for concerns regarding ACL Audit Command Language so that we can learn more and help others who are trying to learn.

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




Audit Command Language – Data Types

Welcome to the latest post in the series of ‘Audit Command Language Tutorial for Beginners’. Before we discuss the tool in more detail, let us cover a few basic concepts in the next few posts. In this post, let us discuss data types.

There are many data types in the ACL just like any other tool. However, these can be covered in three main categories i.e. character (ASCII), numeric (Float, Numeric) and dates. See the ACL Help.

Also, check out the post on this site for the Audit Command Language Help.

Data types define the nature of the data in any specific column being imported into a table. For instance, a column such as ‘Order_Date’ would have date values, would be imported as ‘DateTime’. See Example

Data Import Screen in ACL

Audit Command Language Data Types
Data Types in Audit Command Language

Similarly, while importing a numeric field, try to ensure that the maximum length is considered to avoid truncated values. Pay special attention to the decimal points for numeric values. Typical values for numeric data types would like ‘Sales’, ‘Units Sold’ etc. Similarly, watch out for columns with descriptive values like ‘Names’, ‘Store Name’, ‘Customer Name’ etc. for character values.

Avoid Data Issues

Very often, users rush to import data into an Audit Command Language project without carefully looking at the raw data. The below tips could be useful for avoiding such issues.

  • Study the raw data files carefully to identify most data types.
  • Make quick notes of the data structure and identify possible relations between data points.
  • All data fields should be imported as character values from the raw data files. These values may be transformed to required formats during the execution of the Audit Command Language scripts.

For those interested in Audit Command Language certification, please refer to reference materials provided alongside the posts. These are helpful study materials to prepare for various certification exams. Also, please sign up for our newsletter to stay up to date with the latest activity on the site.

Audit Command Language – How to set up ACL project


How an Audit Command Language project is set up can improve the efficiency of the overall workflow. It is safe to say that there is definitely no clearly defined way to set up your project, but there are nonetheless a few guidelines you may want to follow. Follow the procedures below:

Set Up Project Folder 

Audit Command Language is quite peculiar in the way it creates its own set of files with their own set of extensions like ‘.fil’, ‘.lix’ etc. It can be quite confusing to have them in the same folder as the raw data files or any other output files that you might create. As general rule, create your project in a directory as shown in the screenshot

folder-structure

In data folder, place your raw data files. The points any files being exported to the ‘Output’ folder. Create your ACL Audit Command Language project in the ACL folder. See screenshot.

Creating a new project in Audit Command Language Software
Open new project in Audit Command Languge

project-set-up-path

Naming the Project

Name the project which best describes your nature of work, with the name including the date. For example, ‘Customer Analytics – Instore Evaludations 01012016 through 06302016′. This format of naming helps to identify the project clearly and is carried over all the way through the log files generated in “Audit Command Language”.

Create a Standard Directory Structure for all Projects

Always create a directory structure dedicated for all Audit Command Language projects. This practice enables users to better manage the project work. This is categorically true for young professionals. Those going through ACL training may want to adopt this best practice as early as possible.

For those interested in Audit Command Language certification, please refer to reference materials provided alongside the posts. These are helpful study materials to prepare for various certification exams. Also, please sign up for our newsletter to stay up to date with the latest activity on the site.

 

Audit Command Language – Introduction


The Audit Command Language is a GUI tool used primarily for data analytics in the field of IT audits and risk assessment. This tool is one of the first tool that most beginner data analytics professionals would come across. This series of  “Audit Command Language Tutorial” is aimed at such young professionals. Listed below are some of the topics, which are a foundation for starting with Audit Command Language. This post covers, the core concepts used in the ACL tool.

Data Import

The first step would be to understand the various methods for importing data into the tool. Audit Command Language is a GUI based tool. This makes it easy to start off with loading your data for your projects. Most commonly used files are delimited files (pipe, comma separated, etc.) with a text qualifier such as double quotes.

  • Delimited files are files, which have data columns splits based on a certain recurring character.
  • Such files may allow for any character to act as the ‘delimiter’ to separate the fields in a data file. It is always preferred if the character is uncommon. For instance, ‘~’ character would be preferred over a comma.
  • It is helpful, if delimited files are exported with text qualifiers for ‘character type’ fields. This mitigates any data issues that may arise due to special characters present in fields like names.
  • Text qualifier covers the entire width of the data column. Please see screenshot below.
Text for input into Audit Command Language tool
Text for input into Audit Command Language tool

Data Types

This is where Audit Command Language, is simpler to most other tools. There are broadly three kinds of data types. These are numeric (amount fields), Text (names, descriptions etc.) and DateTime (dates). The screenshot shows these three data types. The purpose of the audit command language software is to work on risk assessment and audit projects. With this context, these data types serve the purpose. Please note, there are many different data types supported, which would fall under one of these data types. These may be used as appropriate for the raw data being imported into the tool.

Functions

Another extremely important feature for a data analytics software is ‘Functions’. There are predefined functions in Audit Command Language, which allows for data manipulations/cleansing. Audit Command Language Functions are categorized, based on the data types. There are three main categories of functions. These are character, numeric and date functions. There is another type of functions, which is not used very often. These are the Boolean functions. As the name suggests, these are designed to return ‘True” or “False” values.

Joins

All data analytics professionals, would swear on this concept. Joins are the bread and butter for a data analytics professional since this the most commonly used operations. Joins are the combining of two or more data sets based on a common field (or key). Joining data sets is a frequent requirement. Any data analytics professional would find themselves helpless without it. This holds true  in today’s environment, where BIG DATA is a frequent buzz word.

Merge/Append

Just like ‘Joins’, append/merging data sets is an important and regularly required procedure in data analytics projects. This procedure is used to combine data sets vertically i.e. a table with same fields (with same widths and data types) are joined together so the total records are the sum of all the tables being combined. Audit Command language has simple GUI solution to such complex tasks.

Going forward, the above concepts would be discussed along with Audit Command Language scripts and workspaces. This would be the form of instruction on this website to cover each concept. Please refer to reference materials provided alongside the posts. These are helpful study materials to prepare for Audit Command Language certification exams. Please sign up for our newsletter to stay up to date with the latest activity on the site.