ACL Certified Data Analyst

How to import Excel source data into ACL Analytics manually and by scripting

In last month’s edition, we showed you how to perform some very basic analysis, such as Quick Sort and Summarize, on your data. But before you can analyze your data in ACL Analytics, you have to get the data into ACL first. In this edition, we will cover how to define and import your data into ACL manually and by scripting, using an Excel file as an example.

First and foremost, how do I get my source data into ACL Analytics?

Great question! You must first create an ACL table to contain the data. Here are the four basic steps to create an ACL table:

  1. Navigate or connect
    • Navigate to a source data file, or connect to a file or a database containing the source data.
  2. Define the source data
    • Specify information about the structure and characteristics of the source data so that ACL can read it. ACL sometimes automatically define some source data so user definition may not be needed.
  3. Import or read directly
    • Import the source data into a native ACL data file, or read the data directly from the source without creating an ACL data file.
  4. Name and save the ACL table
    • Name and save the automatically created ACL table.

How do I define and import data?

ACL has two components for defining data, importing data or reading data directly from the source, and creating an ACL table:

  • Data Definition Wizard – a page-based wizard that provides a standard way to access a variety of data sources, mostly file-based. The basic process for defining and importing data using this wizard is consistent, but the selection and sequence of pages presented depends on the the type of data source you are using.
  • Data Access Window – a visual interface that contains a number of data connectors you can use to access source data in either databases or files. The data connectors use either native ACL ODBC drivers, or whatever Windows ODBC drivers you have installed.

Which of the above two components should I use for defining and importing data?

Your data source will dictate which method you must use to define and import into ACL. For some data sources, you can use either the Data Definition Wizard or Data Access Window. For a list of all the data sources you can access with ACL, and which component you must use to access them, check out our Help Docs here >>

Here is a short comparison on the different options available with the two different components:

Option Data Definition Wizard Data Access Window
Select tables Yes Yes
Search tables No Yes
Select fields Depends on the data source Yes
Import multiple tables No Yes (up to 5)
Join tables No Yes
Filter data No Yes
Preview data import Yes (basic) Yes (modern interface, easily refreshable)
Estimate data import size No Yes
Specify field length Yes Yes
Rename fields Depends on the data source Yes (in SQL Mode)
Change field data type Depends on the data source No (data type can be changed after import)

How to define and import an Excel spreadsheet into ACL Analytics?

Now that we have a better understanding of how to define and import data and which component to use, let’s try to define and import an Excel file together. This example is taken from “Chapter 3 – Importing Data” in the ACL Analytics Foundations course in ACL Academy. If you wish to follow along, make sure you download the training data files under the “Files” drop-down menu on the top right within the course in ACL Academy.

In this example, we will be using the Data Definition Wizard (DDW) to import our Excel file into ACL Analytics.

  1. Launch ACL for Windows.
  2. In the ACL for Windows launcher, click on the green “Analytic Project” button under the “Create” section.
  3. Create an ACL project called DataImport.
  4. Select File > New > Table. The Data Definition Wizard window will pop open. Within the DDW, you will see a list of steps that you need to go through on the left before you can complete the import.
  5. Select Platform for data source
    1. The DDW identifies Local (default).
    2. Click Next.
  6. Select Data
    1. Select File (default) to open the Excel file saved on your computer.
    2. Click Next. Your Windows folder opens.
  7. Select the ProductClass.xls file and click Open. You will return to the DDW window.
  8. Identify Properties
    1. The DDW will correctly identify this as an Excel file. Click Next.
  9. Define Fields/Records
    1. Select ProductClass$ since this is the Excel worksheet while ProductClass is a named range.
    2. Select Use first row as Field Names.
    3. Select Entire Excel Worksheet or Named Range as how the field lengths and types will be detected.
    4. Click Next.
  10. Excel Import – Preview Data
    1. Review the three fields (you can change the Data Type if incorrectly identified) and click Next.
  11. Save Data File As – An Excel file will be written to a fixed-width file with a FIL extension
    1. For the created fixed-width file, enter ProductClass as the name.
    2. Click Save.
  12. Final
    1. Ensure field names and types are accurate. To make any changes, click Back.
    2. Click Finish.
  13. Enter ProductClass as the table name and click OK.

Congratulations! You have successfully imported your Excel spreadsheet into ACL Analytics. It wasn’t too hard, right?

What if I have the same Excel file with different monthly data to import into ACL for repetitive analysis? Is there a faster way than to go through those steps every time?

