Gramps 5.2 Wiki Manual - Manage Family Trees: CSV Import and Export

Languages:

English • Deutsch • español • suomi • français • עברית • italiano • македонски • Nederlands • polski • русский • shqip • slovenčina • Türkçe

This section relates to using Gramps with the Comma Separated Values Spreadsheet(CSV) format.

You can also Export the current list view to a Spreadsheet(*.ODT) or CSV file.

Gramps Spreadsheet Import/Export

This format allows you to import/export a spreadsheet of data all at once. By default, the spreadsheet must be in the Comma Separated Value (CSV) format. (Although alternative Delimiters can be specified in the View -> Configure options in list views.) Most spreadsheet programs can read and write this format. It is also easy to write by hand. This is the only Gramps import format which allows for merging with existing data.

This spreadsheet format does not allow for 100% export of Gramps data.

It only exports (and imports) a subset of data, namely: people (names, gender, birth, baptism, death, and burial dates/places/sources); marriages (dates/places/sources); relationships (parents and children); and places (title, name, type, latitude, longitude, code, enclosed by, and enclosed date). Notes are not exported, but new notes are appended onto the end of existing notes.

Both the CSV import and export use the Place Format setting (in the Display tab of Edit -> Preferences) for places.

For most uses, the 'Full' setting for Place Formats should be used. If the place format setting doesn't match the CSV file for import, you may get the same place created more than once.

There are three main uses for this format:

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.
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 bring 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. An example of this is restoring your Gramps database by loading the Narrative Website into a spreadsheet.
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

Fig. 6.1 Export Assistant - Export Options - wizard dialog (showing defaults for "Comma Separated Values Spreadsheet(CSV)") with highlight Bottom section for File format specific options

To export your database:

Start Gramps
Load the Family Tree to be exported
Select from the menu Family Trees -> Export...
Select Next on the Saving your data window.
Select Comma Separated Values Spreadsheet (CSV) on the Choose the output format window
On the Export options window:
1. In the top section, select which filters to apply to your family tree
2. From the checkboxes, select which items to include in the export (people, marriages, children, places) and whether to Translate headers into the language currently being used.
On the Select Save file window, select the destination filename and path
On the Final confirmation window, verify the setting and click the Apply button to begin the actual export.

A subset of fields containing your genealogy data will be saved to a .csv file in the format described below. The people and families are annotated in such a way that allows the data to be edited and overwrite the outdated information as they are Imported back in. The annotations allow updating the Tree database via Import.

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 are never exported with this tool.

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

Places: Place, Title, Name, Type, Latitude, Longitude, Code, Enclosed_by, Date

Individuals: Person, Lastname, Firstname, Callname, Suffix, Prefix, Title, Gender, Birthdate, Birthplace (or Birthplaceid), Birthsource, Baptismdate, Baptismplace (or Baptismplaceid), Baptismsource, Deathdate, Deathplace (or Deathplaceid), Deathsource, Burialdate, Burialplace (or Burialplaceid), Burialsource, Note

Marriages: Marriage, Husband, Wife, Date, Place (or Placeid), Source, Note

Families: Family, Child

The first column in each area is the Gramps ID. That is what will connect your edits back to the appropriate data, so do not alter those IDs. Load this file into your favorite spreadsheet using comma separated, double-quote text delimited, 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 on top of your old data and it will be corrected.

LibreOffice allows auto-formatting to be turned off when opening the CSV file.

Auto-formatting may cause LibreOffice to interpret the dates incorrectly. Change the type of the column to 'Text' rather than 'Standard'. If your spreadsheet program doesn't allow formatting the fields before imported the data into columns, it will be necessary to change the dates display format in Gramps before exporting. This is done with Edit -> Preferences -> Display -> Date Format

.

Excel allows formatting columns as 'text' while opening the CSV file.

If not overridden, Excel may interpret the dates incorrectly. Change the type of the column to 'Text' rather than 'General'. Open the CSV from the file menu (select 'Text files' as the type in the file open dialog box). This brings up the 'Text Import Wizard' which allows selecting 'Delimited' by commas, and selecting 'Text' for all the columns. (To Select all columns, click the first column, scroll to last and shift-click it.)

.

Excel cannot directly save proper Unicode CSV files.

To save a CSV in Unicode, save to "Unicode Text (*.txt)", then open the file in Notepad++. Using Notepad++ 'Search' and replace menu, change all the tabs ('/t') to commas (','). Using Notepad++ 'Encoding' menu convert to "UTF-8-BOM" and save the file to CSV ('*.csv').

Import

To import your data:

Use the file from above, or create a spreadsheet (described below) with genealogical data
Start Gramps
Create a new Family Tree
Select from the menu Family Trees ->Import...
Select the Comma Separated Values Spreadsheet (CSV) file and then select the Import button

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.

If you load this spreadsheet into LibreOffice, make sure you select each column as type Text rather than Standard. Standard will reformat your dates and numbers. Also, if you use Excel, you will probably want to select all cells once opened, and change the format of the cells to Text.

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. The first column in each area is the Gramps ID reference. You must use special names for the columns. They are:

Place

place - a reference to this place
title - title of place
name - name of place
type - type of place (eg, City, County, State, etc.)
latitude - latitude of place
longitude - longitude of place
code - postal code, etc.
enclosed_by - the reference to another place that encloses this one
date - date that the enclosed_by place was in effect

People

person -  a reference to be used for families (marriages, and children) 
grampsid - to assign a Gramps id to the person
firstname/first_name/given_name/given - a person's first name
surname/lastname/last_name - a person's last name
callname/call_name/call - a common name (nickname) for the person
prefix - surname prefix (von, de, etc)
suffix - a suffix 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)
source - source title for person
note - a note for the person's record

