ACL tip: Create a File Import Script

Did you know that you can create a script to import a file into ACL? That you can automate loading a table?

I’m talking about the File > New > Table command in ACL, also known as the Data Definition Wizard. Yes, you can create such a script, and I’m going to teach you how!

The good news is that it’s so much easier than you think. The bad news is that it doesn’t APPEAR easy, but it really is, because ACL does the heavy lifting for you. I promise that if you hang in there, you’ll so be a pro. Just try it once, and you’ll be hooked!

One other thing: this is a long post, so get your favorite drink, and relax. I’d also suggest that you print it and read through it before attempting this at home.

Best of all, if you’re not satisfied, you can blast me in the Comments, and I’ll return all the money you’ve invested so far!

In this post, I’ll cover the following:

• Why Create a File Import Script?
• How to Create a File Import Script
• Understanding the IMPORT Script
• Fixing Errors, Editing the Script
• Editing Example

Why Create a File Import Script?

Here’s some reasons to write a script for file imports:

1. You find yourself importing a file multiple times. It happens all the time: you get a bad file from IT, or when you load a file, you define a field in the wrong format. Or when you download data from a system, you forget to include a couple fields. So you go through the process of loading the file and defining all 20+ fields again, which is frustrating and time consuming. Especially when you have a file with many fields and records.
2. You need to load several files from different time periods. Sometimes the system from which you are extracting data imposes limits on the amount of data you can export at one time, so you have to download chunks of data separately (e.g., by quarter). As a result, you have multiple files with identical fields that have to be loaded one by one.
3. You perform the same ACL audit on an ongoing basis (e.g., weekly, monthly, annually). The source files seldom change (although once you script the import, even source file changes like extra fields are easily managed).
4. You want spend your time analyzing data, not loading it. In other words, using a script to load files is so much faster! Depending on the file you’re loading, you can save 10-30+ minutes or more, per file.
5. You want to automate the entire ACL project so that it performs continuous auditing; ACL starts, goes and retrieves the file, imports it, analyzes it, and dumps the reports on a shared drive, or emails it to you. Before you can automate a project, you have to automate the file import.

How to Create an File Import Script

For this example, assume the following:

• The file you want to import into ACL is an Excel spreadsheet called Servers.xls.
• The file contains 1000 records consisting of the following 3 fields: ServerName, Location, and Install_Date.
• You don’t want to load the second field (Location) into your table, as it is not necessary for your analysis.
• The ACL project name is called Project2.
• You want to load the file into an ACL table called ServerInventory.

Follow these steps:

If your file contains few records, you can skip steps 1 & 2 and load the file as normal and start with step 4 (also skip step 8). The procedure below is for large files, which is what I usually deal with.

1. Create a copy of your source file, and give it a different name. For example, if the source file is Servers.xls, name the copied file Servers-copy.xls. Save the copied file in the same folder as the source file.

2. In the copied file (Servers-copy.xls), select about 25 records, and delete the rest. Make sure the records you keep are representative records of the overall file.

In other words, keep records with a) the least amount of blank fields and b) the longest field lengths. For example, in the ServerName field, don’t select records in which the server names are Mail and App1; instead, select records with longer server names like Server1SanFransisco and StarWarsDarthMaul33. Otherwise, some of your server names may be truncated to Serv and StarW. Bad field lengths could really present a problem is your field is a dollar field, and you import $100 instead of $100,000 (it’s hard to identify fraud if you do that).

If the file is too big to open and easily edit, split the file into smaller pieces, and import one of the pieces instead. I explain how in Free File-Splitter Program.

3. Import the copied file (Servers-copy.xls) into ACL and define the fields as normal.

While technically this is the same thing you normally do, it will go faster than importing the entire file; since you’re only importing 25 fields, it will go quickly. When you import a file, ACL creates a detailed log entry that used in the next step. Stay with me.

4. After the table is loaded, go to the ACL log, and copy the last IMPORT command into a new script as follows (see screenshot below – click to enlarge):

a. Double-click on the ACL log (shown as Project 2 in the Project Navigator area).
b. Locate the IMPORT command at the bottom of the log, and click the box in front of the command so that an X appears in the box (if you don’t click the box, the Copy command is not available).
c. Right-click the IMPORT command, and select Copy.

5. Create a new script by right-clicking the ACL project name and selecting New > Script (see below).

6. Paste the IMPORT command from step 4 into the new script by right-clicking in the box on the lower right and clicking Paste (see below).

7. Type the commands SET SAFETY OFF and SET SAFETY ON commands before and after the IMPORT command as shown below (to insert a blank line, position the cursor and press Enter). Either all caps or lowercase works.

Normally, when you do something in ACL that will result in a file or table being overwritten, ACL will prompt you as to whether you really want to do that. The SET SAFETY OFF command suppresses those prompts and the script quietly overwrites the files; the SET SAFETY ON command turns those prompts back on at the end of the script.

If you turn the safety off in a script and don’t turn it back on, you could then overwrite something important later when you are using ACL’s menu commands. The safety stays off until you turn it back on.

