Part 3: Dealing with Errors (or: Causing Errors on Purpose)

This guide will help you with two situations: 

1. You've almost completed your CiviCRM contact import, but you've gotten an error message that says "CiviCRM has detected invalid data or formatting errors."
2. You are preparing your import file and you're not sure what values to use for some CiviCRM fields. 

First, let's start by looking at what CiviCRM does when it runs into an error during a contact import.

What an Import Error Looks Like

Here's an import file -- a contact spreadsheet, almost exactly like the one from Part 1, except this time I've intentionally added one invalid value: my second contact has a State/Province of "Not sure." CiviCRM will not accept this value if you try to type it into a State/Province field in the contact summary screen, and it's not going to accept it during an import, either.

First Name	Last Name	Email	Phone	Address 1	Address 2	City	State	Zip
Chris	Smith	chris@companyemail.com	123-456-7890	123 Address Street	Apt 106	Washington	DC	20001
Taylor	Green	taylor@homeemail.com	555-444-3333	456 Main St		Portland	Not sure	04101
Miles	Johnson	mjohnson@testemail.net	999-222-1234	789 First Avenue	Unit C	Portland	OR	97222

 

Sure enough: when I get to Step 3 of my import process -- Step 3, Preview, as described in Part 2 of this guide -- I get some messages telling me something is wrong: 

 

In the green message area toward the top, there is one message: 

CiviCRM has detected invalid data or formatting errors in 1 records. If you continue, these records will be skipped. OR, you can download a file with just these problem records - Download Errors. Then correct them in the original import file, cancel this import and begin again at step 1.

Click 'Import Now' if you are ready to proceed.

Then, below that in my import summary, I see another very similar message:

Total Rows	3	Total number of rows in the imported data.
Rows with Errors	1	Rows with invalid data in one or more fields (for example, invalid email address formatting). These rows will be skipped (not imported).  Download Errors
Valid Rows	2	Total rows to be imported.

Notice that CiviCRM is not going to do any importing unless I tell it to. It is giving me clear warnings that there are going to be errors. Now I have a chance to go back and fix them before anything is actually imported. Also notice that CiviCRM is offering to go ahead and import rows that do not have errors. If I choose to proceed, it will skip rows that have errors. (FYI: it will skip them entirely. So in this case, even though the error just affects the State/Province field, it's going to skip importing this entire contact because of the problem in this one field.)

Handling Import Errors

At this point, I have a choice:

1. Go back and fix my original spreadsheet. Then I can start the import process from the beginning, uploading my revised spreadsheet instead.
2. Go ahead and import some records now, and then create a new spreadsheet of just the records that had errors the first time. "Create a new spreadsheet" sounds like a lot of work, but it's not that bad. CiviCRM is about to hand us a CSV file that gets us most of the way there. Choose this option if you have a lot of errors, because it's going to be easier to fix them if you start from this new spreadsheet we're about to download.

Whichever you choose, you'll be doing at least one more import this go-round! This is a great example of why it's helpful to save your field mapping, as we discussed in Step 2 of Part 2.

Let's look at this Download Errors link, seen in both error messages on this screen. Clicking it gives me a CSV download, which I can open in my spreadsheet software. My entire CSV is just this:

Line Number	Reason	First Name	Last Name	Email (match to contact)	Phone	Street Address	Street Address	City	State	Postal Code
2	Invalid value for field(s) : State/Province	Taylor	Green	taylor@homeemail.com	555-444-3333	456 Main St		Portland	Not sure	04101

This spreadsheet looks very, very similar to the spreadsheet I just tried to import. There are some important differences, and those differences are what we're paying attention to now: 

1. My original import had three rows, or three contacts I was trying to import. This Import Errors spreadsheet only has one row. That's because this spreadsheet contains only records with import errors. In my example, that's just one row (or one person).
2. There is a new column here (spreadsheet column A) called "Line Number," and below that it's telling me my import error is Line Number is 2. It is referring to the 2nd contact in my import file. (In my original spreadsheet, this actually displays as spreadsheet row number 3, but that's because the first row in my spreadsheet contains column headings.) In my example, this isn't crucial information: I only had 3 contacts to import and it's pretty obvious which one is shown here. But if you're going to fix your original spreadsheet, and that spreadsheet has got hundreds or thousands of contacts, these line numbers are really helpful. 
3. There is one more new column here (spreadsheet column B) called "Reason." It is telling me specific information about the error encountered for this contact. Here, it says "Invalid value for field(s): State/Province." Since I tried to import someone with a state called "Not sure," this is exactly what I expected CiviCRM to tell me. 

