Difference between revisions of "Gramps 3.0 Wiki Manual - Manage Family Trees: CSV Import and Export"

From Gramps
Jump to: navigation, search
(New page: = Gramps Spreadsheet Import/Export = 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 sp...)
 
 
(13 intermediate revisions by 3 users not shown)
Line 1: Line 1:
= Gramps Spreadsheet Import/Export =
+
{{grampsmanualcopyright}}
  
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:
+
{{languages}}
  
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.
+
== Gramps Spreadsheet Import/Export ==
  
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.
+
This format 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. It is also easy to write by hand.
  
= Export =
+
NOTE: '''This spreadsheet format does not allow for 100% export of GRAMPS data.''' It only exports (and imports) a subset of data, namely: people (names), birth and death events, and relationships (parents and children).
 +
 
 +
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 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. An example of this is [[Narrative_Website_Import|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 ==
  
 
To export your database:
 
To export your database:
  
1. Start gramps
+
# Start gramps
2. Select "Export" from the File menu
+
# Select "Export" from the File menu
3. Select "Comma Separated Values Spreadsheet (CSV)"
+
# 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.  
 
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.  
Line 37: Line 43:
 
Family, Child
 
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!)
+
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 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 ontop of your old data and it will be corrected.
  
= Import =
+
HINT: Open Office allows you to turn off autoformatting when you open the CSV file. If you don't do this, Open Office may interpret the dates incorrectly. Change the type of the column to Text rather than Standard. If your spreadsheet program doesn't allow you to format the fields before you get it into columns (eg, Excel) you need to change the display format of dates in gramps before you export. You can do this under Edit -> Preferences -> Display -> Date Format.
 +
 
 +
== Import ==
  
 
To import your data:
 
To import your data:
  
1. use the file from above, or create a spreadsheet (described below) with genealogical data
+
# use the file from above, or create a spreadsheet (described below) with genealogical data
2. start up gramps
+
# start up gramps
3. import the file into your current database
+
# 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 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.
Line 53: Line 61:
 
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:
 
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 ==
+
=== People ===
  
{{{
+
<pre>
 
person -  a reference to be used for families (marriages, and children)  
 
person -  a reference to be used for families (marriages, and children)  
 
grampsid - to assign a gramps id to the person
 
grampsid - to assign a gramps id to the person
Line 74: Line 82:
 
deathsource - source material for death
 
deathsource - source material for death
 
deathcause - cause of death
 
deathcause - cause of death
}}}
+
</pre>
  
== Marriage ==
+
=== Marriage ===
  
{{{
+
<pre>
 
marriage - if you want to reference this from a family, you'll need a matching name here
 
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  
 
husband/father/parent1 - the reference of the person above who is the husband  
Line 90: Line 98:
 
source - source materials of the marriage
 
source - source materials of the marriage
 
note - a note about the marriage/wedding
 
note - a note about the marriage/wedding
}}}
+
</pre>
  
== Family ==
+
=== Family ===
  
{{{
+
<pre>
 
family - a reference to tie this to a marriage above (required)
 
family - a reference to tie this to a marriage above (required)
 
child - the reference of the person above who is a child
 
child - the reference of the person above who is a child
Line 101: Line 109:
 
gender - male or female (you should use the translation for your language)  
 
gender - male or female (you should use the translation for your language)  
 
         [You can put gender here, or in person above]
 
         [You can put gender here, or in person above]
}}}
+
</pre>
  
 
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").
 
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").
Line 111: Line 119:
 
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:
 
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:
  
{{{
+
<pre>
 
marriage, person1, person2, place
 
marriage, person1, person2, place
 
m1, p1, p2,"Clinton, Co., MO"
 
m1, p1, p2,"Clinton, Co., MO"
 
m2, p3, p4,"Havertown, PA"
 
m2, p3, p4,"Havertown, PA"
}}}
+
</pre>
  
 
A spreadsheet program will do this automatically for you.
 
A spreadsheet program will do this automatically for you.
Line 121: Line 129:
 
Here is an example spreadsheet in Open Office, but any spreadsheet program should work.
 
Here is an example spreadsheet in Open Office, but any spreadsheet program should work.
  
http://bubo.brynmawr.edu/~dblank/test-csv.png
+
[[Image:Test-csv.jpg]]
  
 
Notice that the data need not begin in the first column, nor in the first row.
 
Notice that the data need not begin in the first column, nor in the first row.
Line 127: Line 135:
 
And here is the resulting data in gramps:
 
And here is the resulting data in gramps:
  
http://bubo.brynmawr.edu/~dblank/Screenshot-1.png
+
[[Image:Screenshot-1.jpg]]
  
 
Here is an example of a CSV text spreadsheet with multiple areas:
 
Here is an example of a CSV text spreadsheet with multiple areas:
  
{{{
+
<pre>
 
Firstname, Surname, Birthdate
 
Firstname, Surname, Birthdate
 
John,      Tester,  11/11/1965
 
John,      Tester,  11/11/1965
Line 152: Line 160:
 
m1,    p6
 
m1,    p6
 
m2,    p7
 
m2,    p7
}}}
+
</pre>
  
 
If you cut and paste that into a file, you can import it directly.
 
If you cut and paste that into a file, you can import it directly.
Line 158: Line 166:
 
If you make your references be gramps IDs inside square brackets, then you can refer to people already in the database, like this:
 
If you make your references be gramps IDs inside square brackets, then you can refer to people already in the database, like this:
  
{{{
+
<pre>
 
Person,    Firstname, Lastname
 
Person,    Firstname, Lastname
 
joe's boy, Harry,    Smith
 
joe's boy, Harry,    Smith
Line 170: Line 178:
 
firstname, surname
 
firstname, surname
 
Timothy, Jones
 
Timothy, Jones
}}}
+
</pre>
  
 
This example would create and add Harry Smith to the previously existing family in gramps, family F1524.
 
This example would create and add Harry Smith to the previously existing family in gramps, family F1524.
Line 178: Line 186:
 
Finally, this also creates a person named Timothy Jones who is not related to anyone.
 
Finally, this also creates a person named Timothy Jones who is not related to anyone.
  
== Real World Example ==
+
=== 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:
 
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
+
[[Image:Gen5-spreadsheet.jpg]]
  
 
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.
 
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.
Line 192: Line 200:
 
Saving as CSV and importing into gramps produces the far right-hand column in the tree:
 
Saving as CSV and importing into gramps produces the far right-hand column in the tree:
  
http://bubo.brynmawr.edu/~dblank/gen5-gramps.png
+
[[Image:Gen5-gramps.jpg]]
 +
 
 +
 
 +
{{man index|Gramps_3.0_Wiki_Manual_-_Manage_Family_Trees#Importing_Data|Gramps_3.0_Wiki_Manual_-_Manage_Family_Trees#Exporting_Data|3.0}}
 +
 
  
Please feel free to provide comments or suggestions on the gramps mailing list (or right here).
+
[[Category:Documentation]]

Latest revision as of 09:28, 6 February 2009

Gnome-important.png Special copyright notice: All edits to this page need to be under two different copyright licenses:

These licenses allow the Gramps project to maximally use this wiki manual as free content in future Gramps versions. If you do not agree with this dual license, then do not edit this page. You may only link to other pages within the wiki which fall only under the GFDL license via external links (using the syntax: [https://www.gramps-project.org/...]), not via internal links.
Also, only use the known Typographical conventions



Gramps Spreadsheet Import/Export

This format 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. It is also easy to write by hand.

NOTE: This spreadsheet format does not allow for 100% export of GRAMPS data. It only exports (and imports) a subset of data, namely: people (names), birth and death events, and relationships (parents and children).

There are three main uses for this format:

  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. An example of this is restoring your GRAMPS database by loading the Narrative Website into a spreadsheet.
  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. Start gramps
  2. Select "Export" from the File menu
  3. 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.

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 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 ontop of your old data and it will be corrected.

HINT: Open Office allows you to turn off autoformatting when you open the CSV file. If you don't do this, Open Office may interpret the dates incorrectly. Change the type of the column to Text rather than Standard. If your spreadsheet program doesn't allow you to format the fields before you get it into columns (eg, Excel) you need to change the display format of dates in gramps before you export. You can do this under Edit -> Preferences -> Display -> Date Format.

Import

To import your data:

  1. use the file from above, or create a spreadsheet (described below) with genealogical data
  2. start up gramps
  3. 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.

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

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
source - source materials 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 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.

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, person1, person2, 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 Open Office, but any spreadsheet program should work.

Test-csv.jpg

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

And here is the resulting data in gramps:

Screenshot-1.jpg

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:

Gen5-spreadsheet.jpg

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: Open Office allows you to turn off autoformatting when you open the CSV file. If you don't do this, Open Office may interpret the dates incorrectly. Change the type of the column to Text rather than Standard. If your spreadsheet program doesn't allow you to format the fields before you get it into columns (eg, Excel) you need to change the display format of dates in gramps before you export. You can do this under Edit -> Preferences -> Display -> Date 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:

Gen5-gramps.jpg


Previous Index Next