CiviCRM Community Forums (archive)

I'm using CiviCRM v2.2 in standalone, running on a Fedora Core 10 system.

I have a list of contacts that I'm importing from a CSV file.  Almost all (but not absolutely all) have addresses in the US, so the field is unpopulated in the CSV file.  The default country for new contacts is set to the US (I checked specifically).

What's happening is that the imported contacts have the wrong country.  It looks like what's happening is that instead of applying the default country, it looks at the state (VA, for Virginia) and grabs the first country with a state that sorta matches (in this case, putting our contacts in Hungary).  Now, I have nothing against Hungary - my grandfather was born there.  But in this particular case, I'd like the country to reflect our contacts' actual country of residence.  Any suggestions?

And, for the contacts already entered (several thousand) with the wrong country, is there an easy way to change them in bulk - something like "if country='Hungary' then country='United States'"?

Thanks!

Yeah - I can duplicate it on the demo site.  I filed the issue and included the CSV file that demonstrated the problem.  Thanks.

Well, it's a little uglier than a single SQL command since the country that gets assigned to each imported contact depends on which US state their address is in.  So, I have lots of substitutions to make, not all of them obvious (since some of the non-US countries might actually be correct).  I think I probably need to just re-import all of the records after setting the countries explicitly.

Is there a command-line import script someplace?  I don't mind the length of time it takes to do the import (I've got 15,000+ contacts to import) but my browser session keeps timing out.

OK - thanks.  I appreciate the help.

I take it back:  this problem is a lot scarier than I thought.  Apparently the import resulted in completely faulty state_province_ids being imported, even though the display is showing properly.  Perhaps based on postal code logic?  Who knows.  This is very sad:

1105 Melrose Ave     Glendale     CA     91202-2485     Spain

this is the display.  However, in the database:

mysql> select state_province_id from civicrm_address where street_address = '1105 Melrose Ave';
+-------------------+
| state_province_id |
+-------------------+
|              2434 |

Whyever would it import with the state_province_id equaling C?rdiz and the Country id equaling Spain?

I can't fix this without something to hang the fix off of.  I can't find the email address in any of the address tables.  How can I identify the users and match them to my spreadsheet and try to script a fix?  What a sorry mess.

Sorry, I didn't mean to be negative!  I meant our addresses are a mess.  Do you know where the email addresses are located for the contacts?  How else can I tie civicrm_address with the appropriate contact id?  Is there a table I don't know about, in other words, or is there a schema for 2.2.6 I can look at?  Because our spreadsheet (obviously) still has States, so I can go in and fix all the state_province_ids if I just knew who to attach them to.  I don't want to trust just the first address line. 

Thanks, lobo.

I would be happy to provide the very dopey perl script I wrote, but all it does is output sql based on the states ids to "fix" the country_ids to match United States for all U.S. state_province_ids.  And that didn't fix it for us since our state_province_ids are not US States.  But I will attach the two files for any users like awarnock this might've helped. 

I'm not allowed to use the test db because this is confidential data.  Maybe I could make a limited set of rows and mess up the names and addresses...But as you can see the state_province_id is for a province in Spain. this is only one example. All of our US imports are like this--I can see them listed in the display in a contact search.  I see City, State, postal code, and then some random Country that is not the United States, and when I go into the db and look at the state_province_id, sure enough it's for some random province in that non-U.S. country.  But in the interface they have a State.

->Arrow

Thanks, lobo. That was the key. I exported the old state_province_ids and names  and emails and I've written a hinky script to write the sql to fix. It's working well.  I've discovered all the Canadian addresses are similarly showing up with African nations and stuff. Only North American addresses affected so far, with one exception:  LAN (Lancashire) province (it's in the U.K.)

I want to help you track down this issue so I've got a test import csv file with just one of the addresses and the name changed but with the header mappings.  I'll try to reproduce on http://drupal.demo.civicrm.org.  Maybe the problem will be gone with 3.0! 

I'm testing importing a data set currently and am finding the same error:

warning: substr_compare() [function.substr-compare]: The length must be greater than zero in »
/<MY CIVICRM ROOT>/civicrm/CRM/Import/Parser/Contact.php on line 1026.
Line 1026 is inside the "checkGender" function:

...
  1026              if ( substr_compare( $value, $gender, 0, strlen( $gender ), true ) === 0 ) {
...
In this test import data set, two of my contacts do not have any gender listed. Could this cause the problem? Oddly, I see that warning 6 times. Doing more investigation ...