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.

You must have an installation of Perl on your system (With ODBC plugin)
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

7 thoughts on “Create a database from your GEDCOM file”

Kurt sagt:

6 Juni 2011 um 19:27 Uhr

Bin eben zufaellig auf den Blog gekommen. Gefaellt mir sehr.

Antworten
Hans sagt:

11 Juni 2011 um 07:31 Uhr

I’m not familiar with Perl. Like to see the script for PHP.

Antworten
1. luventas sagt:
  
  12 Juni 2011 um 12:03 Uhr
  
  I can create it in the next week, then I will create a new post with the PHP version.
  
  Antworten
  1. Hans sagt:
    
    12 Juni 2011 um 20:31 Uhr
    
    That would be great. I’m looking forward to it.
    
    Antworten
Hans sagt:

11 Juni 2011 um 07:32 Uhr

I’m not familiar with Per. Like to see the script in PHP.

Antworten
Hem Singh sagt:

1 Juli 2014 um 13:24 Uhr

Please send me demo data script

Antworten
1. luventas sagt:
  
  9 Juli 2014 um 13:48 Uhr
  
  the database script is in the post.
  
  Antworten

7 thoughts on “Create a database from your GEDCOM file”

Leave a Comment Antworten abbrechen