The best way to import data into GEDitCOM II is through a GEDCOM file. But, sometimes you might find genealogy data in a spread sheet file that cannot be converted to a GEDCOM file. You could always manually enter those data directly into a GEDitCOM II file, but if the file is large, that task might become overwhelming. This extensions provides an alternative. The process is to label the columns of the spread sheet to identify them with standard GEDCOM tags for an individual. This extension will then create a new individual record for each row in the spread sheet and convert any data found in columns for that row into GEDCOM lines for that individual. If you define the columns correctly (as explained in more detail below), this extension can import all data in the spread sheet into a GEDitCOM II file in one step.
The first goal is to import spread sheet data into new individuals in a new GEDitCOM II document. The task of linking individuals in family records is discussed below. The process for importing individuals is:
- Edit the spread sheet document using any convenient software (e.g., Excel or Numbers, but see details on these or others below)
- The first row of the spread sheet must contain special labels needed by this extension to create GEDCOM data. Start by inserting a new row number 1 into the spread sheet. Each cell in that row must list GEDCOM tags corresponding to the data in that column. Some examples are NAME, SEX, BIRT.DATE, BIRT.PLAC, etc. In other words, give the GEDCOM tag for level 1 data or give tags connected by periods for level 2 or higher data.
- The column header tags must be unique meaning you cannot, for example, import two census events by having two columns labeled "CENS". The second one will overwrite the first one.
- Each subsequent row of the spread sheet should have data for one individual (the individual's name should be in a "NAME" column). Fill in columns with known data for the GEDCOM tag corresponding to that column. If that individual does not have that GEDCOM data, leave the column blank.
- When done, save the file as either a tab-delimited text file (a ".txt" file) or as a comma-separated-values file (a ".csv" file). See below for a sample spread sheet ready for import and some details on editing tools.
- Run this extension to import all individuals, but if you want family links too, see the the next two sections before importing the individuals.
Creating family linkages from text data is much harder (which is one reason people use genealogy software). But this extension can create family links if columns are set up according to the following rules. The first family task is to link two spouses into one family record:
- Add a column labeled "FAMS.spse". For each person that has a spouse, enter the line number in the spread sheet for their spouse (Excel and Numbers display line numbers to help in this step). In this numbering, the line of column headers is line 1 and the individuals begin on line 2. Note that the spread sheet can have only one "FAMS.spse" column, which means this import tool can only handle one spouse for each individual. To have multiple spouses, include the other spouse in the import and link them in GEDitCOM II after the initial import is complete.
- Add a column for "SEX" before the "FAMS.spse", which is needed to correctly assign spouses as husbands or wives in family records. This column should have "M" or "F" for each individual.
- To create a same-sex couple, simply link two individuals with the same sex in the "FAMS.spse" column.
- To have a single spouse family (i.e., family where one spouse is unknown), enter "0" in the "FAMS.spse" column of the one known spouse.
- To enter additional information about these families, add columns with data for the family record such as "FAMS.MARR.DATE" and "FAMS.MARR.PLAC" for marriage data and place. These data only need to be in the row for one of the spouses and must come after the "FAMS.spse" column.
- Run this extension to import all individuals and create all needed family records to link entered spouses, but if you want to link children too, see the next section before importing
The second family task is to link children to their parents:
- Add a column labeled "FAMC".
- For each person with known parents, enter the line number in the spread sheet for either one of their parents. In this numbering, the line of column headers is line 1 and the individuals begin on line 2.
- Run this extension to import individuals and all entered family links.
This sample spread sheet can import selected descendants of Queen Elizabeth II to a GEDCOM file. Note a couple of individuals have multiple spouses. This spread sheet has all spouses, but the import can only create one spouse for each individual. Note also that slashes (e.g., "/Surname/") could be used to designate unusual surnames but none were needed in this example. See below for some details on editing such spread sheets.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | NAME | SEX | BIRT.DATE | BIRT.PLAC | TITL | FAMS.spse | FAMC |
| 2 | Sophie Rhys-Jones | F | 20-Jan-1965 | Oxford, Oxfordshire, England | Countess of Wessex | 3 | |
| 3 | Edward Antony Richard Windsor | M | 10-Mar-1964 | Buckingham Palace, London, England | Earl of Wessex | 2 | 22 |
| 4 | Sarah Margaret Ferguson | F | 15-Oct-1959 | Marylebone, London, England | Duchess of York | 5 | |
| 5 | Andrew Albert Christian Windsor | M | 16-Feb-1960 | Buckingham Palace, London, England | Duke of York | 4 | 22 |
| 6 | Timothy Laurence | M | 1-Mar-1955 | Camberwell, Southwark, London, England | 8 | ||
| 7 | Mark Anthony Peter Phillips | M | 22-Sep-1948 | Tetbury, Cotswold District, Gloucestershire, England | |||
| 8 | Anne Elizabeth Alice Windsor | F | 15-Aug-1950 | Westminster, London, England | Princess Royal | 6 | 22 |
| 9 | Camilla Parker Bowles | F | 17-Jul-1947 | London, England | Queen Consort | ||
| 10 | Lilibet Diana Mountbatten-Windsor | F | 4-Jun-2021 | Santa Barbara, CA, Santa Barbara, USA | Princess | 13 | |
| 11 | Archie Harrison Mountbatten-Windsor | M | 6-May-2019 | London, England | Prince | 13 | |
| 12 | Rachel Meghan Markle | F | 4-Aug-1981 | Los Angeles, Los Angeles, California, United States | Duchess of Sussex | 13 | |
| 13 | Henry Charles Albert Windsor | M | 15-Sep-1984 | Paddington, London, England | Duke of Sussex | 12 | 20 |
| 14 | Louis Arthur Charles Windsor | M | 23-Apr-2018 | London, England | Prince | 18 | |
| 15 | Charlotte Elizabeth Diana Windsor | F | 2-May-2015 | London, England | Princess | 18 | |
| 16 | George Alexander Louis Windsor | M | 22-Jul-2013 | Paddington, London, England | Prince | 18 | |
| 17 | Catherine Elizabeth Middleton | F | 9-Jan-1982 | Reading, Berkshire, England | Princess of Wales | 18 | |
| 18 | William Arthur Philip Windsor | M | 21-Jun-1982 | Paddington, London, England | Prince of Wales | 17 | 20 |
| 19 | Diana Frances Spencer | F | 1-Jul-1961 | Sandringham, Norfolk, England | Princess of Wales | 20 | |
| 20 | Charles Philip Arthur Windsor | M | 14-Nov-1948 | Buckingham Palace, London, England | King Charles III | 19 | 22 |
| 21 | Phillip Mountbatten | M | 10-Jun-1921 | Isle of Kerkira, Mon Repos, Corfu, Greece | Duke of Edinburgh | 22 | |
| 22 | Elizabeth II Alexandra Mary Windsor | F | 21-Apr-1926 | London, England | Queen Elizabeth II | 21 |
You can prepare the spread sheet for importing with any spread sheet software or even any text editor. The two most common spread sheet applications for Mac users are Excel and Numbers. Both of these applications have idiosyncracies that complicated importing, but this extension was written to support them. You can probably use other applications as well. Here are some details:
Excel's indiosyncracies are that the saved file puts quotes around many cells (which is not necessary in tab-delimited files) and it uses odd line ending characters (either Windows-style carriage return and line feed or old Mac style carriage return only). To solve these problems, this extension will remove quotes and will look for out-of-date line endings. The only consequence of quotes will be that if you put your own quotes in cells, they might be removed too. Fortunately, this should be very uncommon for imported data, except maybe for a NOTE tag.
Another issue with Excel has limited support for unicode text. If you need names or places with characters not available in Excel, you might need to switch to other software.
Finally, Excel often tries to reformat any text that it thinks is a date. Sometimes it might pick a format that loses date information (e.g., truncate years to final two digits such as "1745" to just "45"). Make sure data in columns for dates are formatted to a date style that GEDitCOM II can recognize.
Numbers' indosyncracy is that is exports using Windows line endings or carriage return and linefeed on each line. This extension was written to watch our for Windows line ending characters.
When using "CSV" files, cells that contain commas have the be quoted. This extension looks for and removes those quotes. As a consequence, if you use quotes in cells, they could disrupt the import. Fortunately quotes should be very uncommon for imported data, except maybe for a NOTE tag. Furthermore, even if you use them, you should be OK as long as all your quotes are in matched pairs.
Another Numbers indiosynracy is that it exports all cells including empty columns and rows beyond the last row and column with data. This extension will ignore those empty cells, but the import will be more efficient if you delete unneeded rows and columns before exporting.
Most other spread sheet software should work as well. The only requirement is that the application allows you to save the data as either a tab-delimited file or a comma-separated-values file. Furthermore, those files hopefully will not have some new idiosyncracies that disrup the import.
You can even prepare data in any text editor (e.g., Apple's TextEdit, BBEdit, MS Word, or Pages). In text editing tools, you must manually separate columns by tabs or commas (but not both in the same document). When doing family links, you will need some method to find the line numbers for spouses or parents. When done save as a plain text file and then import using this extension.
Hint: If quote marks are in issue in Excel or Numbers, one way to avoid that problem is to prepare the document in Excel or Numbers, but when ready to save, copy and paste all cells into a plain text document (e.g., in Apple's TextEdit or BBEdit) and save from that application rather than from Excel or Numbers.