University Of Bahrain
College of Information Technology
Department of Information Systems
IT Auditing
IT IS 444 / IT BIS 445
Tutorial 2
Tutorial Objectives
By the end of this tutorial you should;
- Be able to create a new project
- Be able to import data into your project from various sources
- Be able to verify data imported before your analysis
Exercise 1 – Create a New Project
ACL acts as a single repository for data from several sources to enable you to conduct your analysis more
accurately and consistently. When you create your new project, it will be empty with no data, and will prompt
you to add data from various sources. To begin with, launch ACL.
1. Simply select File -> New -> Project.
2. Name the file: ACL_Tutorial_Metaphor -> click Save.
The Data Definition Wizard will appear; this will be explained in Exercise 2.
Exercise 2 – Importing Data into your Project
As you have created your new project, and now you should be at the Data Definition Wizard.
The Data Definition Wizard is the main tool that we use to import and define data with ACL. It is extremely
helpful and can adapt to the different type of data sources that you will be using to import data; such as xls,
csv, text files and more. In this exercise, we will import the following six files into the project:
1. Credit_Cards_Metaphor.xls & Trans_April.xls
2. Unacceptable_Codes.txt & Company_Departments.txt
3. [Link]
4. Acceptable_Codes.mdb
Exercise 2.1 – Using the Data Definition Wizard (Excel Files)
To begin with, since the Data Definition Wizard is already appearing, we may go ahead and add our
first data source; Credit_Cards_Metaphor.xls.
1. On the Data Definition Wizard -> click next until the Select File to Define dialog box appears ->
browse to the Sample Data Files folder -> select Credit_Cards_Metaphor.xls -> click Next until
the Data Source screen should appear.
Note: ACL can detect the type of file.
2. With the Data Source screen -> ensure you choose the correct location of your project file ->
enter the following Name: Credit_Cards_Metaphor -> click Save -> click Finish. Save the table as
the name appears.
Note: If an excel sheet has more than one sheet, you select the worksheet in the Data Source
dialog box. Data Sources in ACL are saved as .FIL extensions.
To get to the Data Definition Wizard during any time in your project, you may do so by going to File ->
New -> Table. And from there we will add our next table; Trans_April.xls. You may do so by repeating the
previous steps, however locate the Trans_April.xls and name the table Trans_April.
1
Prepared By: Ahmed Ramadan
University Of Bahrain
College of Information Technology
Department of Information Systems
IT Auditing
IT IS 444 / IT BIS 445
Exercise 2.2 – Using the Data Definition Wizard (Text Files)
Working with text files can vary between each file, as there are several ways that people may store data
in text files; i.e. with or without column headings, with or without tab delimited, and etc. With ACL, importing
data from different sources is made easy, however you must understand the nature of the data to ensure
proper import and ensure proper data extraction.
Now, we will import Unaccpetable_Codes.txt, which has two columns titled Codes and Descriptions
and its fields are separated by tab characters (Tab Delimited).
1. Select File -> New -> Table. Keep clicking next until the Select File to Define -> locate and select
Unacceptable_Codes.txt -> click next until you reach the File Format screen -> select Delimited
Text File and then click next.
2. The Delimited File Properties will appear, set the following:
a. TAB as the Field Separator
b. Use first row as Field Names
3. Save the data source as Unacceptable_Codes -> the Edit Field Properties will appear, set the
following:
a. For the fields Codes & Description set the Type: ASCII Text
4. Keep going next until you Finish and add the table.
Now, we will import Company_Departments.txt, which does not have a header row, we will have to set
them ourselves.
1. Repeat the previous steps (Company_Departments.txt) until you reach the File Format screen ->
select Delimited Text File and proceed to the Delimited File Properties screen and set the
following:
a. Deselect the first row as Field Names
2. Proceed and save the data source as Company_Dept
3. In the Edit Field Properties, set the following:
a. Field 1 – Name: Dept_Name, Type: ASCII Text
b. Field 2 – Name: Dept_Code, Type: ASCII Text
4. Finish and save the table in ACL.
Exercise 2.3 – Using the Data Definition Wizard (CSV Files)
Now, we will import [Link]. Unlike others, this will be a different process.
1. Select Date -> External Data -> Disk. The Select File to Define will appear -> locate and select
[Link] -> this will open the DDW -> ensure you are importing it as Delimited Text File ->
and set the following properties; Comma as the Field Separator and use the first row as field name.
Proceed and save the table as Employees.
2. In the Edit Field Properties:
a. Set the data type for CardNum and EmpNo as ASCII Text
b. Set HireDate as Date and specify the format (otherwise data will be lost).
c. Ensure Salary and Bonus_2002 are Numeric (Formatted).
3. Proceed and save the table.
Exercise 2.4 – Using the Data Definition Wizard (Access Database Files)
Now, we will import Acceptable_Codes.mdb. Unlike others, this will be a different process.
1. Select File -> New -> Table. Proceed as you have learned before, locate and select the correct file.
Keep proceeding and notice since this database only has one table, it will be selected automatically in
the Select the Access Table/View. Proceed and save the data source and table as Acceptable_Codes.
2
Prepared By: Ahmed Ramadan
University Of Bahrain
College of Information Technology
Department of Information Systems
IT Auditing
IT IS 444 / IT BIS 445
Exercise 3 – Verifying Imported Data
Before beginning your analysis, it is important to check and verify the data you have imported or else your
analysis may provide you with incorrect or useless results. ACL provides several commands that help us
accomplish this easily.
Exercise 3.1 – Verify Data Types and Formats
It is critical to ensure that all data types for each field are correct and fields have been defined properly. The
way data appears is only a preference. For example, ensure data fields such as EmpNo as ASCII and not
numeric as we will not be using this field for calculations. ACL can be used to fix incorrectly formatted data by
creating computed fields and more.
1. Open the table Credit_Cards_Metaphor -> go to Edit -> Table Layout (Ctrl+I) -> Notice any
adjustments to be made
2. Double click on CardNum -> change its data type to ASCII -> save your changes and close.
Exercise 3.2 – Verify Data in the Table
As critical as it is to verify data types and formats, it’s even more important to ensure that the data collected
is as accurate as possible. ACL helps us achieve this using the Verify Command.
The Verify command checks whether the data in the table matches its data type and indicates data validity
errors. Before beginning your analysis you must ensure that you have zero data validity errors.
1. Open the table Credit_Cards_Metaphor -> go to Data -> Verify -> click Verify Fields and in the
Selected Fields dialog -> select all fields (Add All) -> click OK -> OK
2. Examine the result.
Exercise 4 – Documenting your Work
You can save notes to your project, table and to specific records.
Add the following note “This employee is leaving Metaphor Corporation by the end of the year” to
employee at record 3.
3
Prepared By: Ahmed Ramadan