CiviCRM Community Forums (archive)

Recently I imported our membership database into CiviCRM.  This is about 2300 main contacts with another 400 related organizational contacts and 800 spouses, relationships among all those as appropriate, plus 2300 memberships and about 8300 contributions.

Below is my list of notes and comments throughout the import process.

I'm posting them in hopes they may be of interest/help to others going through this same process.

There are 3-4 bug reports in there, some possible feature requests, and quite a bit of the rest I'd like to integrate the documentation on import in the the Wiki.

But in the meanwhile, here is the raw and unedited list:

NOTES/THINGS LEARNED DURING CIVICRM IMPORT

Make a DB backup of the entire CiviCRM SQL database before starting and between each step.  I just use phpmyadmin, click on the entire database then export all tables as SQL filed.

Contact subtypes (ie, Media Contact subtype of Organization), are CASE SENSITIVE.  If subtype doesn’t match an existing subtype, the import of that record fails.

Contact subtypes have some other issues importing (can't remember the details, sorry?)

Custom Data with multi-select etc is CASE SENSITIVE for import

Custom Data with multi-select if mismatches fails silently/appears to have imported by actually didn’t. 

Best to import Custom Data with ‘value’ rather than ‘label’ if the label is long & complicated, as the match will fail for some unknown reason, and no warning is given that the import of that data field failed.

Long Custom Data fields (ie, with a long 'value' field) don't match even if they appear to match (ie, copy/pasted from same source).  I am not sure the cause but it might be because of commas (,) in the value field.

If both external ID AND dedupe rule selected, the External ID overrules the dedupe rule match.  If the External ID doesn’t match and the dedupe rule does, there will be no match and the data will not be imported for that record.  However on the final import screen it will show those records as being imported in the final tally even though they are not. This might be expected behavior but #1 it is undocumented and #2 it creates some problems.  For example, how could you possibly update the external id? Suggestion:  Create an option on the initial import screen to ignore the the External ID if the dedupe rule matches.

In many cases it might be better to import the ID key from the external database to a specifically created Custom Data Field.  Then define a dedupe rule to control dedupe detection using this new Custom Data Field however you like.  In fact, you could create several custom data fields to hold the external DB keys for data from several different external databases if necessary.  This would have most of the advantages of the External ID without the disadvantages.  (The one disadvantage of this method is that it would be possible to create two different records with the same external database ID, which is not possible to with with CiviCRM’s External ID.  However it would be very easy to detect and merge these duplicates using dedupe rules if that situation occurs.)

Best to make a group AND tag for each import step, plus a group for the entire import. Can delete any unneeded groups or tags later.  I have been making one single group for the entire import project and a new/different tag for each import step.

Including groups/tags helps in an important way: Because the CiviCRM import stats report records as updated when they are actually skipped, the group/tag update is the only way to tell how many contacts have actually been updated or added by the import.

External IDs are helpful but they also cause problems.  Consider deleting all external ids from the SQL DB except the ones you know will match or are trying to match.

Doesn’t seem to be any way to import ‘deceased’ field  There is a deceased Date field instead.  But uploading a date to that field doesn't check the 'deceased' boolean checkbox.

Won’t upload the 2400 contacts in one go, 500 error, splitting it into 3 groups of 800.  Won’t do 800 at a time, splitting into groups of 400. PITA.

Yes/No (for custom data field) isn’t working apparently because an empty field is not acceptable for a boolean field.  Those records are silently skipped.  We’ll try adding 0 to all empty fields so they are 1/0.  Then Excel somehow formatted a few of them as dates so reset the formatting for that column to text.

For dates (custom data fields), empty fields are also not acceptable and the entire record is silently skipped.  Put something like 1/1/1900 in all empty date fields for custom data.

On the 3rd Import step, if errors are detected it gives a file you can download listing the records along with their error messages.  (However it is missing key errors like the custom field boolean & date can’t be empty, the multi-select match is case sensitive, the contact sub-type match is case sensitive, and possibly others.)  On the 4th step it again gives you a file to download if there are errors and certain records didn’t import. HOWEVER that file is missing many, many of the cases where the record did not upload.  That 4th step error file MUST include each and every record with an error or any other reason why the record wasn’t imported.  The 4th step file misses any errors involving custom data and some others.

---> Note that I created a patch to solve part of this problem, see http://forum.civicrm.org/index.php/topic,25031.msg110366.html#msg110366

Note that empty fields are acceptable for boolean & date fields in ordinary fields, for instance “Do Not Mail” (boolean) or Deceased Date.  It is only for Custom Fields that are boolean or date that the problem arises.

Deleted records in our external DB are causing problems when imported.  Often we ‘delete’ the records only because they are merged with another, or we accidentally entered the person twice, then merged the records, etc.  But when importing, if the deleted/inactive record comes up first then it overwrites the previous record and marks the member as inactive/do not contact.  Went through the membership file & checked all inactive members, removing those who had simply been merged with another contact.

On earlier steps I, when trying to import using a dedupe rule, had thought that External ID was controlling the matches and if External ID did not match but the dedupe rule did match, then the match was rejected.  However since then I have chose “no dedupe rule” and now CiviCRM seems to be matching contacts with similar name/email but DIFFERENT external ids!  Not sure if it is defaulting to a certain dedupe rule or what, but I had assumed that choosing 'no dedupe rule' meant just that.

Importing just the external ID and address custom field (address status for home address) results in nasty error.  Created a bug report.

Importing spouses (with external ID for relationship import) worked for 379 of 400 records but died a nasty death/pear exception halfway through #379.  Import of 2nd different batch with 363 records worked fine.

Memberships Import

When a large memberships import failed due to out of memory error it had actually processed many (thousands?) of memberships already.  So I ended up with many double memberships.

Cannot import memberships with start date in the future because that doesn’t match any membership status that is defined.  (We had some of those for people who had paid memberships ahead.)

Leaving out the membership status and just allowing the system to calculate it from the given start and end dates is the best procedure.  IF you specify a status and it conflicts with the membership status rules then that membership is simply not imported.

Contributions Import

About 8300 contributions to import, and 20 different categories in our Filemaker DB.

First set up Contribution Types and then use a lookup table to translate the previous filemaker donation type to the new CiviCRM Contribution Type.  Ended up with about half the categories in CiviCRM.

Went through & removed or fixed all blank payments.

Removed or explained all previous $0 contributions (we sometimes use them for courtesy memberships etc.).

Double checked that all External IDs will match actual CCRM contact External IDs (I have been changing a few External IDs here and there throughout the import process as we discovered dupes and various mistakes).  The was done by finding all contacts with a non-empty external id, exporting to a CSV file, opening with Excel, and then using vlookup to make sure each external ID in the contribution list actually matches one of the external IDs in CiviCRM.  A few didn’t and they were fixed.

Backed up the entire CIviCRM SQL database.

Decided to first try importing a group 100 contributions to see what problems will happen and to set up the import field map.

AND . . . this is where I discover that individuals & organizations must be separated & uploaded separately.  So, split the file into separate ind/org files and uploading the first 100 individuals.

I looked about contribution entry screens, contribution view and edit screens, and the civicrm_contribution SQL table to figure out which fields I needed and where available to match with fields from our previous DB

Added source code “Filemaker DB import 2012-09-16” so that this batch of imports can be identified and also traced back to their original source in the Filemaker DB

We have ‘donor’ and ‘honoring’ fields which are free-form entry and often used for general notes.  The donor or honorees, even when mentioned, aren’t often listed in easy first-last format.  So we’re just combining those two fields into one “Notes” field for import.  Oops, we don't have a field for line item detail either so we are combining all three of those fields into one 'notes' field.

Import our filemaker contribution record ID to the transaction ID field

Field “Amount Label” corresponds to SQL DB field amount_level which allows inclusion of item detail (as done with price sets in CiviCRM).

However, the contribution import DOES NOT create a line item record for the the import screen doesn’t seem to give any access to other fields in the civicrm_lineitem table such as qty, unit_price, line_total  (turns out lack of line item creation is a bug in CCRM 4.2--see below.  But lack of access to qty, unit_price, line_total, and particularly label fields is a missing feature.

After several rounds of working through step 2 of the import process with the first 100 contributions, then going back & reconfiguring the CSV file and input mapping to work better, we’re now going to first try it with just 3 contributions--one for each main type of contribution we use.

Good idea I did that because there were several potential problems with the first 3 and going back & deleting 100 would have been unpleasant.

Realized that if the external id system goes awry somehow and contributions are matched with the wrong contact, it won’t be easy to detect unless someone has all the old filemaker record IDs memorized.  So I added the member name & membership ID to the beginning of the Notes field for each contribution.  (So now FOUR fields are being combined into Notes.)

Adding fields for Thank You Date (same as receipt date, because from our previous DB we always mailed thank yous with receipts), Net Amount, and Non-deductible amount (calculated from total amount).

And . . .  it turns out that not creating a line item in civicrm_lineitem is a bug introduced in 4.2, http://forum.civicrm.org/index.php/topic,26000.msg109932.html  and http://issues.civicrm.org/jira/browse/CRM-10817#comment-45045 So that puts an end to our contribution importing for now. 

Oh, well . . .

UPDATE: I added many of these items to the wiki documentation here: http://wiki.civicrm.org/confluence/display/CRMDOC42/Importing+Data+-+Additional+Notes

I recently imported 16,000 contacts at a single import batch in one time on a powerful, private, dedicated server.

Wow. 

I think the limits are an unholy combination of apache/web server timeouts, php timeouts, and SQL timeouts with some memory limit problems thrown in there for good measure.  It's good to know that you can overcome all that with the right hardware and settings.

Flip side, imports are taking .74 seconds per record even on powerful, dedicated hardware.  There is probably some room for improvement there.  We often do mailings to 10-20K contacts--which ideally we would be importing and exporting from CiviCRM to track our contacts with them--and ideally that size of import or export would be a normal daily task taking maybe 5-10 minutes.

FYI when importing contacts, memberships, and contributions I've been using the attached VBA script in Excel to automatically chop the files up into the appropriate number of records and save them as CSV files.

It has a couple of little quirks (doubles up the header in the first file, sometimes doesn't save the last CSV automatically--you have to save it manually) but it saves a lot of time.
'customize by changing chunksize and the save filename below
'save in a 'module' in the VBA section of your Excel file
'run under 'macros' in the developer section of the Excel ribbon

Sub divide_file_into_chunks_by_row()

Dim lngLastRow As Long, lngReadRow As Long, wbWriteBook As Workbook, chunkSize As Integer

Application.ScreenUpdating = False

With ActiveSheet

lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row

'CUSTOMIZE HERE
chunkSize = 1000 ' how many rows to include in each file

For lngReadRow = 1 To lngLastRow Step chunkSize

Set wbWriteBook = Workbooks.Add

.Rows("1").Copy wbWriteBook.Sheets("Sheet1").Range("A1")
.Rows(lngReadRow & ":" & lngReadRow + chunkSize - 1).Copy wbWriteBook.Sheets("Sheet1").Range("A2")

'temporarily turn off alerts while saving as CSV so as to avoid driving the user crazy
Application.DisplayAlerts = False

'CUSTOMIZE HERE - file save name
wbWriteBook.SaveAs "MyImportFile-" & lngReadRow & "-" & lngReadRow + chunkSize - 1 & ".csv", xlCSV
Application.DisplayAlerts = True

wbWriteBook.Close

Next lngReadRow

End With

Application.ScreenUpdating = True

End Sub



Lots of ways the community can (and should) help: a list of ideas is in the book:

http://book.civicrm.org/user/current/the-civicrm-community/civicrm-community/ (last section: Open Source = Community Sourced)

with regard to the below a few thoughts:

1. Seems like there are a few things that can be fixed to improve the process. Would be good to spec it out, reproduce current behavior against desired behavior. Involve the folks on this forum topic and potentially a few others.

2. The next step would be to work on submitting a patch and unit/web tests to ensure that Civi does the right thing going forward.

The platform moves forward when folks contribute and "scratch their own itch". Fortunately for Civi, a lot of folks are contributing in various areas

lobo

My question - are there ways we (the civiCRM community) cn help to fix these challenges ?  Its' root causes appear two fold: 1) not allowing for matches even though the "proper" capitalization may not be equal; 2) not alerting users that their thousands of supposedly successful import rows resulted in only a few hundred actual updates.  Not sure what happened with the integrated testing stage on this feature  but I would personally be glad to help.

I think it would really help CiviCRM's growth if the import procedures were refactored and updated.

It would be a somewhat major job though and part of the trouble is, most of us only go through this once.  Once all our data is in and we're not doing the massive imports any more, interest naturally wanes.  But it's a huge hurdle for anyone who is transferring from another system and I'll bet many, many prospective CiviCRM users are turned away from adopting the system at the import stage.

Also, when I was going through the import process my impression was that the import system needed some pretty heavy-duty re-writing.  But from a more practical perspective, maybe a two-tiered approach:

 - short term, there are maybe 5 or 7 really serious problems that could be addressed by bug reports, patching, testing

 - longer term it might make sense to raise some $$$, enough to have someone knowledgeable about these things take a look at the whole system and see if there is a fundamentally better way.

NOTES/THINGS LEARNED DURING CIVICRM IMPORT

I'm returning all these many months later to add one note--that somehow in the contact import I ended up importing about 150 blank individual records and 3500 or so blank organization records.

I'm not quite sure how/why this happened--it could be that they were there in the Excel CSV file exports (sometimes when you delete rows in Excel they still show up as blank rows when you export to CSV) or some other reason I don't know about.  I started out with one master file, then deleted all org records to create the individual import and deleted all individual records to make the org import.  The numbers of blank records at least somewhat match up to those respective deletions, so that is probably the most likely source.  One reason this is strange is I don't remember seeing a report of import numbers nearly so large--if I'd seen "3500 contact imported" at the end of my organizational import, I definitely would have noticed that!  But it's possible that those blank records are created but not counted somehow.

I should say I'm not absolutely sure these blank records showed up as a result of the imports (I didn't turn on logging until after finishing up all the imports) but that's my best guess.

It's not a huge deal--I just used search builder to find all individuals and then all organizations with first/last/org name, email, and street address = NULL, and then deleted them.   But just something to be aware of if you're doing a large import.

Contact subtypes have some other issues importing (can't remember the details, sorry?)

Just one small point here, in case it trips up anyone else, as it did for me.
When you are importing a contact and you want to make it a subtype, you must remember to include CHAR(1) characters around the subtype value.  (You can do this in CSV files I am sure, otherwise you can run a SQL UPDATE afterwards to fix the string values.)
For example, in my case my subtype was "Membership_Data" but this has to be specified (in SQL) as
SET contact_sub_type=CONCAT( CHAR(1) , "Membership_Data", CHAR(1) )
Hope that helps.
Mark

Yes/No (for custom data field) isn’t working apparently because an empty field is not acceptable for a boolean field.  Those records are silently skipped. 

Seems I just hit this too - shall i flag this in JIRA? sidestepping problem worked for me but seems like something that would be worth fixing?