ACL: Edit Scripts Easily

As soon as you create an ACL script, you often have to add to it or edit it. There’s an easy way to do it.

But this post is a long one. So I’d suggest you print it, and read through it before attempting this at home, and especially at work. Unfortunately, while editing scripts is easy to do, it takes a bit to explain. But I’m confident you’ll learn enough to make it worth your smile.

First, a Few Notes about Scripts

When I write scripts, I generally don’t write just 1 or 2 scripts for a project. I separate my scripts by function so that my project is compartmentalized by function/script.

One script imports a file, another verifies the file and checks for duplicates, another script cleans or formats fields, etc. I do that so that as I edit scripts, I can run one function at a time and make sure it’s working before moving forward. It also makes it easier to reuse commands and scripts in other projects.

Although I talk about editing “scripts”, I really mean editing the commands used to import,  join, extract, and export data. You might put all those commands in a single script, but it makes writing and troubleshooting the commands harder.

Editing Scripts

To easily edit scripts, I use what I call the “FIRST 10” principle. You can apply this principle to joining tables, extracting tables, or exporting tables. All you need to do is add “FIRST 10” to the end of the command.

You can use any number you want; you could use “FIRST 20” or “FIRST 35” or whatever. I just like 10 because it’s fast and does the trick.

In the original post, I mistakenly said “fields” in several places when I meant “records”, which I fixed. The “First 10” principle applies to records, not fields. Sorry for the confusion. Thanks to Chris (see Comments) who caught the other error.

Let me illustrate.

On a recent project, I received the source file and just finished scripting the joining of 2 tables. I also had created a bunch of scripts that added computed fields, extracted various tables, and exported tables to create reports. Then the hammer fell.

The audit team I was working with had the IT department extract a new source file with 7 additional fields. So I had to add those fields to the import script (which I will discuss later in this post), and every downstream script that did all the joining, extracting, and exporting.

I manually added the fields to my scripts, but instead of processing the entire file to see if I made the changes correctly, I processed only the FIRST 10 records. That’s an easy and quicker way to test your scripts. If I made an error, the script failed quickly and I could fix it quickly (this source file had over 120K records (rows) and 35 columns).

Let’s look at a simple example.

Editing a Join Script

In the following example, I had a script that used the ServerName field as the key to join 2 files: The primary file (servers2) contained the Install_Date, Tag_Num, and ServerName fields. The secondary file (servers1) contained the ServerName and Location fields.

Here’s my original join script (I’m not showing all the other commands like SET SAFETY OFF, etc.)

The primary file had a new field, OS, added to it, so I needed to add that field to my script. I typed in the extra field (it must be in the same location in the script in which it appears in the source file) and added “FIRST 10” at the end, as shown below in yellow:

Then I ran the script, which joined only the first 10 records. If the join failed, I figured out why, tweaked the script, and ran it again.

Once the script worked with 10 records, I deleted “FIRST 10” and ran the join script with all records.

Now that the join script was working correctly with the new field, I needed to change the extract script.

Editing an Extract Script

My extract script created a new table called “Florida_Servers” containing only servers where the Location field was “FL”.

Here’s my original extract script:

I made the same changes to this script, as shown below in yellow:

Again, I ran the script to extract the first 10 records. When satisfied with the results, I deleted “FIRST 10”, and ran the extract script with all records.

Editing an Export Script

Editing an export script is done the same way as editing a join or extract script, except that the EXPORT command requires you to specify the name of each field you are going to export to a report (e.g., Excel spreadsheet).

To use the same field name as the one used in the ACL table, just add AS ” ” after the field name–that’s AS followed by a space followed by double-quote space double-quote.

So I added the OS field to the script by typing OS AS ” “ at the appropriate point in the script as shown below.

Again, I ran the script to extract the first 10 records. When satisfied with the results, I deleted “FIRST 10”, and ran the export script with all records.

Editing an Import Script

Because editing an import script is a little different from editing a script that joins, extracts, or exports, I saved it for last. It’s a little more complicated.

Here’s my original import script for my primary file (servers2):

Adding new fields to an import script is similar to what you do when editing the other scripts noted above. However, in addition to the field name, you need to add the other information that describes the field.

The other items that you need to add is described in my post, ACL tip: Create a File Import Script. See the topic called “Understanding the IMPORT Script” and the items labeled I, J, and K in that section.

I added the OS field and the additional field information to the script, as shown in yellow below:

Another difference is that you can’t add “FIRST 10” to an import command. If you do, it will just be ignored.

You can limit the number of records imported in 2 ways:

a) During import, choose the radio button that limits the import to the First 100 records (see below). This is the faster method most of the time, but when I have hundreds of columns in a table, I like to use method ‘b’ (further below).

b) Change the number of records in your file (actually, a COPY of your file) as follows:

1. Save the new file that contains all the records you want to import (the one with the new fields in it) and move it into your ACL project folder.

2. Make a copy of the new file (don’t change the file name) and save it somewhere else, outside of your ACL project folder. You’ll need it later.

3. Open the new file (the one with the new fields in it that resides in your ACL project folder) with Exel or Wordpad or whatever program with which you want to edit the file.

4. Delete all but the first 25 records (remember, you have a full copy saved somewhere else).

5. Run your edited script, which will run against the file with only 25 records in it.

6. Check the results. Make sure the width (J, as described above) is correct and no characters in the new field are being cut off. If so, increase the width of that field in your script (change the value in J to a higher number), and run the script again.

7. When you’re happy with the results, move the file with 25 records to somewhere else, and copy the full file with all the records in it back to it’s original location in your ACL project folder.

8. Run the script again, this time against the full file, and check the results.

Now, some of you are going to say that importing the new file manually and then copying the log commands to the script is a lot easier. If so, do it. Sometimes it is.

But as cumbersome as the steps above might seem, it sure saves me a ton of time, especially when several fields are added to a huge file.

And after you’ve done it a few times, the steps get easier and faster.

If you have problems editing the import script, see this post, ACL tip: Create a File Import Script, which provides several problems that can occur, as well as their solutions.

Remove FIRST 10

After editing and testing all the scripts one-by-one, remember to check that you removed all the “FIRST 10″ commands from your scripts. Then rerun all your scripts in succession and double-check your final results to ensure they are correct and include all the appropriate records.

And, as always, let me know what you think.