The rest of this spreadsheet is the same as this row of my import file: it has the First Name, Last Name, Email, Phone, etc. 

If you're going to fix your original spreadsheet and then import everyone at once, go ahead and do that now. Use this spreadsheet to help you find errors in your original. Make whatever changes are needed, then export a fresh CSV. In CiviCRM, go to Contacts > Import Contacts to start at the beginning. Upload your newly revised spreadsheet. When you get to Step 3, Preview, you may see some new errors, but at least it won't be the errors you've just fixed! 

If you already imported some contacts and now you just need to import the ones that got skipped: I'd said CiviCRM was about to hand us a spreadsheet we could use to re-import just the rows with errors. Here it is! We can use the Reason column to help us fix all the errors in each row. Go ahead and do that now. Watch out for one thing: because of these two new columns CiviCRM added, this spreadsheet won't exactly match the field mapping you created last time. That's OK! Once you've fixed your data, delete new columns A and B, "Line Number" and "Reason." Now this spreadsheet has the exact same columns as your original one, which means it'll work fine with your saved field mapping. Now export it as a new CSV. In CiviCRM, go to Contacts > Import Contacts, and start importing. Upload your new CSV, the one you've just fixed, which contains only the records that got skipped previously.

What if I Don't Know what to Fix?

In my example, I know exactly what to fix: CiviCRM needs a valid State/Province for this one contact. I put "Not Sure" and of course CiviCRM doesn't like that. 

What if it's not so obvious? What if CiviCRM is telling you there's an invalid value somewhere, but you have no idea what it wants instead? Luckily, there are some tricks you can use to figure it out. 

Edit the field in CiviCRM

Pull up an existing contact in CiviCRM. Click to edit the field in question. Try entering the same value you've got in your spreadsheet, the one causing an error. Sometimes you can get more information this way about what CiviCRM does and does not allow. 

Just in case CiviCRM does let you save your attempted edit: do not perform this test by overwriting real data on a real contact. Use a fake or test contact you've already got in your database, or experiment on your own contact record. 

Select fields (multiple choice fields)

Mostly you'll get errors related to select fields (radio options, drop-down/select fields, etc). When this happens, it's because you're trying to import an option that's not already in CiviCRM. 

If your spreadsheet has options that aren't already in CiviCRM, but should be: add them! Configure the necessary options in CiviCRM. See CiviCRM documentation: Creating Custom Fields for help changing options on a custom field in your database. See CiviCRM documentation: Customizing the User Interface for help changing options on CiviCRM's built-in fields. You can safely add options to some built-in contact fields such as Gender, Prefix, and Suffix. 

If your spreadsheet's options already are in CiviCRM, but you're still getting an error: the most likely explanation is a typo in your spreadsheet values. Check them really carefully. You can replace your spreadsheet values with a copy & paste right out of CiviCRM to make sure.