8. Change the file name in the script from your copied file (e.g., Server-copy.xls) to your original source file (Server.xls) as shown below. Be careful that in doing so, you don’t add or delete characters elsewhere in the script.

Remember, to view a larger screenshot, just click it. Click ‘Back” to return to post.

9. Run the script by right-clicking the script and selecting Run. When asked to save the changes to the new script, click Yes. The script will run and load your original source file.

10. Check your reloaded table (e.g., ServerInventory) and note that all 1000 records of your source file were loaded. Notice how fast it was!

Understanding the IMPORT Script

It’s important to understand the different sections of the import script so that you can edit it. As you become more adept at editing these scripts, the more time you will save. More on this later.

Right now, let’s look at the details of the import script that I created for this post. Each section of the script is described in the Key below the image.

The IMPORT command is all one command that wraps around the screen in one continous stream. It cannot be broken up into seperate lines as shown below, which I did to make it easier to document and understand.

KEY

A. IMPORT command (never changes)

B. Type of file being imported = EXCEL (unique to the file type being imported)

C. Name of table to be created

D. Path where resulting table file (.fil) will be saved

E. Path of file being imported (if the file is in the ACL project directory, the full path is not shown, just the file name).

F. Excel worksheet or tab name that will be imported (unique to Excel).

G. Command that uses the field headings in the file as the fields headings in the table (if “Use first row as Field Names” is selected during import)

H. Name of first field

I. Format of first field; C = Character (text)

J. Width of first field = 19

K. Display name; how the name of the first field will be displayed; AS “ “ is the default, which means display the field name as shown in H

L. Name of third field (see Q for more info)

M. Format of third field; D = Date

N. Width of third field = 19

O. Date format of third field = year-month-day

P.  Display name of the third field (same as shown in L)

Q. Second field is being ignored and will not be loaded into table (not imported). The “2” means the second field. ACL always lists the ignored fields at the end of the script.

Fixing Errors, Editing the Script

Now that you’ve learned how to create an import script and the different sections in such a script, here’s some pointers regarding how to troubleshoot script errors and edit the script.

• Before running your new script, remember to change the name of the file to be imported from your copied file to your original source file. This is the most common error that occurs. Even in my projects. :)
• Likewise, pay religious attention to table names and file paths in the script. The second most common error is that the script is trying to load the wrong file, create or write to the wrong table, or that the file isn’t in the path in which the script is looking.
• When importing Excel files, make sure the name of the worksheet (tab) in the script (see F in the Key above) matches the name of the tab in the worksheet.
• Your import script has to have the same number of fields in it as your source file does. If the source file has 4 fields (even if you intend to ignore some of the fields), the script has to contain 4 fields. If your script doesn’t work, this is a key item to check.
• To edit the script in ACL, just open it (double click the script name). To insert a new line, position the cursor where you want the new line, and press ENTER. Always make a copy of the script and edit the copy, especially when the script is working. That will save you from having to recreate the script from scratch if you mess up.
• To change the name of the script, close the script first. Then right click the script name, and select “Rename”.
• If you run the script and nothing happens (no table is displayed), go to the ACL log and look the last IMPORT command; it will have a red X in front of it (see below). Double-click the log entry for more information on the error.

When you double-click that IMPORT command, the error will be displayed (see below):

“Columns” means fields. Which field is missing? Field 2, which was “IGNORE 2” in the script example (see “Q” in the graphic in the Understanding the IMPORT Script topic above). Edit the script to fix the error, and run the script again.

Editing Example: Loading 3 Similar Files

When you need to load files that are similar, editing import scripts can save you even more time.

For example, I had 3 files to import that are very similar. Two files (File1 & File2) had the same 8 fields in it, and the third file (File3) had the first 7 fields that the other two files had, not the 8th field, but 1 other field. The letters (ABC) below refer to the parts of the script described above in the Understanding the IMPORT Script topic

First, I loaded File1 according to steps 1-10 above to create Table1 & Script1 with 8 fields in it.

Second, to create Table2 & Script2, I did the following:

• Made a copy of Script1 and renamed it to Script2.
• Changed the name of the table to be created to Table2 (C).
• Changed the file path to reflect that of File2 (D).
• Changed the name of the source file to reflect that of File2 (E).
• Ran Script2 to create Table2 with 8 fields in it.

Third, to create Table3 and Script3, I did the following:

• Made a copy of Script1 and renamed it to Script3.
• Changed the name of the table to be created to Table3 (C).
• Changed the file path to reflect that of File3 (D).
• Changed the name of the source file to reflect that of File3 (E).
• Changed the 8th field to reflect the extra field in File3 (changed the field name, format, width, and display name (H through K).
• Ran Script3 to create Table3 with 8 fields in it.

In this example, I only performed 1 manual import (File1), but ended up with 3 scripts and 3 tables. The more fields and records that each file contains, the more time you can save using this method.

So give this a try and let me know how it works for you. Let me know any points of confusion and whether this method saved YOU time.