[Gramps-devel] Spreadsheet Export/Import with update

[Douglas S. B. <db...@cs...>]

[I have a functional spreadsheet export/import with update working with
gramps, still described at
http://emergent.brynmawr.edu/emergent/GrampsCSVImport This text is from
that page. Except for the encoding issues, internationalization, and
report options, it is finished. Let me know if you have comments,
questions, or if you might find this useful. -Doug]

Gramps Spreadsheet Import/Export

Import Version: 10
Export Version: 10

WARNING: this is currently under heavy development, so please don't use
this on good data; use only as directed, on test data!

This program allows you to import/export a spreadsheet of data all at
once. The spreadsheet must be in the Comma Separated Value (CSV) format.
Most spreadsheet programs can read and write this format.

There are three main uses for these tools:

1. You can export your core gramps data into a spreadsheet format, edit it
with a text or spreadsheet program, and import the changes and additions
back into gramps. This is handy for sending to others to fill in, or for
taking on the road when you don't have your full gramps application.

2. You can import new data into your gramps database. For example, if you
have a set of new people to add to your database, but don't want to hunt
and peck your way to finding where they go, you might find it easier to
type them into a spreadsheet, and then quickly suck all of them in at
once. This is handy if you have a large amount of data that you are
cutting and pasting from another application or the web.

3. You can also import a set of corrections and additions. Say that you
have printed out a report, and you are going through it marking
corrections. If you make each correction a section of a spreadsheet, you
can "script the edits" and then execute them all at once.

Export
------

To export your database:

   1. Place the file http://bubo.brynmawr.edu/~dblank/ExportCSV.py in your
gramps/plugins subdirectory.
   2. Start gramps, Select "Export" from the File menu, and select "Comma
Separated Values Spreadsheet (CSV)"

A selected set of fields of your genealogy data will be saved to a .csv
file in the format described below. In addition, the people and familes
are referenced so that the data can be edited and read back in, thereby
updating the database.

There are some columns that will be blank, specifically note and source
columns. These are listed in the spreadsheet so that you can make notes
for the import, but notes and sources are never exported with this tool.

If you load this spreadsheet into Open Office, make sure you select each
column as type Text rather than Standard. Standard will reformat your
dates and numbers.

Your data is broken up into three sections representing individuals,
marriages, and children. The exported fields and column names are:

Individuals:

Person, Lastname, Firstname, Callname, Suffix, Prefix, Title, Gender,
Birthdate, Birthplace, Birthsource, Deathdate, Deathplace, Deathsource,
Note

Marriages:

Marriage, Husband, Wife, Date, Place, Source, Note

Families:

Family, Child

The first column in each area is the gramps ID. That is what will tie your
edits back to the correct data, so don't alter those data. Load this file
into your favorite spreadsheet using comma separated, double-quote text
deliminted, and Text format (any encoding for now). Then you can add or
correct data, and save it back out, keeping the same format. You can then
import the data back ontop of your old data and it will be corrected. (USE
TEST OR BACKUP DATA FOR NOW!)

Import
------

To import your data:

   1. place the file http://bubo.brynmawr.edu/~dblank/ImportCSV.py in your
gramps/plugins subdirectory.
   2. use the file from above, or create a spreadsheet (described below)
with genealogical data
   3. start up gramps and import the file into your current database

The merge part of this code will only add or update information to your
database, and it always assume that the spreadsheet data is the correct
version.

The spreadsheet is data made up of columns. Each column should have at the
top of it the name of what type of data is in the column. You must use
special names for the columns. Currently they are:

People:

person -  a reference to be used for families (marriages, and children)
grampsid - to assign a gramps id to the person
firstname - a person's first name
surname/lastname - a person's last name
callname - a common name (nickname) for the person
prefix - surname prefix (von, de, etc)
suffix - a suffex of a person's name (Jr., Sr.)
title - a person's title (Dr., Mr.)
gender - male or female (you should use the translation for your language)
note - a note for the person's record
birthdate - date of birth
birthplace - place of birth
birthsource - source material for birth
grampsid - give a particular gramps id
deathdate - date of death
deathplace - place of death
deathsource - source material for death
deathcause - cause of death