You can always choose not to import a particular value. Maybe you have a contact who's using a name suffix that's not used by anyone else in your database. If you don't want to add a new suffix to your system, you can always clear out this field in your database and do the import without that person's suffix. (They may not appreciate it, of course! That's between you and them.) 

You may have seen that each select option in CiviCRM has both a Label and a Value. Most of the time, you can use the label or the value in your data file, and CiviCRM will handle it just fine. If you're still having trouble, try switching: change your spreadsheet data so it uses an option's value instead of the label (or vice versa).

If you find yourself with a lot of data transformation to do, check out the tip under Other Resources at the end of this article.

Checkbox fields

To import values for a checkbox field, your spreadsheet should say "Yes" to check the checkbox. For an unchecked checkbox, you can import "No" or just a blank (empty spreadsheet cell). 

Date fields

I mentioned back in Part 1 that the CiviCRM importer is pretty forgiving with date formats. If you do encounter trouble with a date field, double check that your spreadsheet's date format matches the format you've chosen for your import. You can't import incomplete dates, such as just a year with no month and day. You also can't import any non-date information such as text. Basically, if you can't type a particular value into a date field in CiviCRM itself, then you can't import that value into a date field with the importer. 

 

Causing Errors on Purpose

When you're setting up a spreadsheet for your first import, there might be a lot you're not certain about. That's OK! Find out what CiviCRM will accept by just trying it and seeing what happens. You can take advantage of the fact that CiviCRM will report on errors before actually adding or modifying any data. 

 

Create a very small import file just for testing purposes, similar to the 3-person file I used throughout this guide. For example, say you're not sure what type of Country values CiviCRM wants. You can create a spreadsheet of a few sample contacts. Give one of them a Country "US," one "USA," one "United States," etc. Run an import process up to Step 3, Preview. If one or more of your tested options throws an error, well, there's your answer! 

Of course, you can do this same kind of testing with a real import file too. But a real import process usually involves miscellaneous other errors: invalid email addresses, typos, and other messy problems that show up real data. By testing with an ultra-short and simplified list, you can focus just on the fields and options you have questions about. 

You can also finish a test import to confirm how CiviCRM will handle something more complex, such as linking to other types of data. Try an import with just one single contact record. If the results aren't what you hoped, it is easy to find that contact and fix it -- or delete it entirely, if it was a fake contact to begin with.

Final Tips

How long does an import take?

This guide is long, but the import process itself does not have to be! If I've got a procedure that I know gives me a valid spreadsheet to import, and I've already got the necessary field mapping saved in CiviCRM, an import process takes me less than 30 seconds. Your first import (and probably your second and third ones) will involve a lot of trial and error, but it'll get quicker each time. 

Be careful

You can't undo an import. If there's anything you're uncertain about, test with with a small set of contacts that you can easily find and fix if something goes wrong. 

Even after you've tested and found answers to anything you weren't sure about, you may still want to create a small batch for your first real import. If you have 1,000 contacts to import, you don't have to pull them all on your very first try. You can extract the first 10 contacts from your big list, create a separate spreadsheet with just those 10, import them, look up those 10 in CiviCRM after the import to verify they're OK, and move on to bigger lists as you feel more confident. 

As I mentioned in Part 2, it's a good idea to have a recent backup of your database. This is always a good idea, just in general. 

Other Resources

CiviCRM detailed documentation on imports is available here. 

This guide has focused on importing contacts to CiviCRM. You can also import activities, event registrations, memberships, and contributions. These all follow the same basic principles. You can also import relationships via a process described in the official documentation. 

There are several extensions that support importing other or more complex types of data. I've used the intimidatingly-named API csv Import GUI extension successfully in a lot of different situations. Many other types of imports get pretty technical pretty quickly. (It turns out there is a reason they're not supported by core CiviCRM!) 

Last but not least: your external data source may give you data that doesn't match up exactly to the labels or values in CiviCRM. For example, maybe you're exporting data from another system where all the Country codes are just a little bit different from CiviCRM's. If it's a one-time import, use your spreadsheet software to update values with Find & Replace. If there are just a few different values to clean up...use your spreadsheet software to do Find & Replace. If you've got a lot of different values to replace, and you need to do it again and again, check out the VLOOKUP function. It works in both Microsoft Excel and Google Sheets. If you're not an Excel wizard, you may want to find someone who is and have them set it up for you the first time. 

 

< Previous: CiviCRM How To: Importing Contacts, Part 2                   Using the CiviCRM Entity Module: Next >