0

We are trying to understand why importing contacts from CSV files with Address, City, State, Zip but no Country results in addresses with countries like Uruguay (for some CA residents), Yemen (for some South Dakota residents), and today, for the first time, for a bunch of CA residents, they were assigned "Guinea-Bissau". Although we have different staff doing different imports, the majority (e.g. ~3000 out of ~3060) of our imports DO end up with Country="United States"

In "Adminster > Localization > Languages, Currency, Locations" we have Default Country set to "United States". That was set when we first configured Civi long ago.

Is there some other setting that deals with setting the default country on import? Should we solve this by always adding a Home-Country column to CSV imports? That seems cumbersome, but will do if that's the only solution.

BTW, we're running WP 4.8.2 and CiviCRM 4.7.22.

2

I think the problem can happen if you import your 'State' as the Abbreviation. If you look in table civicrm_state_province you will see lots of States with Abbreviation CA - and unless you specify the right country it can have unexpected out comes possibly depending on which Countries you have enabled.

I think this jira address the core issue

The workaround for this issue is to ensure that, when importing address data, that a valid country is always specified when there is data in a State/Province field.

  • Hmm I see JIRA has a patch and in QA. I will nudge one of my team to see if they can QA this and help move it along. – petednz - fuzion Sep 23 '17 at 20:49
  • I think we can all agree that as I stated previously one should never run an incomplete Address import... – KarinG - Semper IT Sep 24 '17 at 0:28
1

For sure this sounds like it should be working - and one could say it's a bug that it does not default to default country - but the workaround: to explicitly add Country in your CSV for import - is so easy -> I recommend you just do that and get this off your plate.

If you have System Settings -> Mapping & GeoCoding enabled -> the GeoCoding may be the culprit here: there are Cities that appear in more than one Country - so feeding it a partial address could lead to 'random' results; I've often realized that when adding a partial address in Google Maps for directions - and the result sent me half way around the world [as opposed to using my default Country - it should know where I am after all]; who knew there is Manchester in California, USA; or a Chelsea in Alabama, USA; there is a London in Ontario, Canada;

So best to never run a Contact import without Country or disable the Geocoding -> Parsing; I have never run an incomplete Address import - my scripts always add Country;

  • Yes, the workaround is easy, but I don't believe in computer voodoo. This only may be one symptom of a bug that has other, currently unnoticed, consequences. That's why I've thrown it out there. We've noticed this for some time the past year, but the "Guinea-Bissau" observation from just today was so bizarre (random?) that I felt the need to share in case someone else has encountered the same and discovered the cause. – Karl Rudnick Sep 23 '17 at 5:56
  • I might also add that if you look in the civicrm_country table in the database, that United States and Uruguay are off by 1 in id. At the beginning, we mostly saw the Uruguay mistake. Sounds suspiciously like an OB1 (Kanobe ;)) error - index off by 1. However, that doesn't explain the few Yemens or the 15 or so (out of 38) Guinea-Bissau entries observed today. – Karl Rudnick Sep 23 '17 at 6:11
  • Finally, we do auto-geocode all addresses, and your suspicion for leaving out Country is spot-on, I believe. There is a San Diego, Uruguay which would explain that, but I don't think there is a San Diego, Guinea-Bissau or a Sioux Falls, Yemen. Nevertheless, no more imcomplete addresses will be imported. – Karl Rudnick Sep 23 '17 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.