and RootsMagic 5 #ancestrycom #sources #update #gedcom

Problems with GEDCOM

In my experience, building an evidence-driven Family Tree on
from evidence found in its databases is faster and easier than extracting
the evidence from and building the documented tree in
RootsMagic. But my RootsMagic database remains superior in other respects
and is my master database for reporting. So I have a number of problems
marrying my Ancestry and RootsMagic family trees and synchronizing them:

  • excessive exception report from the GEDCOM import
  • no images downloaded and linked
  • verbose footnotes

Avoid Glut of
Unknown Info _APID on Import

When you import into a RootsMagic database a GEDCOM downloaded from an
Ancestry Family Tree, RootsMagic generates an exception report file with the
.LST extension and with the same name and the same location as the database.
It is filled with a glut of exceptions of the form:

Unknown info (line 28)
    3 _APID 1,8944::212

These obscure the more interesting exceptions. Since RootsMagic does not
import these _APID lines, deleting them from the GEDCOM prior to import is
harmless while clarifying the LST resport. I use this regular expression
global search and replace in PSPad to clean them out:

Find: ^.+_APID.+$

For the tree that is the subject of this page, it deleted 4461 occurrences
of the _APID tag from the GEDCOM.

Source and Other Images from

The GEDCOM that you download from an Ancestry Family Tree does not contain
any images but it does include a URL to the Source page for each citation
that RootsMagic imports into its Citation Comments field. Conversely, Family
Tree Maker 2012 can synchronise with the Ancestry Family Tree and downloads
all the images; however, it is a bloated, slow program and does not include
the Ancestry source page URL in its export to GEDCOM 5.5 despite providing
the path to the local image file. Importing both GEDCOMs into a common
database and merging duplicate persons sounds plausible and easy but proved
useless. To re-marry the URLs
and the media items in a RootsMagic database, I developed the following
merging procedure which appears to be about 95% successful.

  1. Import the GEDCOM from the Ancestry Family Tree (optionally cleaned of
    _APID tags) to a new RootsMagic database named “AncestryGED.rmgc” (or
    whatever suits you).
  2. Optionally, inspect the corresponding LST file and make any changes in
    the RM database that you feel are important. However, these may be lost
    as this database is secondary in the merging process that follows.
  3. Using Family Tree Maker 2012, create a FTM database by synchronising
    to the Ancestry Family Tree.
  4. Export from FTM a GEDCOM 5.5, choosing FTM 2012 as the output format.
  5. Import the FTM GEDCOM into a new RootsMagic database named to suit
  6. Optionally, inspect the corresponding LST file for exceptions and make
    possible corrections in the RM database. This will be the primary
    database in the merge so these corrections should be preserved.
  7. Open SQLiteSpy with the fake RMNOCASE extension and open the primary
    database, the one created from importing the FTM2012 GEDCOM.
  8. In SQLiteSpy open the following SQL file which you will have
    downloaded to your scripts folder. MergeAncestryURLsToFTM.sql
  9. Edit the ATTACH command line so that the path and filename corresponds
    to that of your secondary database, the one created by importing the
    GEDCOM downloaded from
  10. Execute the script. It is slow because of the many matches and string
    processing to be done and the final status report is misleading as it
    only reports the number of records converted from Name citations unused
    by RM to Personal ones.
  11. Inspect your primary database to see that your citations now include
    the URL to the Ancestry source page in the Detail Comments and that the
    media item, if it existed before, is still there.
  12. You may now proceed to Shrinking
    Verbose Master Sources from

The media are all stored in one folder under the Documents > Family Tree
Maker folder (assuming a default installation). Should you relocate them,
you will need to use RM’s global Search and Replace on multimedia filenames
or its Repair Broken Media Links tool to restore connections. I’m unsure of
the implications for the longterm if you wish to continue terr-building and
synchronising on Ancestry. Will existing names be preserved? Note that a
typical name is 1891 England Census-14.jpg or
1900 United States Federal Census-2.jpg, i.e., the source name with
a serial suffix when there is more than one image from the master source.
I’m inclined to leave them where they are, names unchanged, in the hope that
FTM will download only new images and continue the serial numbers.

Verbose Master Sources from

One problem is that citations from Ancestry are extremely verbose. So I
tackled this one first. Having examined both the GEDCOM from Ancestry and
the imported results in RootsMagic, I elected to make the revisions
post-import as that had a more obvious solution for the tools and knowledge
I have.
My strategy is to replace the RootsMagic Footnote field value with the
ShortFootnote field value and the latter with the Master Source name. The
Bibliography value remains untouched and provides the same detail as the
original Footnote. Below is a comparison of a revised Master Source with its
original. Note that both exhibit a truncation that is the fault of

Source imported from

