Constructing GEDCOMS from Access Databases

Question

Doing data conversions from one file format to another can often be perilous.  My discussion here is the conversion of filtered entries from an Microsoft Access database to a GEDCOM, which principally is a text file.   I am sure this must have been discussed, but what is the template format of a GEDCOM and is the conversion from one format to another usually done.

Answer 1

As you say, a Gedcom is a text file. However, it is primarily a transfer medium, used to move information from one genealogy program to another, using a common template. You can find out more here. ..https://en.wikipedia.org/wiki/GEDCOM

I am assuming you propose to convert your access database to a Gedcom. I would say that the effort to do this, and the possibility of not creating a readable file, would take more time than entering the data manually.

Comments

I concur with Dave. That said, I've asked Gaile Connolly to come over here; I know she's converted spreadsheets to GEDCOM so she may have a faster way.

---

I saw your invite, Jillaine.  I didn't just convert spreadsheets to gedcoms - I was developing a web application to do the job, planned to host it on my own web server, and offer a user account to anyone who wanted it so they could use it for that purpose.  An Access database can instantly be exported to a set of spreadsheets, which could easily be manipulated to the spreadsheet format my tool used.

My goal was to create a WikiTree specific gedcom - one that WikiTree's proprietary import process would bring in cleanly, but the team was apparently not interested in either providing me the info I asked for or in making any modification to their process to recognize when a product of my system was being imported and bring in the coding for categories, section headings, and inline sourcing included in the gedcom produced by my software.  It just took everything that was included in the free text area and put it under a "Notes" heading that it created.

My conversion tool was used during a cemetery challenge, to create profiles for all people listed for an individual cemetery, but I took it down soon after that when it was not possible to overcome the impediments to 100% clean import.  Now, of course, the WikiTree gedcom import process is completely changed and I have not made any attempt to see what it is or determine how it works.  Sorry, Andrew, but I have burnout with regard to format conversions from other things to gedcoms, so I am not able to help.

 

---

Greetings David, Jillaine and Gaile

My intention was to upload a listing of deceased regimental names with dates and places of deaths and where available dates and places of birth.  There are a couple of other fields i.e. spouse.

The next question was going to be whether Wiki Tree actually wants an upload of names (DOB/DOD), who are not related, but who belong to a unique regimental set?   I have maintained a nominal roll of the regiment for the last 25 years and am prepared to share basics details of those who are deceased.   I am asking myself, would this be of any use to the Wikitree concept?

---

Andrew,  from what you have just posted, it would seem that your data is not strictly genealogical, and so the format of a gedcom would not really work. The information you have though would be very useful to others. To use it on here, you could create a FreeSpace page, and copy/paste the data into it.

---

I also think it would be useful information. But why not create profiles for each person, Dave? I fear few would find this if limited to a freespace page.  

While not initially connected to the greater tree, such profiles would be found upon a search by family members or relations (or people like me who love the challenge) who could work on them.

---

I agree with Dave regarding the type of data Andrew has. It does appear to be very valuable info for a lot of individuals, but it's not genealogical. The GED format is intended to support lineage-linked families. It appears that, at present, wikitree supports only the GEDCOM format as a means to import data in bulk, and I'm guessing that is unlikely to change soon.

As mentioned in an important comment by Gaile above regarding the coordination (or lack thereof) between GEDCOM import and wikitree profile creation, and noted in my answer below, GEDCOMs are not imported cleanly into wikitree. E.g., wikitree creates a default Biography section which has to be edited, and GEDCOM notes and comments get combined into a wikitree NOTES section.

Andrew's data appears to be suited for a wikitree Category "import," but, at present, there ain't no such.

@Jillaine: though your idea provides a means to get Andrew's information uploaded to wikitree, creating a new profile for every individual in a GED for later merging (whether via a "challenge" or with the hope that someone later discovers a match) sounds like it might be a bad precedent to set. That's pretty much how lots of GEDCOMs have already been imported.

