Recently, I ran an import script to import a delimited file into ACL, but the last 10 fields were not imported. And I didn’t know it right away, because I received no error message.
In addition (or should I say, in subtraction), the log did not indicate anything was wrong. When I manually imported the file, everything was fine. I copied the log and created an import script.
When I was created another script to do some analysis, I realized I forgot to change some fields to datetime format so that they would display a date like 01/01/2014. So I tweaked the import script to format those fields as date fields.
Then I reran the import script and thought all was fine. When I started running the analysis script, I noticed my table was missing some fields. My analysis script kept telling me a couple fields were invalid (the message said something like “field might have been misspelled”).
When I displayed the table layout, the fields were missing. However, the fields were included in the import script. I ran the script again and the fields were still missing.
After too much time and frustration passed as I tried to figure out the problem, I noted the last field that DID appear in the table. Then I reviewed that field in the import script. It was fine.
Then I noticed the next field had an extra quote in it (see yellow highlight):
Then I looked at the table layout. Notice the “Title” column in the red box below contains format information instead of a title, with a trailing quote. That is why the script died quietly on this field and refused to import this field or any field after it.
So I removed the extra quote in the script, and it ran like a champ.
Not Just Delimited
Just for fun, I tested this on other file formats besides delimited files by adding an extra quote in a similar spot in other import scripts. The same problem occurs with text files, and most likely all other formats. So scripter beware.
So my 2 takeaways:
- Sometimes ACL doesn’t tell you when an error occurs, not to your face, and not in the log. That’s bad. It appears that the program does not check the format sections of the script for proper syntax.
- Be more careful in editing scripts.
Anyone else run into this?
For my other posts about ACL, see Master List of ACL Articles and Tips. Don’t miss Teach Yourself ACL.
For all my posts, ACL or not, see Written by skyyler.
4 responses to “ACL Error: Not all Fields Imported via Script”
first off, thanks for all the things you’ve shared about ACL tips and tricks. it does help me a lot with my work as an auditor, especially in journal entry analysis. i’m trying to sign up to the ACL website to hopefully get some free resource and training materials that i could work on to enhance my ACL skills but, unfortunately, it’s asking for my ACL serial number or GRC url. is the ACL serial number the one that you have when you go to Help > About ACL 9…? thanks again! looking forward to more ACL posts!
It makes my day to hear I could help. Some of the materials do not require any registration or ACL license, but not much. They do have youtube videos that are open, but the quality of the video is better at ACL.com.
Yes, the number is the license number at Help > About.
Was just reading this again, and had to chuckle. In the ‘Takeaways’ section above, 1st bullet, I complain how ACL doesn’t always notify you of syntax errors. In this post, https://itauditsecurity.wordpress.com/2016/11/23/acl-import-fails-no-error/, I complain that ACL reports a syntax error when none existed.
I guess ACL is smart enough to recognize that a table name is too long, but not smart enough to tell you exactly that.
Pingback: Master List of ACL Articles and Tips | ITauditSecurity