GEDCOM to a SQL-database?

[Magnus Wittström]

Hi

I want to export my GEDCOM data to an MySQL database, do anyone know
about a program that can do this for me, or does anyone know about a
program that can export GEDCOM data to any other SQL-based database.

Thanks
/Magnus Wittstrom

[Barney Tyrwhitt-Drake]

In article <3887338F...@student.hk-r.se>, Magnus Wittström
<pt9...@student.hk-r.se> writes

>
>I want to export my GEDCOM data to an MySQL database, do anyone know
>about a program that can do this for me, or does anyone know about a
>program that can export GEDCOM data to any other SQL-based database.
>

I think you need to step back a pace first, and ask yourself just what
you are trying to achieve.

A GEDCOM file produced by a family history database is likely to contain
several items, notably:

1) Events (such as birth, baptism, death) for an individual.
2) Parent-child relationships and spouse-spouse relationships.
3) Links between events and sources.
4) Associations between places and events.

If we just consider the simple piece of GEDCOM below:

0 INDI @I1@
1 NAME John /SMITH/
1 SEX M
1 BIRT 3 JAN 1888
2 PLAC St Mary, Amersham, BKM, ENG

For John SMITH's birth event, how far are you going to normalise the
data in the resulting MySQL tables? Will you create a separate table for
place names or one humungous individual event table with much repetition
of place names?

The answers to these questions depend on what you want to do with the
data. And as different people will want to do different things, it's
very unlikely that there will be one tool that will suit all. If you
then look at all the information in 1-4 above you'll see that there are
many different permutations of how you will organise the data in your
MySQL database.

Perhaps the easiest way if you want to focus on a final event database
in MySQL is to parse the GEDCOM file to produce a single (large) flat
file event table. You can then normalise that if you want to using
whatever techniques you want to. As an example of a program that may
help you to parse the GEDCOM to a CSV file (which you can then import
into just about any database system), take a look at the BIRDIE 2
program information on my website at the URL in the signature below.
--
Barney Tyrwhitt-Drake

Drake Software web site: http://www.tdrake.demon.co.uk

[Jerome Pepping]

Magnus Wittström wrote:

> Hi

>
> I want to export my GEDCOM data to an MySQL database, do anyone know
> about a program that can do this for me, or does anyone know about a
> program that can export GEDCOM data to any other SQL-based database.
>

> Thanks
> /Magnus Wittstrom

someone recommended gen2acc.exe to me which converts a gedcom to a
microsoft access database. i downloaded it and tried to use it. the
progress bar stops at 87% and freezes. has anyone else experienced
this? what could the problem be?

[Paul Blair]

Everything that Barney says is true. However, not being too pure about
it, I found this site useful:

http://www.leagans.com - The Leagans Family Tree
Check out Family Access Web - My gedcom to database converter!

FAW will take info from GEDCOM and paste it into a MS Access db for
you. It works very well, and is a credit to Bill.

FAW makes two tables as a rule - family and individual. This makes it
reasonably easy to work with the data.

Dates are always a problem - you will probably have things like 1904,
3 March 1904, and abt.1904 or c.1904. This really means you have to
treat dates as text, not dates. That's common no matter what you use.

Paul Blair
Canberra Australia
----------------------------------------------

On Thu, 20 Jan 2000 17:10:56 +0100, Magnus Wittström
<pt9...@student.hk-r.se> wrote:

>Hi
>
>I want to export my GEDCOM data to an MySQL database, do anyone know
>about a program that can do this for me, or does anyone know about a
>program that can export GEDCOM data to any other SQL-based database.
>
>Thanks
>/Magnus Wittstrom
>

-------------------------------
Paul Blair
pbl...@pcug.org.au

[Barney Tyrwhitt-Drake]

In article <397f8s0hc5gb1hqsp...@4ax.com>, Paul Blair
<pbl...@pcug.org.au> writes

>
>Dates are always a problem - you will probably have things like 1904,
>3 March 1904, and abt.1904 or c.1904. This really means you have to
>treat dates as text, not dates. That's common no matter what you use.
>

Again, dates can be straightforward, and can be converted to date
formats that database engines can handle, if that's what you want to do.
Leaving dates only as text removes the possibility of being able to sort
and filter records by date - arguably one of the major reasons you want
to put your data into a relational database system in the first place!

My preference is to set up date parsing so that a numerical date format
that the DB engine can use is created as a field alongside the purely
textual form of the date, which is always used for display purposes.
Abt. and c. are reduced to 1 Jan of the year in question, while Bef. and
Aft. are put to the next days either side of the year. Granted that is
not ideal, but it makes for consistent behaviour.

Again, most database engines are not familiar with anything other than
julianised dates. They'll quite happily accept things like 10 Sep 1752
in the British calendar (that never existed). My approach to what the
database sees as 'illegal' dates is to decrement them, one day at a
time, until they appear kosher to the database engine. They will still
sort in the right order, and as you always use the original text for
display purposes, no harm is done.

[Elwyn Goldsbury]

it will depend on the resulting file structure that you want. -
design that
and then you could perhaps just write your own program to do the
conversion
as it will need to know its way around the gedcom format to extract
various parts of records

Elwyn Goldsbury

[Elwyn Goldsbury]

what is the last gedcom record created? amd what would you expect as
the next record ? - that might give you a clue as to the problem

Elwyn Goldsbury

[Beau Sharbrough]

As always, Barney makes a great point.

If you have a data item called BIRTH_DATE, in this case a text date, and you
LEAVE IT ALONE in the database, and create another data item, in this case
ridiculously named BIRTH_DATE_FOR_SORTING and load it up with dates based on
any set of queries you find reasonable (the conventions of using the 1st
when a date is missing and decrementing dates when invalid are fine with
me), then you get the best of both worlds. You still have the accuracy of
unaltered source information, and the flexibility of date objects to grab
with date handling tools.

When we didn't have much disk space to work with, it was kind of silly to
put two birth date fields into an event object (one in text form and one in
date form). Barney has observed that the era where that was reasonable is
in the past.

Now that disk space is headed under a penny a megabyte, and processors are
really fast, I can't think of a practical reason not to create an entirely
transformed set of data objects for sorting, indexing, and lookup and link
them back to unaltered source information.

I'm suddenly imagining a universe of source info and an invisible layer of
data objects underneath them. It looks a lot like a text document with XML
tags!

Sorry, I got carried away but Barney does that to me about three or four
times a year.

--Beau Sharbrough