Tool to help import payments and memberships without emails into CiviCRM

CiviCRM has import functions for contributions and memberships but these require either a known contact Id or email to function.

I am importing rather messy spreadsheet data which only records names against the data I need to import. So how could I marry these records up with the correct CiviCRM contact? I couldn't do it externally in the spreadsheet (e.g. with a VLOOKUP) because the way the names were entered did not match (exactly) any other sheet(!).

This tool is also useful if you have a dataset with emails, but the email doesn't uniquely idenitfy a contact in your database.

So I wrote a tool. If you think you'd find this useful, you can grab it from github. It's written as a Drupal module because that was the fastest way for me to get it working - I realise this is no use if you're running CiviCRM on some lesser other platform.

An interactive tool

It assumes the first three columns are first name, last name, email. So make that so in your spreadsheet. Don't worry if you don't have emails, but do create the column.

When you upload it, several fuzzy matches are attempted

  • If there's an email...
    • Does it match one person? - use that contact
    • Does it match several contacts? - list them for the user to choose.
  • If there's a first and last name
    • Does it match one person? - use that contact
    • Does it match several contacts? - list them for user to choose.
    • No matches? Try last name and first name as a substring (e.g. find "Fred and Wilma Flintstone" from "Wilma")
      • If there's one contact, use that.
      • If there's several, list them.
    • Stil no matches? try last name and first initial.
      • If any are found, list them for the user to choose - this is potentially quite broad now.
  • If there's just a last name, look it up, perhaps it's unusual enough to be useful.
    • If there's up 1-10 matches, list them for the user to choose.
    • If there's more, just say so and give up.
  • Still no match. Well this person's clearly not in the database. Let's give up.

After that, you're presented with the table in a data. The rows that aren't yet matched but could be through interaction are highlighted. The potientials are listed with links to their contact records and an (optionally) AJAX 'choose' link.

Finally you can download your .csv file back again, but with a CiviCRM ID column prepended (will be blank if no match found). The rest of the data is identical.

This can then be pumped into the appropriate normal CiviCRM import function, for memberships, contributions, activities etc.




Add new comment