Marriages:

marriage - if you want to reference this from a family, you'll need a
matching name here
husband/father/parent1 - the reference of the person above who is the husband
                         (for female parent1, you'll need to put gender in
the person area,
                         or edit it later in gramps)
wife/mother/parent2 - the reference of the person above who is the wife
                         (for male parent2, you'll need to put gender in
the person area,
                         or edit it later in gramps)
date - the date of the marriage
place - the place of the marriage
source - source materials of the marriage
note - a note about the marriage/wedding

Families:

family - a reference to tie this to a marriage above (required)
child - the reference of the person above who is a child
source - source materials of the marriage
note - a note about the family
gender - male or female (you should use the translation for your language)
         [You can put gender here, or in person above]

Case doesn't matter. Notice that the names don't use underscores in them.
You may use any combination of these, in any order. (Actually, you have to
at least have a surname and a firstname when defining a person, and you
have to have a marriage and child columns when defining children, but
that's about it.) The column names are the English names given (for now)
but the data should be in your language (including the words "male" and
"female").

Each of these can go in its own area in a spreadsheet. There is no limit
to the number of areas in a sheet, and each area can have any number of
rows. Leave a blank row between "areas". Just make sure that areas are not
next to each other; they must be above and below one another.

You can have mutiple areas of each kind on a spreadsheet. The only
limitation is that if you refer to a person, you must do that in a row
lower than where that person is described. Likewise, if you refer to a
marriage, you must do that in a row lower than where the marriage is
described.

Here is an example spreadsheet in Open Office, but any spreadsheet program
should work.

http://bubo.brynmawr.edu/~dblank/test-csv.png

Notice that the data need not begin in the first column, nor in the first
row.

And here is the resulting data in gramps:

http://bubo.brynmawr.edu/~dblank/Screenshot-1.png

Here is an example of a CSV text spreadsheet with multiple areas:

Firstname, Surname, Birthdate
John,      Tester,  11/11/1965
Sally,     Tester,  01/26/1973

Person, Firstname, Surname
p1,     Tom,       Smith
p2,     Mary,      Jones
p3,     Jonnie,    Smith
p5,     James,     Loucher
p6,     Penny,     Armbruster
p7,     Tim,       Sparklet

Marriage, Husband, Wife
m1,       p1,      p2
m2,       p5,      p6

Family, Child
m1,     p3
m1,     p6
m2,     p7

If you cut and paste that into a file, you can import it directly.

If you make your references be gramps IDs inside square brackets, then you
can refer to people already in the database, like this:

Person,    Firstname, Lastname
joe's boy, Harry,     Smith

Family,  Child
[F1524], joe's boy

Husband, Wife
[I0123], [I0562]

firstname, surname
Timothy, Jones

This example would create and add Harry Smith to the previously existing
family in gramps, family F1524.

Also, this example would marry two previously existing people, I0123, and
I0562.

Finally, this also creates a person named Timothy Jones who is not related
to anyone.

Real World Example

In this example, I had an entire generation to enter, 16 names with
marriage dates. The children I already had in the database. I entered them
into Open Office:

http://bubo.brynmawr.edu/~dblank/gen5-spreadsheet.png

Notice that you can use numbers or strings as the reference names between
areas. In the person area, I used the numbers 1 through 16. That made it
easy to refer to them in the second area of marriages. The marriages are
labeled with the letters A through H.

HINT: make sure that autoformatting is off when you open it in your
spreadsheet application. Otherwise, the spreadsheet program may be storing
dates in another format.

Also note that the children in the third area are existing people as
indicated by the brackets around the gramps IDs.

Saving as CSV and importing into gramps produces the far right-hand column
in the tree:

http://bubo.brynmawr.edu/~dblank/gen5-gramps.png

TODO:

   1. Import/Export in UTF-8 format.
   2. Update the import screen to better match options for this export.

Please feel free to provide comments or suggestions on the gramps mailing
list (or right here in the wiki).