Welcome to the newest post in the series for ‘Audit Command Language Tutorial for Beginners’. As a follow up to the previous post, we are going to explore how to read in the data files provided in an excel format. The concept to import excel files is pretty much the same, but there are a few points that should be kept in mind. This post should do a good job of highlighting said points.
Housekeeping: Before we go through the process of importing an excel file in ACL, lets review a few problem points that should be addressed even before attempting to import and excel file.
- Any raw files required for data analytics are best if they haven’t been formatted so that no data is lost. Excel files have a classic problem of ‘self formatting’ data files. Therefore, before importing excel files, it is imperative that all columns are formatted appropriately.
- You can choose the formatting options in the ‘Home’ tab in the ribbon. It generally would have default as ‘General’. Select the appropriate format from the drop down.
- As general guideline, choose format as ‘Number’ with appropriate decimals for any amount fields only. Any other fields, always choose ‘Text’. This avoid any other fields like IDs which start with ‘0’ to keep all characters values.
- Once the excel file is appropriately formatted with these two formats, it is ready to be imported.
You can follow the below steps to import the excel files into an ACL project.
- Follow the steps to through to the window where you are required to select the type of the source file and choose ‘Excel File’. For earlier steps read post for ACL Data Import – Delimited Files.
- On the next screen, select the appropriate sheet in the excel file to import data. At this point you may choose define the data types for columns based on first 100 rows if you choose to and allow for first row values to be taken as field names. It is advisable to do so as excel files take longer to import in the ACL.
- The next screen presents the chance to edit the data types and names of the columns. At this stage, you may ignore any fields. Please note, for excel files, choose the longest possible length for numeric values so that the amounts are truncated.
- On the next step, save the table in the ACL folder of the project directory and then verify all the columns are appropriately mapped against the expected data types.
- Save the imported table in the ACL project with the appropriate name and you’re done.
You can look through the video demo for importing excel files below:
Similar steps are followed for MS Access files or SQL tables. However, more often than not, analytics projects receive data in the delimited text or excel files as these are the easiest formats to export data in as well for any firms.
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.