CiviCRM’s out-of-the-box functionality includes importers for Contacts, Memberships, and Activities. The API csv Import GUI extension (https://civicrm.org/extensions/api-csv-import-gui) uses the same process as CiviCRM’s standard importer but allows you to upload and import many more types of data. Like the standard importer, you can perform an import to add new records or to update existing records. In this guide, we’ll import a list of Events to CiviCRM.
Before You Begin
Before you begin the import process using the API CSV Import extension, you will need to:
- Set up your import (csv) file.
- Identify related data.
- Plan your import.
- Perform a test import.
Pre-Import Step #1: Your Import File
Decide which fields your import will populate. If you already have some events set up in CiviCRM, review them in CiviCRM > Manage Events to identify which fields you plan to populate. Check out these sample spreadsheets to get started:
- The bare bones csv file includes the few fields that are absolutely required for an import. They are: Event Title, Event Type, and Start Date. Start Time should be included in the Start Date field. If it is not, the Start Time will default to 12:00 AM (midnight).
- The full file includes all the fields available in the Events table that can be imported using this extension.
Although the importer only requires three fields for Events, it might be necessary to populate other fields before your imported data can be usable. For example, you can successfully import records with “Paid Event” set to “Yes” but without any Financial Type data; you’d then need to fill in Financial Type via CiviCRM > Manage Events to finish setting up those events.
Add or Update Records? This importer does not check for existing/duplicate records before importing. You can use an import to create new records or to update a set of existing records. But to do both, you’ll need to do two separate imports.
- To update existing data, create an import file of existing data including the Event ID field, then create and populate fields representing the new data to use in your update.
- To import a set of new data, omit the Event ID field.
If you import data that already exists in your database, but you leave out the Event ID, your import will create duplicate records.
Create your import spreadsheet. You’ll need to generate a CSV file to perform the import, but you can use any spreadsheet editor to manage your data before exporting to CSV format.
Label one column for each CiviCRM field you’ll be populating. (See the sample spreadsheets for bare bones data and full file data.) CiviCRM and the importer extension do not use your column labels for anything, so give them names that are easy for you to read and recognize.
Fill in some data. Populate event names, date fields, and and text/HTML fields. Fill in checkbox fields with “1” (without quotes) for yes/checked and “0” (without quotes) for no/unchecked.
Pre-Import Step #2: Related Data
Your import will include references to other data in CiviCRM. At a minimum, you’ll need values for the required Event Type field, so let’s use that as an example:
1. Look up your existing event types in CiviCRM. (You can find them under Administer > CiviEvent > Event Types, or by pulling up an existing event via Events > Manage Events and under [Info and Settings] click the wrench icon next to the Event Type field.)
2. Identify which of your system’s event types you will use. Note that the event types set up in your CiviCRM database may vary from the ones shown in this screenshot. Your import must use your event types.
3. In the Event Type column of your import file, fill in the exact value of the event type(s) as either a number or text. The importer can handle both, so choose the format you like best:
- Event type ID. In our example, this is labeled “Value.” To import events which are all performances, I would put “5” (without quotes) in the Event Type column for every row in my spreadsheet. Why use the ID? The import will only be valid if this data exactly matches the data in your CiviCRM database. If your data file contains the smallest typo, your import will generate an error. If you want to be sure the related data fields are as error-proof as possible, use IDs.
- Text label.In our example, an import of performances would have the text “Performance” (without quotes) in the Event Type column. Why use the text label? Mainly because it’s easier to read your import file. If you’re working with lots of related data and don’t want to keep consulting separate lists of event types and codes, use text labels.
Some other related data fields you may want to populate:
- Default Role
- Participant Listing
- Location Block ID (event location)
- Financial Type
- Parent Event ID
- Payment Processor
Pre-Import Step #3: Plan Your Import
There are ways in which an import can go wrong, and most of them are pretty harmless. For example, if there’s a problem with a specific value in your data, the import process will skip that row/record and import everything else. If there’s a problem with your data file itself, the csv Import extension will usually tell you that before it even starts importing.
Just in case something goes very wrong, though, here are some ways to be prepared:
- Perform a backup of your CiviCRM database. The import process is not reversible! Make sure you have a fresh database backup on hand before you start. (This is especially important if you’re using the importer to update existing records.)
- If possible, plan to import data outside of peak business hours in case you need to restore a database backup. Even if your backup is only a few minutes old, it won’t include data that users have added or updated in those last few minutes.
- The import process can be fairly resource-intensive. If you have a large number of records to import, consider breaking up your import file into smaller chunks. See how efficiently your system handles smaller batches of data before scaling up to larger import files.
Pre-Import Step #4: Test Your Import
Prepare a test file of just one or two records, and import those to confirm that your data is populating correctly. Follow the instructions in the next section to conduct your test import, and don’t forget to save your field mapping!
Import Time!
You’re ready to go. You have a fully-populated spreadsheet of event data. Generate your import file using “Save As…” or by exporting your spreadsheet to a .csv (comma-separated values) file.
To access the API csv Import extension in CiviCRM, go to “API csv Import” in the top navigation menu.
The import screen begins on step 1: Upload Data, and so will we.
Upload Data (step 1 of 4)
In this screen, you will upload your csv file and fill in some general settings.
- Entity to import: select Events.
- Import Data File: upload your csv import file.
- First row contains column headers: If you have been following this guide, this is exactly how your import file is set up, so check this box.
- Import Field Separator: since you have a .csv file, and csv stands for “comma-separated values,” your field separator should be a comma. Make sure there is a “,” (comma character, entered without quotes) here.
- Date Format: select the date format that matches the dates in your spreadsheet. Note: formats for date + time fields are not displayed here, but datetime values such as “10/17/2018 20:00” or “5/1/2018 6:30 AM” do import successfully if the appropriate date format is selected.
- Load Saved Field Mapping: If this is your first time using this importer, you won’t have a saved field mapping to select yet, so leave it blank. We’ll create a new field mapping in the next step, and you can save it then to reuse later.
Match Fields (step 2 of 4)
In this screen you’ll match your spreadsheet columns to the corresponding database fields in CiviCRM.
Here you’ll see a table representing your import file. This table may take a minute to get used to because it is basically your import file turned sideways, with your column headings shown in a list down the left side of the page. The next two columns on this page are the first and second rows of data from your import spreadsheet. (If this description is confusing, it will make more sense when you get to this screen and recognize your own event data.)
In the last column of this table, “Matching CiviCRM Field,” you get drop-down boxes, each containing a list of CiviCRM database fields to choose from. Select “- do not import -” for any columns in the import file that you want ignored.
Some tips on field mapping for events:
- The CiviCRM field names are listed in alphabetical order. Many are self-explanatory, but some can be tricky to find since they don’t always correspond to the labels you see in CiviCRM’s Manage Events screens. For example, in an event’s Fees tab in CiviCRM, there’s a checkbox labeled “Paid Event,” but that same field in this drop-down list is called “Is this a PAID event?” You will find it alphabetically under “i” for “is.”
- Be careful! The field mapping process may, by default, auto-select the Event Template Title field for the Event Title field, leaving the (required) Event Title field unmapped.
- There are three different “Footer” fields: Footer Message, Footer Text, and Footer Text. Which drop-down option goes with which event text footer field? We figured it out so you don’t have to:
- Online Registration > Registration Screen > Footer Text: Footer Message field.
- Online Registration > Confirmation Screen > Footer Text: Footer Text field #1. (It’s not labeled “#1” in the field mapping, but it is the first of two available fields labeled “Footer Text.”)
- Online Registration > Thank-You Screen > Footer Text: Footer Text field #2.
Save your field mapping. When you’re done, check “Save this field mapping” at the bottom of the page to save this mapping to use again later. TIP: Even if you don’t plan on reusing it, it’s helpful to save your work in case you get even one import error and you need to re-import to fix it.
Note about saved field mappings: They are based on the fields’ order of appearance in the import file. The first column of import data is mapped to the first field you select here in the Match Fields screen, the second column is matched to the second field you select, and so on. Keep in mind that it’s the order in which the columns appear in your import file, not the column names, that are saved and reused.
Preview (step 3 of 4)
This screen previews the results of your import. Check the number of rows of data in your import file (remember to subtract one, -1, if your first row contains column headings) and verify that the importer is reading the correct number of rows. If this is wrong, it suggests there’s a problem with your csv file or with the settings you chose in step 1 of the import process.
This screen also gives you a projected count of valid rows, but it is not a guarantee that you won’t run into errors in the next step.
Click ”Import Now” whenever you’re ready.
Summary (step 4 of 4)
Your import will be processed, and when it’s done you’ll see a summary of the results. If everything went perfectly, you’ll see “Import has completed successfully” followed by your row counts again.
If any part of your import was unsuccessful, you’ll see the following message: “CiviCRM has detected invalid data and/or formatting errors in one record. This record has not been imported.” Click the “Download Errors” link for a csv file containing details of the errors that were encountered.
The Import Errors file tells you the line number (excluding header row, if applicable) of the record(s) which failed to import, along with a Reason column detailing the error, followed by the record itself.
Some important notes about import errors:
- Unless the Summary page says otherwise, all records were imported except the ones listed in the Import Errors file. To import the remaining records, create a new import file that includes only the records which failed to import, correct the problems described in the Import Errors file, and perform the import process again from step 1 with the new csv file. (Repeating the process will be easier if you saved the field mapping in step 2.)
- If your import file and/or field mapping is missing a required field, you’ll get an error: “Mandatory key(s) missing from params array:” followed by the name of the missing field. For example, if you try to import a list of Events but omit the “Event Title” field, you’ll see the reason, “Mandatory key(s) missing from params array: title.”
- You’ll only see one error reason per failed record/row. If there’s more than one error preventing a record from being imported, the Import Errors file will only display the first error the process encounters. You won’t find out about other errors until you correct the first one and try the import again.
- Importing Events to CiviCRM - March 30, 2018
