How to deal with problem data when importing CSV

Question

My first time trying to import CSV data into CiviCRM. The data looks something like the below (changed to not reveal actual names etc). The lines in bold are not importing.

I am guessing that in first failure (Allan Nori) the city (Boise /Hiroshima) and the country (United States / Japan) are causing the problem. In the second, probably the state/province (British Columbia (BC)). And in the third, the state/province (Maryland & District of Columbia (DC)).

For the middle one, I guess I can just eliminate the part in parenthesis ((BC)), although it might be nice if the contact would also come up on searches for BC.

I'm not sure about the best way to handle the other two. In the first case, the person lives part of the year in Boise, Idaho, and part of the year in Hiroshima, Japan. In the other, Chevy Chase is actually in Maryland, but the person also wants to come up on searches for District of Columbia (or DC).

Any advice on the best way to handle such situations would be appreciated.

(By the way, these are not all the columns. I do have email and other data in other columns, but I don't think those are important for this issue.)

Answer 1

You should note the exceptions for the contacts with dual residences and manually remove their secondary residence, and only keep their primary residence in the sheet. You can always create their secondary residence address within CiviCRM after the import wizard finishes as a non-primary address.

For State/Province names and Country name mismatches, you can create a simple MySQL query for each country that has an issue to get the exact spelling that CiviCRM expects in the import process

use my_civicrm_db_name_here;
select c.id, c.name as country , sp.id as state_province_id, sp.name as state_province_name from civicrm_country as c left join civicrm_state_province as sp on c.id = sp.country_id where c.name = "Canada"

For the list of country names you can simply query the civicrm_country table like so:

use my_civicrm_db_name_here;
select id, name, iso_code from civicrm_country

Answer 2

First the available countries and states/provinces need to be defined in the Localization settings.

If you cannot do the SQL query, you can see the available values by creating an address manually and seeing the available values.

The import allows state abbreviations for at least the United States. (e.g. Arizona - AZ). I use the abbreviations when I do contact imports (I import a lot of contacts).

The city field is not validated against a list of valid values. So a dual city would work but is not practical. You should clean up the import data and have 2 addresses for those that need them as Young suggested.