Saturday, November 27, 2010

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:

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:

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>

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] =
personSheet.Cells.Item[row, 2] =
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] =

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