Sqlite - Reporting - Inserting - Updating

Morning Folks.

Just trying out Roots Magic at the moment, don’t have nearly a massive Tree compared to some.

Is there a way to access and Insert large chunks of Data into the tables directly, without going through the application?

I have a python script that has taking all the counties of the UK, towns and cities, into a JSON file.

In Roots, Under Places I have currently the order on.

County, Country

Then under place details, a list of the Towns/cities. I.E

Angus, Scotland

I can convert the JSON to any other format, I.e. CSV for easier importing if needed.


My Place is town, county, country i.e. Montrose, Angus, Scotland. to follow your example.
My Place details is 46 High Street i.e. usually for a residence from a census or electoral roll, or it might be St Andrew’s Church from a baptism, marriage or burial, or Montrose Cemetery. I even have a river name from a drowning incident.
These are only examples but it seems a bit repetitive to have to include the town name for each of these. Are you not going to the level of detail that I do?.

This doesn’t help with your quest to find a way to access and Insert large chunks of Data but you might want to think about what data you put in what fields before you embark on the project.

I am off to Tesco in Montrose now but that is not in my Place details.

Much appreciated for the reply Terry.

No I wont be going into that much detail i.e. Address, Specific Locations etc

Every member has the associated Documents to go with them i.e. Birth, Marriage, Death, Newspaper.

The reason on wanting it

County, Country
----- Town/City

Is I would eventually like to run reports on just Angus, Scotland or Perth & Kinross, Scotland rather than

Montrose, Angus, Scotland or Perth, Perth & Kinross, Scotland.

At the moment I can click on Angus, Scotland and see EVERYONE born in Angus, not just specific town.

Enjoy your trip to Tesco, Getting busier by the day on the lead up to Christmas :stuck_out_tongue:

Yes, and I would encourage you to follow up with the technical implementation in the Forum at sqlitetoolsforrootsmagic.com which focusses on these questions. This Discourse Community is for use of the application and is administered by RM Inc which offers no support for such sideload operations.

I am a regular SQLite user, but for your application I think I might go in a slightly different direction. Instead of writing code to load the JSON file directly into an RM database with SQLite, I might instead write code to convert the JSON file to GEDCOM. I would certainly consider making a GEDCOM file with one dummy person, with a bunch of dummy facts all of the same dummy type and each with a place corresponding to the places in your JSON file. I could then import the GEDCOM file, delete the dummy person, and delete the dummy fact type. All the places would remain.

I don’t know if the code to do that would harder or easier than the code to load the places into RM’s PlaceTable directly, but it’s a different approach that lets the places be loaded without risking SQLite errors to your database.

If you want to go the SQLite route anyway or even if you go the GEDCOM route, It would probably be better to load your places into a new and empty database. Then you can import the place names from there into your main RM database using RM itself to do the import. That way, your code would never have to touch your main RM database.