Ancestry.com and RootsMagic 5 #ancestrycom #sources #update #gedcom

Problems with GEDCOM

In my experience, building an evidence-driven Family Tree on Ancestry.com
from evidence found in its databases is faster and easier than extracting
the evidence from Ancestry.com 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.+$
Replace:

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

Getting
Source and Other Images from Ancestry.com

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
   you.
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 Ancestry.com.
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 Ancestry.com

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.

Shrinking
Verbose Master Sources from Ancestry.com

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
Ancestry.com.

Master
Source imported from Ancestry.com

Footnote:
Ancestry.com,
1870 United States Federal Census (Online publication – Provo, UT, USA:
Ancestry.com 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)
Short
Footnote: Ancestry.com,
1870 United States Federal Census
Bibliography:
Ancestry.com.
1870 United States Federal Census. Online publication – Provo, UT, USA:
Ancestry.com 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.
Repository:

Ancestry.ca
http://www.Ancestry.ca

Master
Source after SQLite procedure

Footnote:
Ancestry.com,
1870 United States Federal Census
Short
Footnote: 1870
United States Federal Census
Bibliography:
Ancestry.com.
1870 United States Federal Census. Online publication – Provo, UT, USA:
Ancestry.com 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.
Repository:

Ancestry.ca
http://www.Ancestry.ca

Procedure

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
    SQL.
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 Ancestry.com and FTM. And then to make it even more convenient,
copy those citation WebTags to person WebTags using WebTags
– Consolidate.

Revealing
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
RootsMagic.

Discussions & comments from Wikispaces site