Joined: 17 Mar 2009
Location: Liverpool, NY
|Posted: Sat Jan 31, 2015 1:52 pm Post subject: Using Spreadsheets for Genealogical Research
|Most computer users these days are familiar with using spreadsheets programs, such as Microsoft Excel. They provide a convenient way to filter, sort, and search lists of data. Therefore, putting data into a spreadsheet format can be useful in genealogical research.
An example is the church records posted on another page of this website. It provides a compact listing of the data contained in those records. One can quickly switch between various types of data, such as baptisms (or births), weddings, or deaths. When researching a particular family, one can filter a worksheet to display only the records for a particular surname to reduce the number of entries that need to be searched. By switching between worksheets, such as baptisms and confirmations or weddings, one can often deduce from the date difference whether a person with the same name in the two worksheets is the same person. The death worksheet sometimes provides notes indicating whether a given person was the child or spouse of another person, helping to put together family groups.
People usually record their genealogical research with a computer program, such as Family Tree Maker, RootsMagic, etc. To show this research to other people, one way is to send them a file in that programís native format and the recipient must in turn have the same program to read the file. Another approach is for the sender to convert his/her data into a GEDCOM file format. (All major genealogy programs have a file export feature to generate a file in this format.) A GEDCOM file can be read by other genealogy programs, but it is not easily interpreted in a standard word processing (text based) program, like Notepad. However, there is another type of software that can be used, namely a GEDCOM to spreadsheet file conversion program.
A freeware program that does this conversion is GEDxlate, available at http://www.gedmagic.com/gedxlate.htm. [Note. This program can also be used to create MS Access database files or text files in CSV (comma separated variable) format.] There are limits on which fields in the genealogy program and GEDCOM are translated. Fields that can be variable in number, such as sources and notes are not converted. The number of spouses is limited to three. If there are more, only the first, second and last are transcribed. Many of the less commonly used fields are omitted, such as occupation or source of death. However, GEDxlate does create a file with all of the major fields that one normal uses. Using GEDxlate is very straight forward. One simply loads the GEDCOM file, checks a series of boxes to select what values to extract, and then clicks on a button to start the translation.
Other limitations are that places are stored as a single string which depends on how much information was stored. This mixture of country only, state and city, etc. makes it impossible to filter on one state only, for example. Another more serious limitation is that dates are also stored as a single string, making it impossible to sort or filter on date. [This was done because some dates are approximate and entered as About, Before, After, or Between.] Fortunately, a rigid format is used in day, month, and year order, i.e. dd mmm yyyy notation with possible 3 character approximations at the beginning, but this cannot be sorted by Excel or other spreadsheet programs.
I developed a modification that can be added to the spreadsheet that separates the birth and death dates into separate year, month, and day columns as well as adds an Age column. This allows one to sort or filter on one of those two dates or sort by age. This modification can be downloaded from GEDxlate_additions.xls. The steps required to modify the spreadsheet are a bit lengthy, so instructions to update the GEDxlate generated spreadsheet can be downloaded from GEDxlate modification instructions. To illustrate this capability, I have also posted links to some family tree files on the Genealogy page of this website.
Free spreadsheet program
If you do not have a version of Excel on your computer, you can download the freeware version of OpenOffice (Calc) to generate, read and manipulate these files. The Windows version can be found at OpenOffice Windows and the Mac OS X version at OpenOfficeMac. (Wait a while for the Save box to pop up automatically.)