Skip to content

Luventas Web Design

A new blog for developers in PHP, Java and mobile Development

  • Home
  • 2011
  • Mai
  • 27
  • Create a database from your GEDCOM file

Create a database from your GEDCOM file

Posted on 27 Mai 201114 Juni 2011 By luventas 7 Kommentare zu Create a database from your GEDCOM file
Genealogy, Perl

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

Print Friendly, PDF & Email

Beitrags-Navigation

❮ Previous Post: Create thumbnail from image with PHP
Next Post: Google Chrome: Problems with inaccurate „enctype“-data in HTML forms ❯

You may also like

Genealogy
Introduction how to use my genealogical scripts
20 Juni 2011
Genealogy
Anleitung zur Verwendung meiner genealogischen Skripte
17 Juni 2011
Genealogy
Include your family tree in your webpage
28 Mai 2011
Genealogy
Include your family tree in your webpage, new Version
22 Juni 2011

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

  1. Kurt sagt:
    6 Juni 2011 um 19:27 Uhr

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

    Antworten
  2. 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
  3. Hans sagt:
    11 Juni 2011 um 07:32 Uhr

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

    Antworten
  4. 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

Schreibe einen Kommentar Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Copyright © 2023 Luventas Web Design.

Theme: Oceanly News by ScriptsTown

Diese Website nutzt Cookies, um bestmögliche Funktionalität bieten zu können. OK, verstanden
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
immer aktiv
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SPEICHERN & AKZEPTIEREN