Create a database from your GEDCOM file

You are interested in creating your own webpage to publish your family tree to the world? Then you need to insert your data in a database. The problem: most programms are creating a gedcom file as global file type or other files formats specially for the software you are using but there is no software to create a database. Nearly all genealogical researchers uses then one of the well known platforms to publish there genealogical data to the world wide web. But what to do, if you do not want to upload your data to one foreign platform? Then you have to start again from the beginning to type your data into a database.Or you can use my script to read out the data from your gedcom file and store it directly to your database. Only two conditions are given to use this script.

1. You must have an installation of Perl on your system (With ODBC plugin)
2. An ODBC connection to your database (local or on webserver)

The database for using my script must be created with the following SQL:

--
-- Table structure for table `famchild`
--

DROP TABLE IF EXISTS `famchild`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `famchild` (
`famID` varchar(40) NOT NULL DEFAULT '',
`child` varchar(40) NOT NULL DEFAULT '',
PRIMARY KEY (`famID`,`child`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `family`
--

DROP TABLE IF EXISTS `family`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `family` (
`famID` varchar(40) NOT NULL DEFAULT '',
`husband` varchar(40) DEFAULT NULL,
`wife` varchar(40) DEFAULT NULL,
`marr_date` varchar(255) DEFAULT NULL,
`marr_plac` varchar(255) DEFAULT NULL,
PRIMARY KEY (`famID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `person_st`
--

DROP TABLE IF EXISTS `person_st`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `person_st` (
`persID` varchar(40) NOT NULL DEFAULT '',
`name` varchar(255) DEFAULT NULL,
`vorname` varchar(255) DEFAULT NULL,
`marname` varchar(255) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`birt_date` varchar(255) DEFAULT NULL,
`birt_plac` varchar(255) DEFAULT NULL,
`taufe_date` varchar(255) DEFAULT NULL,
`taufe_plac` varchar(255) DEFAULT NULL,
`deat_date` varchar(255) DEFAULT NULL,
`deat_plac` varchar(255) DEFAULT NULL,
`buri_date` varchar(255) DEFAULT NULL,
`buri_plac` varchar(255) DEFAULT NULL,
`occupation` varchar(255) DEFAULT NULL,
`occu_date` varchar(255) DEFAULT NULL,
`occu_plac` varchar(255) DEFAULT NULL,
`religion` varchar(80) DEFAULT NULL,
`confi_date` varchar(255) DEFAULT NULL,
`confi_plac` varchar(255) DEFAULT NULL,
`note` longtext,
PRIMARY KEY (`persID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Now you are able to start the script. The script is named gedcomToDatabase.pl and must be called with the gedcom file as parameter:

> perl gedcomToDatabase.pl gedcomfile.ged

#!/usr/bin/perl

use Win32::ODBC;

$anfang = 0;
$anfangf = 0;

@person;
@fam;
@chil;

$birt = 0;
$deat = 0;
$chr = 0;
$buri = 0;
$occu = 0;
$conf = 0;

$indi;
$surn;
$givn;
$marn;
$sex;
$birtplac;
$birtdate;
$deatplac;
$deatdate;
$chrdate;
$chrplac;
$buridate;
$buriplac;
$reli;
$occu2;
$occudate;
$occuplac;
$confdate;
$confplac;
$note;

$famlist;
$marr = 0;
$marrdate;
$marrplac;
$famindi;
$husb;
$wife;

open(DATEI, $ARGV[0])or die "Kann Datei ".$ARGV[0]." nicht oeffnen\n";
while(defined($zeile = <DATEI>)) {
if($zeile =~ /0\x20\x40(I.*)\x40/) {
if($anfang == 1) {
push @person, $indi.";".$surn.";".$givn.";".$marn.";".$sex.";".$birtdate.";".$birtplac.";".$chrdate.";".$chrplac.";".$deatdate.";".$deatplac.";".$buridate.";".$buriplac.";".$occu2.";".$occudate.";".$occuplac.";".$reli.";".$confdate.";".$confplac.";".$note;
$deat = 0;
$chr = 0;
$buri = 0;
$occu = 0;
$conf = 0;
$birt = 0;
$marr = 0;

undef $indi;
undef $surn;
undef $givn;
undef $marn;
undef $sex;
undef $birtplac;
undef $birtdate;
undef $deatplac;
undef $deatdate;
undef $chrdate;
undef $chrplac;
undef $buridate;
undef $buriplac;
undef $reli;
undef $occu2;
undef $occudate;
undef $occuplac;
undef $confdate;
undef $confplac;
undef $note;
}
$indi = $1;
$indi =~ s/\x27/\xB4/go;
$anfang = 1;
}
elsif($zeile =~ /2\x20SURN\x20(.*)/) {
$surn = $1;
$surn =~ s/\x27/\xB4/go;
}
elsif($zeile =~ /2\x20GIVN\x20(.*)/) {
$givn = $1;
$givn =~ s/\x27/\xB4/go;
}
elsif($zeile =~ /2\x20_MARNM\x20(.*)/) {
$marn = $1;
$marn =~ s/\x27/\xB4/go;
}
elsif($zeile =~ /1\x20SEX\x20(.*)/) {
$sex = $1;
}
elsif($zeile =~ /1\x20BIRT/) {
$deat = 0;
$chr = 0;
$buri = 0;
$occu = 0;
$conf = 0;
$birt = 1;
$marr = 0;
}
elsif($zeile =~ /1\x20DEAT/) {
$birt = 0;
$chr = 0;
$buri = 0;
$occu = 0;
$conf = 0;
$deat = 1;
$marr = 0;
}
elsif($zeile =~ /1\x20CHR/) {
$birt = 0;
$deat = 0;
$chr = 1;
$buri = 0;
$occu = 0;
$conf = 0;
$marr = 0;
}
elsif($zeile =~ /1\x20BURI/) {
$birt = 0;
$deat = 0;
$chr = 0;
$buri = 1;
$occu = 0;
$conf = 0;
$marr = 0;
}
elsif($zeile =~ /1\x20OCCU\x20(.*)/) {
$birt = 0;
$deat = 0;
$chr = 0;
$buri = 0;
$occu = 1;
$conf = 0;
$marr = 0;
$occu2 = $1;
$occu2 =~ s/\x27/\xB4/go;
}
elsif($zeile =~ /1\x20CONF/) {
$birt = 0;
$deat = 0;
$chr = 0;
$buri = 0;
$occu = 0;
$conf = 1;
$marr = 0;
}
elsif($zeile =~ /1\x20MARR/) {
$marr = 1;
}

elsif($zeile =~ /2\x20DATE\x20(.*)/) {
if($birt == 1) {
$birtdate = $1;
$birtdate =~ s/\x27/\xB4/go;
}
if($deat == 1) {
$deatdate = $1;
$deatdate =~ s/\x27/\xB4/go;
}
if($chr == 1) {
$chrdate = $1;
$chrdate =~ s/\x27/\xB4/go;
}
if($buri == 1) {
$buridate = $1;
$buridate =~ s/\x27/\xB4/go;
}
if($occu == 1) {
$occudate = $1;
$occudate =~ s/\x27/\xB4/go;
}
if($conf == 1) {
$confdate = $1;
$confdate =~ s/\x27/\xB4/go;
}
if($marr == 1) {
$marrdate = $1;
$marrdate =~ s/\x27/\xB4/go;
}
}
elsif($zeile =~ /2\x20PLAC\x20(.*)/) {
if($birt == 1) {
$birtplac = $1;
$birtplac =~ s/\x27/\xB4/go;
}
if($deat == 1) {
$deatplac = $1;
$deatplac =~ s/\x27/\xB4/go;
}
if($chr == 1) {
$chrplac = $1;
$chrplac =~ s/\x27/\xB4/go;
}
if($buri == 1) {
$buriplac = $1;
$buriplac =~ s/\x27/\xB4/go;
}
if($occu == 1) {
$occuplac = $1;
$occuplac =~ s/\x27/\xB4/go;
}
if($conf == 1) {
$confplac = $1;
$confplac =~ s/\x27/\xB4/go;
}
if($marr == 1) {
$marrplac = $1;
$marrplac =~ s/\x27/\xB4/go;
}
}
elsif($zeile =~ /1\x20RELI\x20(.*)/) {
$reli = $1;
$reli =~ s/\x27/\xB4/go;
}
elsif($zeile =~ /1\x20NOTE\x20(.*)/) {
$note = $1;
$note =~ s/\x27/\xB4/go;
}
elsif($zeile =~ /2\x20CONC\x20(.*)/) {
$note .= $1;
$note =~ s/\x27/\xB4/go;
}
elsif($zeile =~ /0\x20\x40(F.*)\x40/) {
if($anfangf == 1) {
$famlist = $famindi.";".$husb.";".$wife.";".$marrdate.";".$marrplac;
foreach $entry(@chil) {
$famlist .= ";".$entry;
}
push @fam, $famlist;

undef $famlist;
$marr = 0;
undef $marrdate;
undef $marrplac;
undef $famindi;
undef $husb;
undef $wife;
undef @chil;
}
if($anfangf == 0) {
push @person, $indi.";".$surn.";".$givn.";".$marn.";".$sex.";".$birtdate.";".$birtplac.";".$chrdate.";".$chrplac.";".$deatdate.";".$deatplac.";".$buridate.";".$buriplac.";".$occu2.";".$occudate.";".$occuplac.";".$reli.";".$confdate.";".$confplac.";".$note;
$birt = 0;
$deat = 0;
$chr = 0;
$buri = 0;
$occu = 0;
$conf = 0;

undef $indi;
undef $surn;
undef $givn;
undef $sex;
undef $birtplac;
undef $birtdate;
undef $deatplac;
undef $deatdate;
undef $chrdate;
undef $chrplac;
undef $buridate;
undef $buriplac;
undef $reli;
undef $occu2;
undef $occudate;
undef $occuplac;
undef $confdate;
undef $confplac;
undef $note;
$anfangf = 1;
}
$famindi = $1;
}
elsif($zeile =~ /1\x20HUSB\x20\x40(.*)\x40/) {
$husb = $1;
$husb =~ s/\x27/\xB4/go;
}
elsif($zeile =~ /1\x20WIFE\x20\x40(.*)\x40/) {
$wife = $1;
$wife =~ s/\x27/\xB4/go;
}
elsif($zeile =~ /1\x20CHIL\x20\x40(.*)\x40/) {
$c = $1;
$c =~ s/\x27/\xB4/go;
push @chil, $c;
}
elsif($zeile =~ /1\x20CHAN/) {
$deat = 0;
$chr = 0;
$buri = 0;
$occu = 0;
$conf = 0;
$birt = 0;
$marr = 0;
}

elsif($zeile =~ /0\x20TRLR/) {
$famlist = $famindi.";".$husb.";".$wife.";".$marrdate.";".$marrplac;
foreach $entry(@chil) {
$famlist .= ";".$entry;
}
push @fam, $famlist;
}
}

#### Angaben fuer die Datenbank ####

$db = Win32::ODBC->new("stammbaum");
if( ! $db ) {
print "Fehler beim Connect\n";
exit 1;
}

$delete = "DELETE FROM person_st; delete FROM family; DELETE FROM famchild;";
$db->Sql($delete);

open(OUT, ">>$ARGV[0]_insert.sql")or die "Kann SQL-Datei nicht schreiben\n";

foreach $eintrag(@person) {
@tmp = split(/\x3B/, $eintrag);
$insert = "INSERT INTO person_st (`persID`, `name`, `vorname`, `marname`, `sex`, `birt_date`, `birt_plac`, ";
$insert .= "`taufe_date`, `taufe_plac`, `deat_date`, `deat_plac`, `buri_date`, `buri_plac`, ";
$insert .= "`occupation`, `occu_date`, `occu_plac`, `religion`, `confi_date`, `confi_plac`, `note`) ";
$insert .= "VALUES('$tmp[0]', '$tmp[1]', '$tmp[2]', '$tmp[3]', '$tmp[4]', '$tmp[5]', '$tmp[6]', ";
$insert .= "'$tmp[7]', '$tmp[8]', '$tmp[9]', '$tmp[10]', '$tmp[11]', '$tmp[12]', '$tmp[13]', ";
$insert .= "'$tmp[14]', '$tmp[15]', '$tmp[16]', '$tmp[17]', '$tmp[18]', '$tmp[19]');\n";
$insert =~ s/\xC3\xBC/\xFC/go; #ü
$insert =~ s/\xC3\xB6/\xF6/go; #ö
$insert =~ s/\xC3\x9F/\xDF/go; #ß
$insert =~ s/\xC3\xA4/\xE4/go; #ä
$insert =~ s/\x3C\xB3/\xF3/go; #ó
$insert =~ s/\x3C\xA6/\xE6/go; #æ
$insert =~ s/\x3C\xA9/\xE9/go; #é
$insert =~ s/\x3C\x96/\xD6/go; #Ö
$db->Sql($insert);
#print OUT $insert;
undef @tmp;
}
foreach $eint(@fam) {
@tmp = split(/\x3B/, $eint);
$i = 0;
foreach $e(@tmp)
{$i++;}
$i--;
$insert2 = "INSERT INTO family(`famID`, `husband`, `wife`, `marr_date`, `marr_plac`) VALUES(";
$insert2 .= "'$tmp[0]', '$tmp[1]', '$tmp[2]', '$tmp[3]', '$tmp[4]');\n";
$insert2 =~ s/\xC3\xBC/\xFC/go; #ü
$insert2 =~ s/\xC3\xB6/\xF6/go; #ö
$insert2 =~ s/\xC3\x9F/\xDF/go; #ß
$insert2 =~ s/\xC3\xA4/\xE4/go; #ä
$insert2 =~ s/\x3C\xB3/\xF3/go; #ó
$insert2 =~ s/\x3C\xA6/\xE6/go; #æ
$insert2 =~ s/\x3C\xA9/\xE9/go; #é
$insert2 =~ s/\x3C\x96/\xD6/go; #Ö
$db->Sql($insert2);
#print OUT $insert2;
$t = 5;
while($t <= $i) {
$insert3 = "INSERT INTO famchild(`famID`, `child`) VALUES('$tmp[0]', '$tmp[$t]');\n";
$insert3 =~ s/\xC3\xBC/\xFC/go; #ü
$insert3 =~ s/\xC3\xB6/\xF6/go; #ö
$insert3 =~ s/\xC3\x9F/\xDF/go; #ß
$insert3 =~ s/\xC3\xA4/\xE4/go; #ä
$insert3 =~ s/\x3C\xB3/\xF3/go; #ó
$insert3 =~ s/\x3C\xA6/\xE6/go; #æ
$insert3 =~ s/\x3C\xA9/\xE9/go; #é
$insert3 =~ s/\x3C\x96/\xD6/go; #Ö
$db->Sql($insert3);
#print OUT $insert3;
$t++;
}
undef @tmp;
}

close(OUT);

gedcomToDatabase.pl File for download

_______________________________________________________________

UPDATE:

Because of found bugs in the PHP version of this script, I fixed this bug in the Perl version too. Explanation of this fix is written down in the post of the PHP version of this script.

updated version of perl script for download