Data Cleanup Tools

Specify Software Project Staff
04 Feb. 2013
Version 1.0

Specify has data cleanup tools for Agents and Geography. The tools enable users to consolidate duplicate data while updating all necessary database relationships.


The overall process of cleaning up agents is to find two or more Agents that are the same person and then replace all the references for those duplicated Agents with just one Agent. The challenge is merging the related auxiliary information together to form that one unique Agent. The following two table lists all the auxiliary information and relationships for an Agent.

Auxiliary Information
Information Description Possible Issues
Address An agent can have one or more addresses. Removing duplicate addresses
Agent Attachment Move all attachments None
Agent Speciality One or more textual descriptions of an agent's area of Speciality (usually Taxonomy) The order of Specialities may need to be fixed.
Agent Geography One or more textual descriptions of the geography that the agent specializes in. The order of Geographies may need to be fixed.
Agent Variant A list of additional names where the type of name is: Variant, Vernacular, Author, Author Abbreviation, Label Name. Should only contain no more than one of each type.


Relationship Description Possible Issues
Accession Agent   Duplicate Roles
Borrow Agent   Duplicate Roles
Deaccession Agent   Duplicate Roles
Loan Agent   Duplicate Roles
Agent Geography   Duplicate Roles
Gift Agent   Duplicate Roles
Funding Agent    
Loan Return Preparation    
Accession Authorization    


There are three Agent specific clean up tools:

Tool Description
Exact Name Match This tool looks for duplicate Agent records where the Last Name, First Name and Middle Initial fields are exactly the same. If there is a mixture of associated data (e.g. addresses) it will ask the user to choose which data to include in the merged record.
Last Name Separation More often than not, there are Agent records where the entire name has been entered into the Last Name field. This tool takes a guess at how to divide up the name into its different parts of Last, First and Middle Initial.
Fuzzy Match Merging Sometimes names are misspelled by one letter or maybe the first name initial is used in one record and the full first name in a duplicate record. This tools uses fuzzy matching to guess what Agents might be the same person. It provides a list of the matches and ranks from closest to furthest in likely matches.


Processing Agents

The first step is to process all the agents for exact matches. If any are found, the following dialog will be displayed showing each Agent's name and how many exact matches were found. The agents to be processed just need to be selected with the check mark preceding the name.


The cleanup process will remove the duplicate Agent and reassign all necessary references from the deleted duplicated Agent to the single Agent (the 'consolidated Agent)'. The cleanup process can take a few minutes because the database must check nearly every table for where the duplicate Agents are referenced. A progress dialog is shown during the processing. The top bar show the progress for an individual Agent and the bottom bar is the overall progress of the entire process.

During the merging process replacing one agent with another may potentially violate constraints within the database. This situation is caught and a bnotification is shown identifying the problem. For example, an Agent cannot be a collector for a Collecting Event more than one time and this can occur during the merge process, when this happens a warning is issued and the Agents are not merged.

When data fields for the Agent records are being merged and do match, some user input may be required to resolve the issues. For example, one Agent record may contain an e-mail address that is old and a duplicate record may contain a more recent e-mail address or their 'title' may have changed over time. The next dialog image shows a such a situation.

In the example above the agent had been duplicated in the database four times and there are different values in the Email and Title fields. The user selecta a 'Merge Into' record that will be the 'consolidated' or remaining Agent record and then select 'Merged From' for each of the record that will be replaced by the 'Into' record. Note that there are two tabs, the second tab contains the various different addresses that need to also be merged.

Since an Agent can have one or more addresses, the addresses to be added to the 'merged,' the 'Is Included' checkbox indicates that the address will be added to the consolidated Agent.



There are two main goals for 'cleaning up' Geography. The first is to assign ISO codes to each record so they can be compared across collections. For example, in one collection a record's title might be 'Britain' and then in a different collection the title is 'England.' Once the ISO codes are assign the titles can be ignored and the ISO codes are used for identification.

The second goal is to remove duplicate geography records and merge any 'child' records together. For example, there might be a record 'USA' containing the state of California and then a 'United States' record with a California. The clean up tools finds the duplicates and merges them. Although, the merge can be done in the tree editor, this tool can locate one or more duplicates much easier and faster.

The Geography cleanup tool is designed to work on Countries, States, and Counties. The dialog below is used to configure the cleaup process. There are two options: 1) Cleanup all the countries 2) Cleaup a individual country

Cleaning Up All the Countries

The top portion of the dialog is used to process all the countries and to a specified level. This dialog in the picture is configured to only process countries and none of the states or counties.

Cleaning Up a Individual Country

The bottom portion of the dialog is used to process an individual country to a particular level.

This dialog below is configured to process all the 'country' geography records (to just that level), and then process the USA down to the State level.


Merging Geography Records Dialog

Following dialog is presented when the cleanup tool is unable to 'pick' the correct country in order to assign the ISO code. The title in the database is shown at the top and a list of available countries just below it. The 'Update Name' checkbox indicates that the name from the list should be used in place of the current name in the database. The 'Merge Geographies' checkbox will be enabled when multiple geographies have the same name. Checking this box will merge them and their children geographies together. The 'Add ISO Code' checkbox indicates that ISO code in the textfield will be added to the record. Sometimes it is necessary to change an individual ISO code during processing.

Button Action
Quit This button will stop the processing of geographies. It will not 'undo' any geographies that have already been processed.
Next Country When processing the lower levels of the geography tree (State or County), there are times it is desirable to stop processing the current country and skip to the next country to be processed.
Skip This skips the current geography and perform no updates.
Save This button saves updates the geography record and advances to the next record.

After the processing has completed, a report is generated showing what changes were made and it is displayed in the browser



Although, duplicate geographies can be discovered and merged during the initial cleanup when the ISO codes are assigned, the ISO codes tool only searches the 'levels' that were choosen in the configuration dialog. The 'Merge Duplicates' tool will search all of the geography records for duplicates. The following dialog shows that the country of Mexico has been duplicated because there are two similar continents. The 'Primary' checkbox is used to indicate which geography will be used as the 'consolidation' geography. The 'Include' checkbox indicates which geographies will be considered when the geographies are merged.