Author Topic: Wonky state abbreviation assignment - any fix for data already imported? (Read 1255 times)

mkpinformation · March 30, 2011, 10:38:08 am

So I see some other threads on the topic that indicate an import bug.

It seems that during import, state is defined separately from country, so that all of my "CA, US" contact records have been defined as "Ciego de Avila" which is a Cuban province regardless of the presence of the "US".

There's many other such error examples - "KS, US" comes in as "Kalimantan Selatan" which Google kindly informs me is part of Indonesia. I won't bore you with other examples.

I've tried imagining ways to fix the data. First I tried searching by that province name - thinking I could export just the screwed up records with "Ciego de Avila" for example, change them in the CSV, and bring them back in as UPDATE. But, alas, no - the only way I can isolate Ciego de Avila in search is by defining Cuba as Country. Nice try, we have lovely parting gifts.

So then I thought "maybe I can access this inside MySQL" - but that's way over my head, as address isn't part of the contact table, and then in the address table, state/province is apparently referring to a table of numbered codes or something. Definitely not a simple text field, I get that.

Is there SOME way I can fix this data other than manually one at a time for 43,000 records?

Hershel · March 30, 2011, 01:05:40 pm

This could be done in MySQL by a developer who knows the DB schema. Presuming that all of the "errors" could be corrected, i.e no one actually has an address in Ciego de Avila etc.

That anyhow is how I would probably approach this.

mkpinformation · March 30, 2011, 02:05:17 pm

Thank you Hershel.

I'm trying a admin-level fix, cuz I'm no developer. I used search builder to export all the Ciego de Avila records, changed them to California (fully-spelled-out) and am importing them back. 5000+ records (lots of people live in California!) so this is taking a LONG time... and proving to perhaps not be the most effective approach.

mkpinformation · March 31, 2011, 07:22:46 am

Hershel, others -

Do you think this sort of thing might be prevented if the import listed the State/Province field in the CSV **AFTER** the Country field? I thought of this because of the weird way entering those aspects manually in a new individual window works.

This might be something for the CORE TEAM to look at - and/or if the above would work, to make specific instruction regarding import CSV formatting - to have the Country column BEFORE the State/Province column??

Donald Lobo · March 31, 2011, 08:15:17 am

This is fixed in 3.4

can u do a smaller import on http://sandbox.civicrm.org/ and see if the country/state is assigned properly

lobo

mkpinformation · March 31, 2011, 10:49:16 am

Glad to know 3.4 fixed this - I tried to upload a small CSV of data but got errors "invalid value for Country" indicating both CA and US aren't allowed. Hesitant to mess with the dev site localization settings.

Also, it rejected AB for Alberta - on top of not liking CA for Canada.

CiviCRM Community Forums (archive)

News:

Author Topic: Wonky state abbreviation assignment - any fix for data already imported? (Read 1255 times)

mkpinformation

Wonky state abbreviation assignment - any fix for data already imported?

Hershel

Re: Wonky state abbreviation assignment - any fix for data already imported?

mkpinformation

Re: Wonky state abbreviation assignment - any fix for data already imported?

mkpinformation

Re: Wonky state abbreviation assignment - any fix for data already imported?

Donald Lobo

Re: Wonky state abbreviation assignment - any fix for data already imported?

mkpinformation

Re: Wonky state abbreviation assignment - any fix for data already imported?