Introduction
Oftentimes you have a spreadsheet of people who attended your event. Maybe you exported it from another event system, or it's a spreadsheet being shared among the event organizers.
This how to file explains how you can import those contacts and the participant records into the database, preseving details about their participation, such as whether or not they attended.
Note: alternatively, if you are importing participants for a single event and they all have the same attendance status, you can follow our easier tip sheet on generating participation records.
Getting Ready to Import Event Participants
If you’ve never imported data into PowerBase before, you should look over the sections in the CiviCRM user manual that explain the concept and the procedure.
https://docs.civicrm.org/user/en/latest/common-workflows/importing-data-into-civicrm/
PTP has made a screencast on importing contacts. You can see it here:
Planning your Data Acquisition Strategy
When you think about importing event participant data, there are several factors to consider.
Is this import going to be repeated on a regular basis?
If so, you should plan to create a standard .csv or spreadsheet file with headings that you can reuse for each import. This will make repeating your import process much easier.
Are the contacts that went to the event already in PowerBase?
If not, you’ll have to import the new contacts before importing the event participants.While importing contacts, think about how you will match the event participants to this contact (person or organization). The easiest and most reliable way to make sure the match happens is to put an external ID on the contact and then use that contact external ID when importing the event participants later.
A contact external ID, aka External Identifier, is a field that you load with unique values so that you can import or export to another system. If this is a one-time import, you can also assign an arbitrary unique value to the contact external ID. For example, use today’s date combined with a sequence number. Using the date as part of the external ID will help ensure it doesn’t overlap with anyone else’s contact external ID.
If the contacts are already in PowerBase, what data will you use to link the activity to the contact?As noted above, a reliable method for linking a activity to a contact is to use a contact external ID. Equally reliable is to use the internal contact ID aka CiviCRM ID aka Contact ID but that can only be obtained from PowerBase, either by transcribing it into your file one by one, or by exporting the contacts and finding a way to match the contacts to the appropriate event participant records though some other means, such as your spreadsheet program. If you don’t have a contact external ID or the CiviCRM ID in your data, you will need the individual’s first name and last name (in separate fields). As an alternative, you can use an email address. Since sometimes first names, last names or email addresses are not unique in your PowerBase, you can use combinations of all three to achieve the match that you require. This assumes, of course, that you have these fields in your import file. If you don’t have these fields, or if names are combined into one field, you’ll need to edit your import file before you try to import.
Importing Event Participants
First – make sure the contact is already in your system. If the contact is not already in your database, you can’t import their event participants. This document won’t cover importing contacts in detail. See the references above for details.
Getting Started
Your activity file must be in a Comma Separated Values (.csv) format, not in the default spreadsheet format.
Prepare a file with the data that you are bringing into your PowerBase.
This figure - Table 1 List of Activity Import Fields - shows you which fields are required and recommended. The remaining fields are optional. If not all fields are in your dataset, “Activity Status”, for example, you’ll have to add a column to your data and fill each row with the desired “Activity Status” values.
Table 1 List of Activity Import Fields
| Subject | Required | Notes |
|---|---|---|
| Event ID | This is required | Look it up on every page |
| Participant Status ID | One of these is required | Set to "Attended" |
| Participant Status | Either Status or Status ID is required | Set to "Attended" |
| Contact ID | Contact ID, Email or Ext Id is required | Identify the target or "With" contact |
| Email (match to contact) | Contact ID, Email or Ext Id is required | Identify the target or "With" contact |
| External Identifier | Contact ID, Email or Ext Id is required | Identify the target or "With" contact |
| Campaign | Optional | Not campaign name, but the id associated with the name |
| Participant Note | Optional | Notes go here |
| Participant Role | Optional | Set to "Attendee" |
| Participant Source | Optional | |
| Register Date | Optional | Defaults to today's date |
The import file must have some combination of fields to match to the contact as described above:
- contact external ID (sufficient by itself)
- internal contact ID (sufficient by itself)
- email address (may be sufficient by itself depending on dedupe rules and data content)
PowerBase will use the default strict de-duping rule to match the activity to the correct contact. It is beyond the scope of this document to cover de-dupe matching rules, but you can find some information in the CiviCRM manual here:
http://book.civicrm.org/user/current/common-workflows/deduping-and-merging/
You’ll find the “Import Event Participants” link on the Events Menu of your PowerBase.
When you select it, you’ll see the screen below
Check the highlighted fields to make sure they are set correctly to match your data. Click on “Browse” to bring up a window that allows you to find the import file on your computer. Once selected, hit the “Continue >>” button.
Note – if you’ve already imported particiapnt data before, and if you’ve saved the mapping list, you’ll see an option at the bottom of the page which allows you to retrieve your mapping list and re-use it. That’s a good thing because it makes the next page go quickly the next time around, but only if the table’s field layout is in the same order as the prior import.
The next couple of screens show the second step with different import files.
PowerBase will show you the column headings from your import file and the related data from the first two records. This helps you with the matching process. When you are in the matching phase, you’ll go row by row and assign a PowerBase field to each column of your data that will be imported.
When your matching is completed, and if you know you will be importing a dataset just like this one in the future, you can “Save the field mapping” by ticking the appropriate box and entering a name. When you are done with the page, click “Continue >>”. PowerBase will verify your data for some errors and if it passes, you’ll get to the next screen which allows you to import the event participants.
If this screen looks good, you can click “Import Now>>” to bring the participant data in.
Once you complete the import, you get a screen with the results. If all the participant data was entered correctly and matched a contact, all of the rows will go in. If there is an error on the row, it will show it as Invalid Row (skipped). Click on the “Download Errors” button to get a copy of your file with only the offending rows. A new column is added that holds an error message for the row. If you don’t have a match on the contact in the particpant import table, you’ll have to add the contact, then reimport the participant record, or just add it manually on the event menu or contact record event tab.
