5

We are transitioning a small organization to CiviCRM 4.5, with about 2000 total contacts and about 150 memberships. The contacts imported without any problem, but when we went to import the memberships, we found that CiviCRM required either an e-mail address or an external ID.

About half of the members do not have an email address, and since the old membership system was a combination of Excel files and offline physical media, they have no unique membership numbers or other identifiers. All of the firstname+lastname combinations are unique, so we thought that should be sufficient, and we have the deduplication rule set to that effect elsewhere, but it seems to be inadequate for membership imports.

We don't want to be in the business of creating dummy email addresses, so it seems we must manually assign an external ID to the old contact list and the membership list, then reimport both in turn. But is there an easier workaround— some way to use Firstname+Lastname as the unique identifier? Or is there another "best practice" we could adopt here?

Improve this question

2 Answers 2

Reset to default
5

Here's an idea:

  1. Import contacts (as you have already done)

  2. Export the contacts you just imported, selecting the at least the following fields: contact ID, first name, last name

  3. Use spreadsheet formulas to match the ID up like this:

    1. Add a new sheet in your memberships spreadsheet

    2. Paste in the exported data

    3. Add a new column to the left (can't be right) of your exported data, and call this column "name".

    4. Create and fill a formula for the name column to concatenate the first and last name (e.g. =C2&" "&D2 will concatenate the contents of cells C2 and D2, with a space in between.)

    5. In your memberships sheet, create a new column called id, and create a formula like =VLOOKUP(A2,Sheet2.A$2:D$10000,2,0).

      1. A2 is for the column in your memberships sheet that contains the full name. If you have two columns, then you'd want to write something like C2&" "&D2, as we did before.

      2. Sheet2.A$2:D$10000 is an absolute reference (dollar signs so it won't change when formula is filled downwards) to the range containing all the ID reference data. I wrote 10000, but make sure you use a number large enough to encompass the whole data set. Note that Sheet2 is the name of the sheet.

      3. 2 is to say that the value you want to pull into your memberships sheet is the second column from the left in your ID data.

      4. 0 is to say that you want the VLOOKUP function to give you "#NA" if it can't find a match instead of the (terribly chosen) default behavior of picking the nearest match.

    6. Export memberships spreadsheet to CSV, and then try importing into Civi using the ID values that you pulled in

(These formulas should be correct for LibreOffice, but may need to tweaking for MS Office, with which I'm less familiar.)

Improve this answer
1

If you have already imported all your contacts, then each one will have a unique CiviCRM ID. As you only have 150 memberships to take care of, it won't be a huge task to match the CiviCRM ID to the membership record (and with some Excel skills, which I don't have) it could probably be at least semi-automated. Then you can use the CiviCRM ID as the match field for your membership records.

http://book.civicrm.org/user/current/common-workflows/importing-data/ states that the CiviCRM ID or an external ID will do the job.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.