Church Database Conversion with Groovy

This past summer, my church switched from our existing church database to Fellowship One. And when I say "existing database", in actuality we had two different databases, Church Helpmate and NSpire Youth Assistant that we wanted to consolidate into the new system.

Church Helpmate is a Microsoft Access-based Windows application, and we used it for the general church population. NSpire is a Windows application with a custom database, and we used it for the youth and children's ministries. Somehow we had to get all of the families, peoples, addresses, phone numbers, email addresses, etc. from the two old systems into the new system, and we wanted to try to automate as much of the record merging as possible.

Fellowship One is a web application, and they helpfully provide an Excel spreadsheet template for the data import. This meant that I had to get the data out of the two old programs, massage and manipulate the data, merge duplicate records, and then write them out to the Fellowship One spreadsheet. Once the spreadsheet was fully populated, we would ship that off to Fellowship One and they would take care of the rest of the import.

To get the data out of the two programs took a little bit of work. I decided to export to CSV files. For Church Helpmate, adding the following macro to the 'CustomFrontEnd.mdb' file made it simple. Once this was run, every table in the database was dumped to the temp directory as a CSV file.

Public Sub ExportAllTables_to_CSV()                                           
  Dim obj As AccessObject, dbs As Object                                      
  Set dbs = Application.CurrentData                                           
                                                                      
  For Each obj In dbs.AllTables                                               
    If Left(obj.Name, 4) <> "MSys" Then                                        
      Dim strFolder As String                                                   
      strFolder = "c:\temp\"                                                    
      DoCmd.TransferText acExportDelim, , obj.Name, strFolder & obj.Name & ".csv", True
    End If                                                                     
  Next obj                                                                    
End Sub

NSpire provided a simple way to create a CSV export. By creating a custom report from the "All Households" and "All Individuals"reports in NSpire (with both Active and Inactive included), I was then able to pick out the fields I thought would be relevant.

Now I had to make a decision. Should I manually merge and import the data, or should I write a program to automate the work? At first blush, this didn't seem like a good candidate for automation, since it is a onetime deal. However, I decided that it was likely I would not get it right the first time (or second time), and because of that it seemed that having a repeatable process would be the best approach. I decided to write the conversion program in Groovy. This allowed me to harness all the power of the Java ecosystem, but in a more dynamic language with many Perl and Ruby-like language features (I am a big Groovy fan, in case you can't tell).

To parse the CSV files in Groovy, I used the opencsv library. With that, parsing is as simple as can be:

import au.com.bytecode.opencsv.CSVReader

CSVReader reader = new CSVReader(new FileReader(inputDir + householdCSV));
List<String[]> inputHouseholds = reader.readAll();

This code converts each CSV record into a String array. It was then easy to pull out the fields into various objects representing households, individuals, etc.

Once all the data was parsed from CSV, I wrote a lot of code to validate the data, and convert it into the format needed by Fellowship One. Some of the various tasks were to:

• Determining the person's position in the household (head, spouse, child) and marital status, and handling households where no head was specified.
• Converting dates into proper Fellowship One format.
• Looking at the comment field to get dates for various milestones like baptism, dedication, membership, etc.
• Determining the graduation class for children & youth based on their birthdate.

Once all the records from Church Helpmate and NSpire were parsed, I then had to merge the records. Here is the approach I used:

• For each person in NSpire, if they are a child or youth, see if they were already in the Helpmate data. If so, assume that the NSpire data is more up to date and accurate and lean towards using it over the Helpmate data when creating the F1 output. This means if both NSpire & Helpmate have an email address and they are different, I'll assume the NSpire record is correct
• If the person from NSpire is an adult, and they are in Helpmate, assume that the Helpmate data is more accurate when creating the F1 output.
• If the person from NSpire is a child/youth and they are not in Helpmate, find their parents in NSpire (using the household ID which ties all people from the same household) and see if either of their parents are in the Helpmate data. If they are, then add the child/youth as a member of that household when creating the F1 output.
• If the person from NSpire is an adult and is not in Helpmate, add them (along with their kids) into the F1 output.

Finally, once the data was merged and a final validation step was done, it was time to write out the data. I decided to write directly to the Fellowship One spreadsheet using Groovy's ExcelHelper class from the Scriptom library. It is not fast by any means, but it gets the job done and is simple to use. Here is an example:

import org.codehaus.groovy.scriptom.util.office.ExcelHelper

def write(limit) {                                                             
final outputFile = new File('../output/FT Standard Format.xls').canonicalFile
                                                                           
def helper = new ExcelHelper()                                             
helper.create(new File("../FT Standard Format.xls"), outputFile) {workbook ->
                                                                           
    def list = households.values() as List<Household>                      
    sortHouseholds(list)                                                   
                                                                           
    if (limit) {                                                           
        list = list[0..limit - 1] as List<Household>                       
    }                                                                      
    writePeople(workbook, list)                                            
    writeAddresses(workbook, list)                                         
    writeCommunications(workbook, list)                                    
    writeNotes(workbook, list)                                             
    writeAttributes(workbook, list)                                        
}                                                                          

Each "write..." method wrote to a separate worksheet in the spreadsheet. Here's what the writePeople method looks like:

private def writePeople(workbook, List<Household> list) {            
 def personSheet = workbook.Sheets.Item[2]                        
 def row = 2                                                      
 list.eachWithIndex {hh, i ->                                     
     println "P$i: ${hh.fullName}"                                
     hh.individuals.values().each {person ->                      
         personSheet.Cells.Item[row, 1] = hh.id                   
         personSheet.Cells.Item[row, 2] = person.id               
         personSheet.Cells.Item[row, 3] = person.position         
         personSheet.Cells.Item[row, 4] = person.title            
         personSheet.Cells.Item[row, 5] = person.lastName         
         personSheet.Cells.Item[row, 6] = person.firstName        
         personSheet.Cells.Item[row, 7] = person.nickName         
         personSheet.Cells.Item[row, 8] = person.middleName       
         personSheet.Cells.Item[row, 9] = person.suffix           
         personSheet.Cells.Item[row, 11] = person.statusGroup     
         personSheet.Cells.Item[row, 12] = person.status          
         personSheet.Cells.Item[row, 13] = person.subStatus       
         personSheet.Cells.Item[row, 15] = person.statusComment   
         personSheet.Cells.Item[row, 16] = person.envelopeNumber  
         personSheet.Cells.Item[row, 19] = person.gender          
         personSheet.Cells.Item[row, 20] = person.birthDate       
         personSheet.Cells.Item[row, 21] = person.maritalStatus   
         personSheet.Cells.Item[row, 23] = person.occupation      
         personSheet.Cells.Item[row, 24] = person.employer        
         personSheet.Cells.Item[row, 27] = person.school          
                                                                  
         row++                                                    
     }                                                            
 }                                                                
}

With this done, I was able to create a spreadsheet and send it off to the Fellowship One data conversion team. They were able to import the data for an initial data conversion, and then our church staff took a look at it to make sure everything looked ok. I was pleased when I got the word that it looked ok, and that they would skip doing a final data conversion and just do some minor cleanup manually.

Just recently I posted all of the source code for this project to Google Code. For those who are interested, you can download it at https://code.google.com/p/f1importer/