CiviCRM Community Forums (archive)

Importing contacts sharing an address with a household is not easily supported by the GUI and comments on the forums often suggest using custom scripts.  These notes describe a GUI approach that worked for me.

Before importing anything, clean up the data as much as possible.  If you haven’t discovered OpenRefine (openrefine.org) go look now. Its ability to work with rows and records (multiple rows with a common field) makes it very flexible to manipulate individuals and households.
 
My data already had each contact assigned to a ‘family group’ equivalent to a household.  I created 2 csv files of households and individuals.  Each entry in the households file should include the household name and the address fields.  Each entry in the individuals file should include the household name and other fields not shared with the household.

Use the import wizard to create the new set of households.

Now we want to import the individuals file and use the “Master Address belongs to” field. It would be simple if this were expecting another contact’s name and address type (eg Smith Household, Home address) - but instead it needs an address id, and finding the right value takes us a little more ‘off piste’.

I don’t see an easy way to get that via the civi GUI, but it can be pulled directly from the database. Since we are doing this with GUIs, fire up phpmyadmin, select the civicrm database, click the SQL tab and run:

SELECT civicrm_contact.display_name, civicrm_address.id, civicrm_address.street_address, civicrm_address.city, civicrm_address.postal_code
FROM civicrm_contact, civicrm_address
WHERE civicrm_contact.contact_type = "Household" and civicrm_address.contact_id = civicrm_contact.id

(The display_name and id in the select clause are required, the others are optional - see the note below.)

Use the Export link to download the file listing all the households with their name and the address id.  (If you have multiple addresses per household then you probably need additional Where clauses to select the right address.  My households only have one address.)

Now we need to enhance the import file and include the relevant address id from the SQL query. One option is to use the vlookup spreadsheet function.  Arrange your data in a spreadsheet with two tabs: one with the existing import data for individuals, the other with the household name to address id mapping.  Then add a column on the individuals tab with a formula something like =vlookup(A2,Sheet2.A2:E500,2,0) … Assuming your household name is column A and your mapping data is A2:E500.  The result should be the relevant address id.  Now do the same to lookup the street address, city and postal code.

Check for any lines where vlookup has failed.  Correct or remove them otherwise the import to Civi will fail.  Then save it as a csv.

Use the Civi import wizard and map the address id column to “Master Address belongs to Home” and the other address fields as usual.

Once the import has finished, you should find the individuals are correctly mapped to the households.  One extra bit of CiviMagic is that when you share an address with a household, it automatically creates a ‘Member of Household’ relationship.

One less magic thing is that when a shared address is imported with just the address id, Civi shows that the individual shares an address with the household but does not show the actual address.  However, when the household address is updated it will then be copied to the individual and then display as expected.  A work around for this is to import address fields as well as the shared address id - hence the street_address, city, postal_code in the SQL query and the individual import file.

Hope this helps someone!

Still works like a charm - 4.4.5 - Thanks a million, @ayduns

The wiki is community editable. Please take the time and add this information to the wiki to avoid other folks asking the same question:)

thanx

lobo