Identify Blank / Repeat Fields

Script: IdentifyBlank_RepeatFields.iss

Developed: Originally by Lee Cunningham, ECAS, Ottawa TSO, 613-688-734

Modified by Paul Dawson, Windsor TSO, May 25, 2007 and  Steve Luciani, London TSO, 519-675-3057 March 5, 2009

Script rewritten by Brian Element (brian.element@ideascripting.com – part of the GEL group) May 14, 2012

This IDEA script scans all the fields in an IDEA file. If a field contains two or more different values, the script adds the field name to a text file called KeepTheseFields.txt.  Fields that did not make it to the text file are unwanted fields that are either a blank field or a field with a repeat value. The script also earmarks unwanted fields by prefixing their field names with a user specified string. An additional file is created that will show the field that are either empty or repeats along with the content and a boolean field in which the auditor can select which fields to delete from the main file. 

When starting the script you will have the following options:

The first option allows you to select a file in which to look for empty or repeat fields.  The second option allows you to delete the fields from the file assuming that you have already run the first part.

By selecting the first option you will have the following dialog:

This dialog allows you to select the file you wish to perform the analysis on plus select the prefix for the fields that have been identified as empty or repeats.  The prefix must begin with a letter, any other selections will give you an error.  The script will identify all numeric, character, date and time fields that are empty or repeats.  The script uses the field statistics for the numeric, date and time fields so it will force a recompute of the field statistics prior to performing the analysis.  A separate file will be created which will show the name of the field that is empty or a repeat, the field contents if the field is a repeat, if not it will be blacnk and a third field that allows you to select the fields you wish to delete.

A history is also written to the selected file showing the fields that are empty or repeats.

Once the following information has been generated you can rerun the script and select the Delete Fields option, this will use the file that is generated above to delete the fields through the script.

You must select two files, the first file is the file that has the fields to be deleted, the second file is the file that was created in step 1 and show the fields to delete.  The script will go through the file and delete all selected fields from the ***-del fields file.

If the script completes properly you will get a message saying that the script ran properly.  The script also uses progress bars so you will be able to tell approximately where you are in the script, the progress bar is an estimate as some type of fields take longer to analysis then others.