July
13

Tasked with importing thousands of records from a different product into CiviCRM software, I dragged my feet. I was not looking forward to the learning curve and time required to fully understand the import procedure and how to do it error free.

To test and clean the data without messing with the live CiviCRM installation, I created a separate Drupal+CiviCRM installation to test on.

If your data is unique or complex, importing will not be easy, regardless of the type of software. I was supplied a CSV export file, that I edited in Microsoft Excel and imported into CiviCRM 3.1.4. I could write an entire chapter on cleaning the data and planning the data import, so I will not enter into that here. In short, about the CiviCRM Import Contacts feature I found:

Likes:

  • Custom data fields, with multiple values for each field, can be imported in one step from a CSV file.
  • You can create a group for each set of imported data, which allows you to view or delete the data you just imported.
  • Relationships between and Organization and a Person can be made easily enough, using matching rules.

Dislikes:

  • The speed at which contact imports occur is slow.
  • The fragility of the import procedure.

Now, more about my dislikes. Regarding the import speed, I'm sure in subsequent versions of CiviCRM this will be improved. What CiviCRM lacks in import speed it makes up for in the vast amount of import features, such as the saving of import field mapping, creation of a group on import, the checking for data errors, and checking for duplicate records.

Regarding the fragility of the import procedure, there could be some trial and error required in order to get your data to import just right. I will address some specific problems I found that are not so straightforward to discover.

1. Strange characters

The result of this example of a "strange" character was that CiviCRM stopped the import of the file on the line directly before this row. Suggested fix: change the character in your data to something acceptable.

CiviCRM import does not like strange characters

2. Rows missing fields

For some reason some rows in the data I was given was missing cells at the end of rows. Excel will not give you any indication the cells are missing, and it took a trick to get Excel to recognize and save them. You will only see the missing fields if you open the CSV file in a text editor, like TextPad, you will see:

CiviCRM import does not like missing fields

The absence of commas at the end of the last two lines shows indications that fields are missing in your CSV file. To fix this, I created a new column in Excel named "Junk", and inserted a space into each cell in that column (use the fill down feature to do this quickly). When importing data in CiviCRM, simply have it ignore this last "Junk" column. In this way, every row in the CSV file will contain the same number of fields.

CiviCRM import, add a junk column

3. Importing Country and State values

This was a frustrating bug until I found a working method. Organizations that should be located in the United States were importing as "Iraq" or "Suriname". If a row being imported had a State or Province, then the Country value was being ignored, and the State of some other country was selected. I found to combat this error, you can place the Country column of your import file before the other address columns.

CiviCRM address columns

With these three odd peculiarities with the CiviCRM Import Contacts feature explained, hopefully this will help others who have similar errors. If it has helped you, or if you have some more tips, your comments are welcome.

1