Comparing Duplicate address

Question

I have set of name, addresses, phone number etc in CiviCRM. These are slightly outdated. I have an excel sheet for the same people with their new addresses. I have a unique external identifier for most of them.

If the excel sheet address is new, I want CiviCRM to add it as a second address and if it is same as old address, then I don't want to update.

How can I achieve this?

Answer 1

The most reliable way to do this is to not import addresses that are known matches.

I regularly use Pentaho Kettle to create import files for me. I'll separate addresses into their own CSV, then use Kettle to look up whether the address matches the contact's address in Civi. I'll filter out those that do, leaving only remaining addresses. However, Kettle has a high learning curve.

You could do the same with just LibreOffice Calc or Microsoft Excel - but it requires some Excel/Calc knowledge.

• Export all CiviCRM addresses (with external identifier) to a CSV.
• Open the CSV in Calc/Excel, and concatenate all the fields you want to compare into a new column (make this Column A). Label this column "Unique lookup".
• Create a concatenated "Unique lookup" column in the new address spreadsheet as well, using the same fields.
• On the new address sheet, Use the VLOOKUP function in Excel, using exact matching. Use the existing addresses as your lookup sheet. Return a value in a new column for matching addresses.
• Sort by the VLOOKUP field in your new addresses. Delete any address that has a match in the existing addresses.