Places+-+Recombine+Fractures+from+FTM+2012

This is a problem with an Ancestry.com tree synchronized with Family Tree Maker 2012 (version 21.0.0.723), and exported therefrom. Places containing a forward slash ("/") in the name are split on import into FTM into two parts. Everything after the slash goes to the Place name and all before to the Event description. T he FTM2012 GEDCOM exports the fracture while the Ancestry.com direct GEDCOM does not. As only FTM2012 can automatically download media and deliver the paths via GEDCOM to RootsMagic, I am forced to use its GEDCOM.

Here's an example: FTM appears to parse on the last slash, which it drops.
 * Ancestry.com place: || Toronto (West/Ouest) (City/Cité) Ward/Quartier No 5, Toronto (west/ouest) (city / cité), Ontario, Canada ||
 * FTM2012 Residence description: || Toronto (West/Ouest) (City/Cité) Ward/Quartier No 5, Toronto (west/ouest) (city ||
 * FTM2012 place: || Cité), Ontario, Canada ||

This may be a problem solely with places originating from Ancestry's databases for the Censuses of Canada with the complexities of bilingual English/French wording. Healing the fractures seemed a daunting task to do manually through RootsMagic so I worked up a series of SQLite queries that seem to have cured the patient. I'll go on to merge places and/or split out Place Details within RM.

code format="sql" -- Places-RecombineFTMfractures.sql /* 2012-03-22 Tom Holden ve3meo Investigation into fractured Place names from FTM2012 where part of the name is exported in the Event description (Detail) and the balance in the Place name. Typically, these are places from Ancestry Canada Census databases with '/' between English/French words, e.g. "west/ouest".

Recombines the fractures and deletes the event description part.

Does not carry Place Details (Site) over to the recombined Place - a revised version could by replacing the fractured Name in PlaceTable rather than creating a new recombined Place as this script does. Don't know why I didn't think of that.

N.B. This is a series of queries intended to be executed one at a time in sequence although one might go for broke and fire them off as a batch. There would be no chance to inspect the data.

/* Find Places with just ')' in name and not the balancing parenthesis, found in the event description, as in incorrectly exported places from FTM 2012. SELECT PlaceID   ,NAME FROM PlaceTable WHERE PlaceType = 0    AND NAME LIKE '%)%' AND NAME NOT LIKE '%(%';

/* Make a Table of EventIDs using the split Place names DROP TABLE

IF EXISTS xEventPlace; CREATE TEMP TABLE

IF NOT EXISTS xEventPlace AS   SELECT EventID ,Details ,Event.PlaceID AS PlaceID ,Place.NAME AS Place ,Event.SiteID AS SiteID ,Site.NAME AS Site FROM PlaceTable Place NATURAL INNER JOIN EventTable Event LEFT JOIN PlaceTable Site ON (Event.SiteID = Site.PlaceID) AND Site.PlaceType = 2 WHERE Place.PlaceID IN (           SELECT PlaceID            FROM PlaceTable            WHERE PlaceType = 0                AND NAME LIKE '%)%' AND NAME NOT LIKE '%(%'           ) AND Event.Details LIKE '%(%';

-- Generate recombined Place names in PlaceTable INSERT INTO PlaceTable SELECT DISTINCT NULL AS PlaceID ,0 AS PlaceType ,Details || '/' || Place AS NAME ,'' AS Abbrev ,'' AS Normalized ,0 AS Latitude ,0 AS Longitude ,0 AS LatLongExact ,0 AS MasterID ,'Generated by SQLite query from fractured FTM 2012 export: ' || Details || '/' || Place FROM xEventPlace;

-- Revise events to point to recombined Places UPDATE EventTable SET PlaceID = (       SELECT PlaceTable.PlaceID        FROM PlaceTable            ,xEventPlace        WHERE PlaceType = 0            AND PlaceTable.NAME LIKE EventTable.Details || '/' || xEventPlace.Place            AND EventTable.EventID = xEventPlace.EventID        ) WHERE EventID IN (       SELECT EventID        FROM xEventPlace        ORDER BY EventID ASC        );

-- Verify event places SELECT EventID ,xEventPlace.Details ,xEventPlace.Place ,PlaceTable.NAME FROM xEventPlace LEFT JOIN EventTable USING (EventID) LEFT JOIN PlaceTable ON (EventTable.PlaceID = PlaceTable.PlaceID)

-- Erase event descriptions UPDATE EventTable SET Details = '' WHERE EventID IN (       SELECT EventID        FROM xEventPlace        ORDER BY EventID ASC        ); -- All done code