Ancestry.com+and+RootsMagic+5

=Ancestry.com and RootsMagic 5= toc 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: =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: code Unknown info (line 28) 3 _APID 1,8944::212
 * excessive exception report from the GEDCOM import
 * no images downloaded and linked
 * verbose footnotes

code 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: code Find: ^.+_APID.+$ Replace: code 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. 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.
 * 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. [[file: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

=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: **<span style="font-family: Tahoma,sans-serif; font-size: 9pt;">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.
 * <span style="font-family: Tahoma,sans-serif; font-size: 9pt;">Repository: **

<span style="font-family: Tahoma,sans-serif; font-size: 9pt;"> Ancestry.ca

<span style="font-family: Tahoma,sans-serif; font-size: 9pt;"> http://www.Ancestry.ca

Procedure
This involves multiple steps and tools: The steps comprise: 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.
 * SQLiteSpy
 * PSPad or other text editor with sorting capability
 * MS Excel or, possibly, other spreadsheet program, using [[file:AncestrySourceConverter.xlsx]]
 * 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.

=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.