CiviCRM Bulk Importing of Batches and Associating Contributions

Starting with version 4.2 and 4.3,  CiviCRM has incorporated sophisticated financial batch capabilities.  However, for organizations switching from another CRM or financial system with batches,  there is currently no capability to import batches or associate contributions with batches through the standard CiviCRM import process. (Hopefully this capability will be added.)  This means that one must either manually create batches and manually associate contributions to batches or load the batches and then create the association through database inserts. For organizations with a lot of data, the only real option is to take the database insert route.

If you don't have solid database skills, I would not try loading this data, I would find someone with a good understanding of the CiviCRM data model  - like your friends at BackOffice Thinking :)

If you want to try it, here's how we recently loaded the contributions and batches for an organization with about 100,000 donations and 10,000 batches.....

Note:  There are certainly ways to automate this if you wish, by loading the contributions and batches into a table and then use standard set operations or do direct database loads from the old system - but if you are only doing this once, the work to make it more efficient won't be worth it.  You could also use the API, but the API does not allow for any bulk uploading.

Steps:

1. Load the individual contribution data through the standard CiviCRM import process.  Using the standard import process ensures that all of the subsequent tables (financial_trxn,  line_item, and financial_item) are populated as well.   When loading, ensure that (1)  you load the associated batch ID from the legacy system in an associated custom data field (we created a custom data field called legacy_batch_id) and (2) you create a unique transaction_id (we used the unique ID from the legacy CRM).
    
2. Load the batches into civicrm_batch through an insert statement.  Since we previously loaded the legacy CRM database into the VM we were able to do a direct insert statement into civicrm_batch.  You can also create a table with the batch data and insert from that table as well.  Either way you do it, it is important to keep the leagcy system's batch_id so that the contributions can be associated with it later. (We inserted the batch_id from the legacy system as the last part of the civicrm_batch_name field.)
    
3. The final step is to associate the contributions to the batches in the civicrm_entity_batch table.  This is a bit trickier.
   • The entity batch table has 4 fields
     • id (an auto increment field)
     • entity_table --> the associated entity table is populated from the civicrm_financial_trxn table, not the civicrm_contribution table as you might think if you are not familiar with the database changes in 4.3
     • entity_id --> the appropriate id from the civicrm_financial_trxn table
     • batch_id --> the appropriate id from the civicrm_batch table
        
4. • Now you can then successfully insert the proper transaction/batch associations into the civicrm_entity_batch table.  Please note that:
     • you are inserting the civicrm_financial_trxn.id into the entity_id field
     • the civicrm_contribution table and the civicrm_financial_trxn table are joined through the trxn_id on both tables
     • the civicrm_batch table must be joined to the civicrm_contribution table associated custom data table that contains the legacy batch id.

Can't wait for this to be easier, but for now this will work and isn't too hard.  Often times the hardest part is figuring out the the other CRM's data structure :)

Good luck and I'd love to hear if this works for you.