The working scenario is that we want to update the values we have in the Country contact field. Besides that the standard list is over 200 entries, we have hundreds of non-standard values across a few million contacts.
The first step would be to go to Settings/Picklists and create a picklist with the standard country values. You can add entry by entry, though you should be making a 2 columns excel file and upload. In the upper right corner there is the upload the values.
Second step is to obtain a full listing of the non-standard values and map them to correct values. The format should be an excel, left column being non standard values, and the right column being their mapping to a correct value. In order to get the list of non-standard values you can go to Setting/Field and Views, select your contact field, and click View field population details in the lower left corner
Another way to obtain the values is a Contact Field Values report from Insight.
Now, there is a bit of manual work here in creating the mapping file. You should assign a correct value for all non-standard values thus obtaining an excel mapping file.
Navigate to Contacts/Data Tools. In the upper left corner pick Data Tools/New Lookup Table. Create the table and in the upper right corner you have a Manage Entries button and upload the mapping table. It should look like below.
You can also add manual entries to this table.
Now create a new update rule made to run based on the Update Table you just made. See below.
Now in order to do the actual cleansing, create a program builder to run the update rule against the desired audience.
In the first step add the contacts with either a filter or feeder. The second step is running the update rule set. The last step is an exit.That's all you need, though the above setup checks for values not covered by the Update Tables and adds them to a wait (later on I am manually adding new values in the LookUp Table and circle the contacts back to the top).
Note: for the field you want to cleanse, you could make an eloqua picklist with the standard values. Then create a compare contact fields filter, asking for the contacts having Country field values NOT in that picklist nor blanks. And that's your audience to cleanse.