Places – Recombine Fractures from FTM 2012 #ancestrycom #ftm2012 #places

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

[inline comment: “FTM appears to parse on the last slash, which it drops”
ve3meo Mar 24, 2013
Belatedly, I worked out a procedure within FTM 2012 to recombine the fractured places. It is a bit tedious but better than editing one fact at a time; doesn’t hold a candle to direct access to the database as we have with RM and a batch process as I did with SQLite. If interested, read http://boards.ancest…/9578.2/mb.ashx. The parsing of the place on the forward slash seems to be a holdover from much earlier versions of FTM and maybe even an old version of GEDCOM which combined a fact place and description on one line, separated by the slash.
]

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.

Places-RecombineFTMfractures.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

Inline comments