ACL tip: Create a File Import Script

File Import Script VacuumDid 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. 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:

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), keep only about 25 records. 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).

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.

Copy ACL Import Command from Log

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

Create New ACL Script

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).

Paste Log Command into Script

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.

ACL Set Safety commands

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.

Edit the ACL 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.

Save & Run ACL Script

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.

ACL Import File Script Example (Key)

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) is correct in the script (see F in the Key above).
  • 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, 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).

Find Script Error in Log

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

Find Scrip Error in ACL Log

“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.

About these ads

10 Comments

Filed under ACL, How to..., Scripting (ACL), Written by Skyyler

10 responses to “ACL tip: Create a File Import Script

  1. Brian L.

    Hi – great post – thanks for all the details & screenshots. Looking forward to a similar one for importing SAP tables via Direct Link. The IMPORT SAP command is absurdly complex & tempermental.

    • skyyleracl

      Thanks, Brian, glad you enjoyed it. Thanks for the suggestion. Unfortunately I haven’t worked with SAP for a while and currently don’t have access to it, so I can’t help you with that. But that’s the kind of stuff I love to tackle. Bummer.

  2. Tom

    Firstly, happy 2013!

    Thanks for this example. I’m looking for the way to load over 10k txt files (with the same structure) into one acl table. I guess that the script should be the best solution but I’m having hard time when creating it.
    1. The source files are all in the same dir.
    2. Each source file has different filename (all are *.txt).
    3. I would like to add the file name of each of the source file as one of the fields in the output table (in order to have the clarity of the reference).

    Can someone help, please?

    • skyyleracl

      Hi Tom, top of 2013 to you too!
      Sorry, but I’m a little confused….
      1. You want to load all the source files into one table? Or each source file into it’s own table?
      2. You want to add the name of the source file (say one is called Money.txt) as a field in each table or add the name of the source file to each field name? You want this field to be blank? If not, what values would it have in it?
      In other words, do you want to see this:

      Field1 Field2 Field3 Money

      or

      Money_Field1 Money_Field2 Money_Field3

      or did I miss your point altogether?

      3. Were you able to get the IMPORT script to run without adding the source file name as a field? If not, what happened?

      Let me know your response to the above and I’ll try to put a script together. Sorry I couldn’t be more helpful as of yet.

  3. ismail

    How can i import sql code like this; PLEASE HELP ME!!!
    select sharedcustomertype, (select paramdescription from iso.dbo.parameter where paramtype = ‘ORTAKTIP ‘ and paramcode = sharedcustomertype), * from iso.dbo.customer

    • ismail,
      I’m not sure what you are asking about. I can read basic SQL code, but have never included it in a script. Are you trying to use the EXECUTE command?

      If you’re trying to make a database connection to an SQL database via ACL, I suggest your ask your question in the ACL user forum.

  4. ismail

    This is sql code :

    select sharedcustomertype, (select paramdescription from iso.dbo.parameter where paramtype = ‘ORTAKTIP ‘ and paramcode = sharedcustomertype), * from iso.dbo.customer

    i want to import this in acl

    • ismail,
      Why do you want to import this? I’m not aware that ACL will run SQL code unless you are using the Execute command to run another program that will run the SQL code. In that case, you don’t need to import it; you just include the SQL code in your script.
      Again, I think your best bet is to submit your question to the ACL user forum at ACL.com.

  5. bg

    Hi
    I created an automated import script in ACL that imports multiple files but the problem is that it gets stuck if it meets bad file.Any idea how i can skip the file and move to the next file without stopping the import script.

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s