CiviCRM Community Forums (archive)

Whenever you have a lot of contacts entering or editing their own data, a common situation is to have a lot of names & addresses entered in all upper or all lower case:

JOHN SMITH
john smith

How do you all find these problem entries so that you can update/edit them?

Here is the method I've been using--it is, to say the least, super-kludgy.

1. Open PHPMyAdmin and do this search:

SELECT id FROM `civicrm_contact` where
(binary first_name REGEXP '^[[:lower:]]+$'
OR binary first_name REGEXP '^[[:upper:]]+$'
OR binary last_name REGEXP '^[[:lower:]]+$'
OR binary last_name REGEXP '^[[:upper:]]+$'
OR binary organization_name REGEXP '^[[:lower:]]+$'
OR binary organization_name REGEXP '^[[:upper:]]+$' )
AND is_deleted !=1

2. Then export the results, which is a list of contact IDs that have the all upper/all lower situation.  I export as CVS and then open that with a text editor.  With a little massaging, find/replace, etc., it's pretty easy to get the list of numbers into this format:  (3,5,27,300,403,424,1000,2301).  (Obviously, I have a lot more in my list, about 100 or 200 typically.)

3. In CiviCRM, open search builder and enter:

   Individual, Internal contact id, IN
and then paste your list of contact IDs into the form: (3,5,27,300,403,424,1000,2301)

4. Search.  Then "All X records" and "Batch Update Via Profile".  I use a profile that has first, middle, last, and nicknames as well as some other fields. 

So then I can quickly & easily edit the names of up to 100 contacts at a time.

5. When finished editing the profiles, which takes care of all individual contacts, re-do the search builder with:

   Individual, Internal contact id, IN  and enter the same list of contact IDs.
This will pick up all the Organization contacts and you can repeat the same process of editing the contacts via profile.  If you have Household contacts, you'll need to do the search & the profile editing steps again for those, as well.

So, that is my kludgy solution.  Anyone have any better ones--this has got to be a common business process CiviCRM users need!

Also--this works only for contact names.  What about addresses?  Obviously, a similar strategy could work for them.  But there must be an easier way!

Here is a query that will find addresses & cities that are all upper or all lower case:

SELECT cc.id FROM `civicrm_contact` cc
left join civicrm_address ca on cc.id=ca.contact_id
where
(binary ca.street_address REGEXP '^[[:lower:]]+$'
OR binary ca.street_address REGEXP '^[[:upper:]]+$'
OR binary ca.city REGEXP '^[[:lower:]]+$'
OR binary ca.city REGEXP '^[[:upper:]]+$'
)
AND cc.is_deleted !=1
AND ca.is_primary = 1

Just as with my procedure above for editing names, export the list of contact IDs, format them to use in the Search Builder "IN" query, and then edit the address records using "Batch Update via Profile".

Everybody seems to have their own hack for this, myself included. Would be nice if one of us would package it as an extension. I've been meaning to for some time, we'll see if I have some time at the sprint tis week...