birthdate - date of birth
birthplace - place of birth
birthplaceid/birthplace_id/birth_place_id - place id of birth
birthsource - source title for birth

baptismdate - date of baptism
baptismplace - place of baptism
baptismplaceid - place id of baptism
baptismsource - source title of baptism

deathdate - date of death
deathplace - place of death
deathplaceid - place id of death
deathsource - source title for death
deathcause - cause of death

burialdate - date of burial
burialplace - place of burial
burialplaceid - place id of burial
burialsource - source title of burial

occupationdate - date of occupation
occupationplace - place of occupation
occupationplace_id - place id of occupation
occupationsource - source title of occupation
occupationdescr - description of occupation

residencedate - date of residence
residenceplace - place of residence
residenceplace_id - place id of residence
residencesource - source title of residence

attributetype - type of attribute
attributevalue - value of attribute
attributesource - source title of attribute

Marriage

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
placeid - the place id of the marriage
source - source title of the marriage
note - a note about the marriage/wedding

Family

family - a reference to tie this to a marriage above (required)
child - the reference of the person above who is a child
source - source title 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]

Details

Column names are not case-sensitive. You may use any combination of the columns, in any order. (Actually, you have to at least have a surname and a given name when defining a person, you have to have a marriage and child columns when defining children, and places need a place reference, but that is 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").

Top-to-bottom order is important in that if you want to reference something in one area to another, the definition MUST come first. For example, if you want to define families of people, the individuals must be defined before the families. The same applies to places. So it is usually best to put the Places data first, people next, then marriages and families.

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 multiple 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. References to enclosed_by places must already exist in the database, or be defined in rows above in the spreadsheet.

If you use the 'grampsid' as a way to assign specific ids, be very careful when importing to a current database. Any data you enter will overwrite the data assigned to that grampsid. If you use ids in the place, person, marriage, or family columns that are surrounded by brackets (for example '[I0001]'), the values you use will be interpreted as grampsids as well. If you are adding new items, you are encouraged to avoid use of the bracket format or grampsid columns, so as to avoid accidentally overwriting your data. If you are mixing the bracket (or grampsid) methods with plain references (no brackets), put the plain referenced data after the bracket referenced data.

If you are entering the data in a text file, and if you wish to have a comma inside one of the values, like "Clinton, Co., MO" then you need place the entire value in double-quotes and put the first double-quote right after the preceding comma. For example:

marriage, parent1, parent2, place
m1, p1, p2,"Clinton, Co., MO"
m2, p3, p4,"Havertown, PA"

A spreadsheet program will do this automatically for you.

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

Fig. 6.2

,,,,,,,,,
,"Firstname","Surname","Callname","Gender","Prefix","Suffix","Title","Note","Grampsid"
,"Douglas","Test","Doug","male","Von","Sr.","Dr.","This is not related","I0007"
,"Laura","Test",,"female",,,,,

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

And here is the resulting data in Gramps:

Fig. 6.3

Example CSV with multiple areas

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

Place, Title, Name, Type
[P0001], Michigan, Michigan, State
L1, Canada, Canada, Country
L2, USA, USA, Country

Firstname, Surname, Birthdate, Birth place id
John,      Tester,  11/11/1965, L1
Sally,     Tester,  01/26/1973, L1

Person, Firstname, Surname, Birthdate,    Birth place id
p1,     Tom,       Smith,   22 Jan 1970, [P0001]
p2,     Mary,      Jones
p3,     Jonnie,    Smith
p5,     James,     Loucher
p6,     Penny,     Armbruster
[P0002],Tim,       Sparklet

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

Family, Child
m1,     p3
m1,     p6
m2,     [P0002]

If you cut and paste that into a file (or use the Addon:Import Gramplet), you can import it directly.

A date can be any valid Gramps date, including dates formats like "26 JAN 1973" or "26.1.1973".

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

place, enclosed_by
[P0001], [P0002]

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.

This also creates a person named Timothy Jones who is not related to anyone.

Finally, this also make place P0001 be enclosed by place P0002.

Example CSV from Spreadsheet

Fig. 6.4

Using Gramps Example.gramps for this example. The children already exist in the Family Tree. So you can enter an entire generation (8 names with marriage dates) into LibreOffice Calc.

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

Also note that in the spreadsheet the children in the third area are existing people in Gramps 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.

Fig. 6.5 Saving as CSV and importing into Gramps produces the far right-hand column in the tree.(Highlighted in yellow)

Contents of CSV file gen4-test.csv

,,,
,,,
"Person","Firstname","Lastname",
1,"Peter","Blank",
2,"Anna Maria","Kiefer",
3,"Georg","Schmidt",
4,"Barbara","Goering",
5,"Johann","Kiefer",
6,"Fides","Federer",
7,"Sebastian","Schelli",
8,"Magdelena","Schlegel",
,,,
,,,
"Marriage","Husband","Wife","Date"
"A",1,2,"28 jan 1712"
"B",3,4,"4 may 1732"
"C",5,6,02/07/1930
"D",7,8,17/08/1927
,,,
"Family","Child",,
"A","[I0104]",,
"B","[I0105]",,
"C","[I0972]",,
"D","[I0973]",,

Gramps 5.2 Wiki Manual - Manage Family Trees: CSV Import and Export

Contents

Gramps Spreadsheet Import/Export

Export

Import

Place

People

Marriage

Family

Details

Example CSV with multiple areas

Example CSV from Spreadsheet

See also

Navigation menu

Personal tools

Namespaces

Variants

Views

More

Search

Navigation

Contributor help pages

wiki

Tools