Yes! You can rely on the power of an ACL script to help you import your Excel file with just one click of a button. An ACL script is a series of ACLScript commands that performs a particular task, or several related tasks, which can be saved so it can be executed repeatedly and automatically.

Let’s expand on this using the example above.

  1. In the Navigator on the left, click Log to open the ACL command log – you will see a record of every command executed.
  2. Select the box next to the words “IMPORT EXCEL TO…” > Right-click on same line > Save Selected Items > Script...
  3. Save Script As
    1. Enter ProductClass_import_script as the name.
    2. Click OK.
  4. Back in the Overview tab in the Navigator, you will see the newly saved ACL script under the ProductClass table.
    1. Double click on the script to open it in the ACL Script Editor – you can see the paths where ACL is pulling the Excel file and where the fixed-width file was saved on your computer. These paths are known as absolute paths and they point to the same location in a file system and must include the root directory.
    2. You can shorten the script by changing the path from an absolute to a relative path, a path that starts from some given working director. In this case, a file name can be considered as a relative path.
    • After you have shorten paths in the script, before running the script, make sure the source data file and the ACL project file are saved in the same folder! If the files are not in the same folder, you will get the following error message.
      cannot import error ACL Analytics
  5. Delete the root directory and leave the file name for both the fixed-width file and Excel file within the script.
  6. In the next line, enter OPEN ProductClass to instruct ACL to open the table after the import.
  7. Click the play button to run the revised script.

Give this scripting method a try! This method will definitely help you save some time when importing data repeatedly!

Frequently Asked Questions

Here are some Frequently Asked Questions for importing data into ACL Analytics:

1. I was trying to import a PDF file and received an error message “Failed to import PDF no recognizable characters found”. How can I resolve this?

  • You may have a scanned PDF file. Many printers and fax machines are able to scan documents and email them. This process converts the text in the report to images.
  • You can either ask for the report to be resent in its original PDF format (not scanned) or if the information is not confidential, you can use an online tool to convert the images back to text (www.onlineocr.net) or you can purchase a software that can convert images to text.
  • Once the file has been converted or restored into text format, you can import it into ACL again. The error message should no longer appear.

2. How do I import Salesforce data into ACL Analytics?

  • Within the project you wish to import the data in ACL Analytics:
    • Go to File > New > Table
    • Select Platform “Local” and click “Next
    • Select “Database and Application” and click “Next
    • OR go to Import > Database and Application
    • Next, select “New Connections” tab and then “Salesforce“.
    • After this step, there is a list of fields you need to complete.
      • If you are accessing the Salesforce data from a trusted IP address, then you are only required to enter your username and password.
      • If the data is not from a trusted IP address you will need to generate a security token through your Salesforce CRM.
        • Log into your Salesforce account, select the settings cog i the top right to get to the Personal Setup screen.
        • Select “Reset your security token” option under “My Personal Information
        • Select “Reset Security Token” and a new security token will be sent to the email address specified
        • Fill the new security token along with your username and password to make the connection in ACL Analytics
    • Next, you are given a list of possible tables to import under the “Available Tables” on the left.
    • Select the required table and select “Refresh” at the lower right to get an import preview.
    • Select “Save” to enter the name of the fixed-width file you are creating.

3. What are several common types of data files that I can access with ACL?

  • dBASE Compatible files
  • Delimited Text files
  • Print Image (Report) files
  • Microsoft Excel/Access files
  • XML files
  • PDF files
  • For a list of all the data sources you can access with ACL, and which component you must use to access them, check out our Help Docs here >>

Tip of the Month

ACL can import Excel files from version Excel 3.0 to Excel 2016. To import a file from an earlier version of Excel, you have to convert the file in another file format that ACL can define. You can define an Excel file even if you do not have Microsoft Excel installed on your computer. However, ACL does not support directly defining Excel files created from web applications such as Google Sheets. You must first open the file in Excel, save it under a different file name, and then import the new file using ACL.

Learn more best practices and how to make the most out of your ACL technology at ACL Connections, September 24-27 in Nashville, TN. This event features four days of training and offers 21.5 CPE credits. Access the full event details here >>

ACDA Bootcamp Series News

Interested to see a live demo on how to import data into ACL Analytics manually and by scripting? Come join our ACL expert, Ruben Rivero, for our ACDA Bootcamp webinar on Thursday, July 20th! Register here >>

Did you miss previous editions of the newsletter series? Don’t worry, just visit here to check out the past editions.

In the next ACDA Bootcamp newsletter, we will take a closer look at the basics of Datetime fields.

Subscribe to receive the Bootcamp Series sent directly to your inbox!