Advice on CSV import with taxonomy for data sanitaztion i have a situation where users will be uploading csv files of the same format/columns but
the data is not consistent...for example in the "state" field, one office might use "NC" and another might use "North Carolina". another might have "N. Carolina". misspellings are common too in some fields. 

how do i let them upload this data but sanitize it at the same time so that i know that "NC" == "North Carolina" == "N. Carolina"...etc?

my thought is to use a vocabulary for each column and force the user to map any data that doesn't match up before the csv is processed and nodes are created. 

so i'm thinking the process would be as follows:


user uploads csv
user gets a form/table showing the data they uploaded along with a vocab drop down for each cell that needs to match up to a term. (each cell in the state column would have a drop down)
user manually maps any data that did not match up to a term.
user hits "process csv" and nodes are created.


so if i uploaded a csv that had "Nrth Car." in the state column...i would have to select "North Carolina" from the drop down before the csv can be processed. 

*would also be cool if i could store these variations as synonyms so that next time they upload a csv they won't have to map the same variations again. ("Nrth Car." would automatically be mapped to "North Carolina" the second time around.) 

thoughts?
Store the list of states as a taxonomy and add a text field to the taxonomy called aliases. This would be an text field with unlimited values.

Now when checking if a term exists you can use:

function taxonomy_load_term_check_aliases($name, $vid) {

    // load term by name (not using taxonomy_get_term_by_name() as I want to pass in vid, not machine name)
    $tid = db_query('SELECT tid FROM taxonomy_term_data WHERE vid = :vid AND `name` = :name', array(':name'=&gt;$name, ':vid'=&gt;$vid))-&gt;fetchField();

    // no match - see if an alias exists
    if(!$tid) {
        $bundle = db_query('SELECT machine_name FROM taxonomy_vocabulary WHERE vid=:vid', array(':vid'=&gt;$vid))-&gt;fetchField();
        $tid = db_query('SELECT entity_id FROM field_data_field_aliases WHERE field_aliases_value = :name AND bundle=:bundle', 
            array(':name' =&gt; $name, ':bundle'=&gt;$bundle))-&gt;fetchField(); 
    }

    $term = taxonomy_term_load($tid);

    return $term;
}


If you can't find a match then add an additional alias. As you list of aliases builds up it will become more and more automated.