1870 United States Federal Census (Online publication – Provo, UT, USA: Operations, Inc., 2009. Images reproduced by
FamilySearch..Original data – 1870 U.S. census, population schedules. NARA
microfilm publication M593, 1,761 rolls. Washington, D.C.: National
Archives and Recor)

1870 United States Federal Census

1870 United States Federal Census. Online publication – Provo, UT, USA: Operations, Inc., 2009. Images reproduced by
FamilySearch..Original data – 1870 U.S. census, population schedules. NARA
microfilm publication M593, 1,761 rolls. Washington, D.C.: National
Archives and Recor.


Source after SQLite procedure

1870 United States Federal Census

United States Federal Census

1870 United States Federal Census. Online publication – Provo, UT, USA: Operations, Inc., 2009. Images reproduced by
FamilySearch..Original data – 1870 U.S. census, population schedules. NARA
microfilm publication M593, 1,761 rolls. Washington, D.C.: National
Archives and Recor.



This involves multiple steps and tools:

  • SQLiteSpy
  • PSPad or other text editor with sorting capability
  • MS Excel or, possibly, other spreadsheet program, using AncestrySourceConverter.xlsx

The steps comprise:

  1. 1. Download the Ancestry GEDCOM or export it from the synchronised
    Family Tree Maker database in GEDCOM for FTM16 format.
  2. 2. Import the GEDCOM to a new RM database.
  3. 3. Backup your database.
  4. 4. Make a copy of your database with a different name.
  5. 5. Open the new database with SQLiteSpy.
  6. 6. Run the following
    query: SELECT SourceID, Name,
    SUBSTR(CAST(Fields AS TEXT),41) AS Fields FROM SourceTable;
  7. 7. Select all results and copy to the clipboard (select a cell,
    Ctrl-A, Ctrl-C)
  8. 8. Paste into a new text file in PSPad
  9. 9. Sort and/or edit so that the headings are at the top and the
    results are contiguous with no intervening blank lines. Copy all.
  10. 10. Open AncestrySourceConverter.xslx with MS Excel.
  11. 11. Delete all from sheet Source.
  12. 12. Paste from clipboard into the 1st cell of sheet Source.
  13. 13. Copy all UPDATE queries from sheet SQL. Note that just the first
    100 queries are created. If what you pasted into sheet Source is more
    than 100 rows, you will either have to do the UPDATEs in multiple
    batches or extend the rows of formulae in sheets Extract, Replace and
  14. 14. Paste into a new SQL window in SQLiteSpy.
  15. 15. Check through to the end that syntax colouring is correct. If
    there was an apostrophe in the original source it will need to be
    escaped with a second so that it is not interpreted as a text delimiter.
  16. 16. Execute the batch of UPDATE queries. This may take some 20 seconds
    – I do not know why it is so slow.
  17. 17. Open both the unprocessed and revised RootsMagic databases with RM
    and inspect the Master Source list to satisfy yourself with the results
    and make any other edits required.

To abbreviate the Short Footnote more, I think requires manual editing of
each citation, most effectively done by converting the sources to the !MyFreeForm3 source template.

Images of Results


Generate WebTags from
the Citations

This applies to RootsMagic 6 which first introduced WebTags. The Ancestry
GEDCOM passes the URL (hyperlink) of each online citation in the citation
Comments field. For convenient access to these online sources, it is
desirable to make a Citation WebTag with that URL. That’s what is described
in WebTags
– from and FTM
. And then to make it even more convenient,
copy those citation WebTags to person WebTags using WebTags
– Consolidate

Citations from Ancestry that Disappeared

We think that citations in support of a Name in an Ancestry tree disappear
on import to RootsMagic in certain cases. See Citations
Invisible Revealed
for further details and how to reveal them to

Discussions & comments from Wikispaces site


Comment: “Open SQLiteSpy with the fake
RMNOCASE extension”

September 2018 19:40:01
ve3meo Sep 28, 2015
A Mac user encountering an error “LIKE pattern too large” or
words to that effect wanted to know if there was a way to run
the script without the RMNOCASE collation (on his own build of
sqlite3). The answer is a qualified “maybe” and it will take
trial and error. Wherever there is a field that is compared to
a value and that field has been defined to use the RMNOCASE
collation, it is probable that an error will result.
It is possible in some cases to override the defined collation
with a COLLATE NOCASE suffix wherever the field is named in a
SQL SELECT statement. However, you cannot UPDATE such a field.
Fortunately, the script does not UPDATE a field using the
RMNOCASE collation so any collation error must stem from a
However, looking into this question raised some others. A
field that is UPDATEd is CitationTable.Comments and a puzzling
thing was observed. The script assumes that it is a TEXT field
which is what is contained in a number of databases inspected
yet in the table definition it is supposed to be type BLOB.
SQLite3 does not enforce field types and it appears that
RootsMagic does not follow its own field spec. This may be a
red herring but it could give rise to unexpected results if
some databases or rows within a database were not
type-consistent with others.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.