Just a suggestion: might it not be a better approach for Andrew to create some sort of well-structured file to be made available elsewhere (maybe a free-space page) and announced in the G2G forum.Then a "challenge" or similar could be initiated to edit THAT information into existing profiles with (e.g.) a category? If an individual doesn't exist, a new profile could be created.

 

---

Thank you so much to all for your contribution.  I figured that the upload in GEDCOM format would make it so much more accessible, but I foresee the issues.   There is incomplete data of the website I maintain at www.bsap.org - being a war roll of honour, civilian death roll of honour and then an in memoriam section since 1980...  I guess if people are searching for ancestors and they make a match with an entry on the website, then they can alway make reference to me.   I often wonder thought what happens when I fall off or get hit by the bus!

Answer 2

Speaking from over 10 years experience, there's no easy "conversion" from any Access form/report/export to GED format. As mentioned above, there appears to be no way to cleanly import a GED to wikitree that results in the proper generation of the biography section, though the GED categories for Name, Birth, Death, Burial, Residence, Marriage and Sources works pretty well, though FAMC and FAMS records get dumped into the wikitree Marriage subsection. You can see typical results (for my GED) here: https://www.wikitree.com/wiki/Veazie-497 [[Veazie-497| Henry Veazie]]

Off-topic (does not speak to "conversion" after use of a filter):

I have a routine to write to a spec 5.0 GED file from my database because I use it for reasons other than wikitree. E.g., Gedreporter imports it quite well. Note, however, the module is written entirely in Access Basic, if you want to go that route. Because of the FAMS and FAMC records in the GED, it's customized due to the way I maintain tables in my DB. It took weeks of coding and testing to get it to work well.

You might consider generating a report that arranges data in the order required for GED records. You will have to get very familiar with the GED field formats and record formats! If you generate such a text file, you can:

1) hand edit the file to add the GED tags to the data. However, that would require some fancy bookkeeping to keep track of the indiv and family records numbers.

2) if you're into programming, write some routines in Word Basic to generate the GED tags for the text file.

Comments

Greetings Bruce

Thank you for your detailed comments and opinion.  I am sure I would be capable of doing such a conversion, by list generation and then using something like Word to remap to a GEDCOM format.  I am just interest in the GEDCOM format and the peculiarities to make it compatible with a Wiki Tree upload.   I am sure with a little research on the web, I should come up with the answers.  I merely sought to establish if there was an experience resource here, and thank you for 'popping' up!  

As I mentioned in my one response above, I am also wondering if what I intend to upload, is actually appropriate material for Wiki Tree.

---

It would be wise to figure out a way to include the appropriate categories (military and cemetery) so those would come along, too.  That would further benefit the greater tree.

Answer 3

I thought I would revisit this thread and discuss an outcome which may interest a few of you.  My issue was a data conversion from Microsoft Access database to a GEDCOM file.  It will work for those who have their data in Microsoft Excel too (obviously structure dependent).

In essence this is fundamentally easy to do provided you understand the basic template of a GEDCOM file.  The trick is so obvious that I am surprised it escaped me when I originated the post.

What I did was set up a Microsoft Word document using the mail merge facility to create an extract of the data to a specified standard GEDCOM layout for data.  I have done this successfully for a single generation export and am experimenting with multi generations at the moment.  The trick is to keep it simple. Once I have run the mail merge, I simply copied and pasted the layout between the GEDCOM headers and tail in a text file and saved that as a GED file.  It works, and WikiTree are enjoying data...

Comments

Andrew, Thanks for your recent post, this is the first time I am seeing this thread.

The comment by Gail from May 24 is intriguing. While I am not intimately familiar with Access, I am quite familiar with Excel and Word. I have never been a fan of Mail Merge, but if it is working for you, then I applaud your efforts!

What I wanted to do is offer services if you ran into any issues with your data. If you were able to dump your data to excel, we can use VBA to format the data for you.