I have loved using RootsMagic 9 and the features/tools it has. But, I would love the feature to export directly to a Spreadsheet (xslx).
I’ve been searching for ways to move a family tree to a spreadsheet, and visa versa.
I tried GEDxlate today (it’s free), and it’s really good for converting a GEDCOM into .xlsx format with many options.
I’m now trying to find other options to export a Spreadsheet to GEDCOM correctly and easily. I have tried Excel2GED (also free) that does this. Obviously one has to create the (or have) the family relationships setup by adding ID’s to link records manually. But it does work.
I’m needing tools like this to try and recreate GEDCOMs and then into a RM tree from a broken database I have managed to convert into a large spreadsheet.
I see a lot of queries like this online a maybe this may help some folk.
I was hoping RM vers 10 would bring out a feature like this. So I haven’t seen a reason yet for me to upgrade from 9.
how’d would that work? you do realize the # of the tables RM has? you could export directly from the tables. You would need multiple spreadsheets – unless you only one row of person and only for basic facts.
I believe you would be much better of using simple Sqlite scripts to get what you want
Kevin
Exporting to a spreadsheet can be done by installing the SQLite ODBC connector and writing SQL queries in the Get Data part of Excel. These are thus linked to the RM database and can be refreshed at the click of a button.
See Tom Holden’s https://sqlitetoolsforrootsmagic.com/ website for more details on writing SQL queries against the RM database.
Thanks for adding that – ODBC connectors with Excel are very useful – I have 2-3 Excel scripts i use regularly for “Maint cleanup” but I generally use Power BI.
The Census one I use combines Census Primary with Census shared and then displays missing census info in a grid to look for missing census facts (based on life span)
I have done something very similar using Family Tree Analyzer. You import your gedcom and then can export a variety of reports in .xlsx format. I do it every few months with my Ancestry gedcom
Thank tor for all your inputs. I will go through the links you’ve shared and see if the SQL makes sense to me. I’m not a SQL user so don’t know the code etc. Regarding the ability to get data into RM9 from a spreadsheet, can any one of you SQL knowledgeable people give me a basic spreadsheet table template and the code etc to link back into RM9? Please message me and I’ll send my email for you to use - unless that detail is of interest to share with the others here. I’m presuming that if I have a spreadsheet template layout I can copy and paste selections from from my large spreadsheet (broken database), and thereby slowly build up branches of these family trees in RM9 using some form of SQL link. I would have to manually enter the relationship links in the template for the tree to be built. This relationship we don’t have in the spreadsheet and has to be worked out somehow by us. We have managed to find other records for some parts of the larger tree where we have the complete records, but in the main we are figuring out the links by dates.
I’m guessing the basic data for the template would be (ID, 1st name, Last name, Sex, Dob, Place of Birth, Dod, Place of death, ID-father, ID-mother, marriage date, Marriage place, ID-spouse 1, Spouse name, ID-spouse 2 Spouse name). Would the names of the relating people be needed on the same line if the ID’s of those persons is given, pointing to their record on another line? Anyway, I’m sure this has been done already.
I look forward to hearing from you as I am keen to try and get this large spreadsheet of mine back to some form of usable database.
My ODBC link is read only so can’t write data back to RM. I wouldn’t want to because the RM database is complex. I have never tried to write back from Excel even to other databases - not sure if it is even possible.
I’m not good with the front end reporting of Power BI despite supporting the back end admin as part of my day job. However I did use it to produce a map report from RM which looked great and a version of my people and census fact report for gap finding, mostly as an excuse to learn Power BI. I find Excel good for quick operational reports - citations without media, items without data etc.
My usual person-census excel is a pivot table with quite a bit of excel function code.
I’m thinking that everyone is over-complicating this.
All family trees are able to export a GEDCOM or portions or the whole tree. And, a GEDCOM is essentially quite a simple structure - in fact, like a vertical sequential spreadsheet. I know that RM and other family tree databases have multiple tables handling all their details and link. But in it’s simplest form a tree doesn’t hold much data, and that’s the data that I’m suggesting could be added from outside using, say SQL etc. which could simplify transferring data found in a spreadsheet into RM. It would save a lot of typing.
So, because RM can export a GEDCOM, why not also a spreadsheet (see my examples of a simple 5 person tree in a GEDCOM format and the same transferred into a xlsx format. So, I’m wanting a facility to either have a simple table looking like my sample xlsx being imported into RM via a SQL - or a way to output the xlsx to a GEDCOM for pulling into RM (then it’s a simple matter of combining this new tree structure into a main tree.
In either case - build a simple tree structure, and the finer details etc can be inserted/edited inside RM afterwards.
Because the GEDCOM is quite long and I don’t think I can upload the file here, I’ve clipped it as an image.
If the source spreadsheet is the same format as your TestTree.xlsx example and has only one record per person, it would be feasible to import it into a table in a SQLite database and to write queries that would transfer the data from it into the multi-table structure of an empty RM database. It would have to write to the PersonTable, Name table, FamilyTable, ChildTable, EventTable and PlaceTable and link them accordingly. It’s not a trivial set of statements but doable.
Thank you for that detail. In my online searches I have seen so many requests from people looking for a way to transfer basic family details on spreadsheets into a proper family tree database. I think a lot of people often only have the basic details of their families, so spreadsheets with a row per person is common. I’m an ex assembler and Cobol programmer, and unfortunately, never did get into the modern PC language, SQL is something (at 81 yrs old now) I don’t really want to go into learning
I found a YouTube video from an Irish guy (GenealogyReviews) who is giving a 4-lesson tutorial on VBA from a simple xslx to GEDCOM. His steps are pretty simple but he forgot to put the lesson in on how to handle the relationships. I’m trying to contact him to see if he has it somewhere.
TomH, I’m hoping that these thoughts might be seeds for one of you programmers to generate that bit of code to allow RM9 or RM10 users to pull some very basic family data into an empty RM db via a table (where data is copied to from a spreadsheet etc), that can then be added to once in RM and then it’s a simple matter to merge into a one’s tree if necessary. I believe this would prove to be a popular add-in. I know I would use it to generate the many missing charts in my 4000 line spreadsheet - it would save me a lot of typing.
