*Importing from Excel

Importing from or exporting to another genealogy program. This is the place to ask.
jfw
Newbie
Posts: 2
Joined: 21 Mar 2015 14:00
Family Historian: V6

Importing from Excel

Postby jfw » 21 Mar 2015 14:09

I am considering buying FH but I have a lot of data already in Access. Access talks to nothing helpful except Excel and I have been told that FH will import from there but can find no reference to it apart from a bit of code which meant nothing to a non-programmer. Please can anyone first confirm that FH will in fact import Excel data? If it will - does FH give its own field definitions anywhere so that I can make my own data fields match? I intend to buy the printed manual but would like to have this info confirmed before I purchase as I don't fancy re-keying several thousand records!

User avatar
tatewise
Megastar
Posts: 15426
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: Importing from Excel

Postby tatewise » 21 Mar 2015 14:43

Welcome to the FHUG.
FH will NOT import directly from Access or Excel data.
It only imports GEDCOM files, or from a few other genealogy program custom databases.

There are some Knowledge Base > Downloads ~ GEDCOM Utilities written by other FHUG users that can convert CSV fies into a GEDCOM file, but whether they are suitable for a non-programmer is debatable:
Knowledge Base > Utility ~ ACC2GED (CSV to GEDCOM)
Knowledge Base > Utility ~ Census CSV to GEDCOM Conversion
Knowledge Base > Utility ~ FIX2GED and GED2FIX

There is a Build a Tree from a CSV Plugin, that will also be a steep learning curve for a non-programmer.

The users here would probably be prepared to help you up the learning curve.
I doubt if any genealogy program will import your custom Access/Excel data.

BTW: The manual is a free PDF download from FH Download Archive via Book tab.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

jfw
Newbie
Posts: 2
Joined: 21 Mar 2015 14:00
Family Historian: V6

Re: Importing from Excel

Postby jfw » 21 Mar 2015 15:26

Thank you for an honest answer - I will stick with Access and go on drawing trees in Word.

User avatar
tatewise
Megastar
Posts: 15426
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: Importing from Excel

Postby tatewise » 21 Mar 2015 15:32

If you could post some examples of the CSV record formats, someone should be able to say how easy they would be to convert.
It all depends on how sophisticated the CSV record structures happen to be.

If they can be converted, then it would save you a lot of time drawing trees by hand, and the whole family history experience would be so much more productive.

I presume you've taken the FH Tour to get a taste of its features.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

User avatar
jimlad68
Megastar
Posts: 611
Joined: 18 May 2014 21:01
Family Historian: V6.2
Location: Sheffield, Yorkshire, UK (but from Lancashire)
Contact:

Re: Importing from Excel

Postby jimlad68 » 21 Mar 2015 17:19

jfw, if you can get your head around Access and drawing trees in Word, I would have thought a trial run of acc2ged should be a doddle. Certainly worth a few minutes to experiment. Perhaps you are not used to running DOS programs, I find you just have to be a bit more prepared and learn how to copy/paste in the DOS window.
Jim Orrell - researching: see - but way out of date http://wc.rootsweb.com/cgi-bin/igm.cgi?db=james-orrell3

RSellens
Famous
Posts: 171
Joined: 07 Aug 2005 17:25
Family Historian: V6.2

Re: Importing from Excel

Postby RSellens » 22 Mar 2015 18:55

If your spreadsheet formats is not to complex, and your willing to provide a sample set of data, i am more than willing to do you a simple plugin to get you started....I have done a few plugins that take military details from the CWCG site an build the individuals withing a blank GEDCOM file.
You will probably still have to do some clean-up/manual entry after the build, but it's better than a complete restart.


Richard

juliusrw
Newbie
Posts: 4
Joined: 08 Apr 2015 20:09
Family Historian: V6

Re: Importing from Excel

Postby juliusrw » 08 Apr 2015 20:21

similar problem. I hhave been sent an excel epreadsheet with c 5000 names - so it would be good not to have to key them in! I have tried to use the "Build Tree from csv" plug in.
I am trying to import into and empty database
The csv looks like ...

ID,Forenames,Surname,Sex,BirthDate,BirthPlace,DeathDate,DeathPlace,Mother,Father,Spouse1,Spouse2
5,Vogel/Mamel,LEWY,F,± 1681 ,Saarwellingen,,,4,3,2,
6,Karoline,CAAN,F,± 1705 ,Saarwellingen,> 1760 ,Saarwellingen,2,5,7,
7,Moses,ARON,M,± 1700 ,,> 1770 ,Saarwellingen,,,6,

I get the folowing error message Original Record ids are stored in the Custom ID field. Use the Work with Record Identifiers Tool to copy them to the FH record IDs if required.

What am I doing wrong - and if possible advice on how I can import some additional fields (MarriageDate, MarriagePlace, Occupations).

Thanks

User avatar
tatewise
Megastar
Posts: 15426
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: Importing from Excel

Postby tatewise » 08 Apr 2015 21:29

That is not an error message but an instruction to complete the job.
Your CSV has ID for each row and the links to Father, Mother, and two Spouses.
The Plugin cannot create Record Id within FH, so it uses the Custom Id field.
To get the Custom Id numbers into Record Id use Tools > Work with Record Identifiers and choose that option.
When that has been done all the family relationships should get establish via the now correct Record Id.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

juliusrw
Newbie
Posts: 4
Joined: 08 Apr 2015 20:09
Family Historian: V6

Re: Importing from Excel

Postby juliusrw » 09 Apr 2015 12:49

Mike
Thank you for replying so quickly.
That does not seem to work ( no doubt I am doing something dim). I go to Tools - Record Identifiers - and the had tried the options to generate new record ids, or to copy existing record ids (I have tried each of options).
If I have an empy project it says no records of the type (individual etc) available
If I put a dummy record in - it works on that record, but not on those I want to import. It looks as if the record identifier tool is not working on the import
It does not seem to make any difference if I tun the Record identifier macro before the csv import plug in, or when the plug in stops, in each case I get the same message.
thanks
Julius

User avatar
tatewise
Megastar
Posts: 15426
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: Importing from Excel

Postby tatewise » 09 Apr 2015 13:36

First you run the Plugin to import your CSV records into Project records.

You should be able to see all the new Individual Records, which each have Custom Id values, although they are only visible in the Property Box on the All tab.

Then as the Plugin requests, use the Tools > Work with Record Identifiers and Generate new record id by copying Custom Id values for the newly created Individual Records.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

juliusrw
Newbie
Posts: 4
Joined: 08 Apr 2015 20:09
Family Historian: V6

Re: Importing from Excel

Postby juliusrw » 09 Apr 2015 14:23

Thanks

Understood
That is what I have been doing - but nothing gets imported, so no records to change record identifiers on.
Do you happen to have a dummy csv that is know to import I can run?

Julius

User avatar
tatewise
Megastar
Posts: 15426
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: Importing from Excel

Postby tatewise » 09 Apr 2015 14:45

I have just run the unchanged Build Tree From CSV Plugin using the CSV data you supplied below, and it imported those 3 records perfectly as 3 Individual and 3 Family records. Then using Tools > Work with Record Identifiers it joined them into a Gedcom Project family.

The CSV.csv file I used is attached.

Have you created an empty Project initially in which to run the Plugin?
Use File > Project Wndow > New Project and delete the one root record it insists on creating.

Where have you looked for your imported Individual Records?
Try the Records Window.
Or is the Plugin saying 0 Individual Records Created...?
If so, you are not using a plain text CSV file.

Is your CSV a plain text .csv file, or are you talking about a Spreadsheet file such as Excel (.xlsx) that you have renamed to .csv instead of using Save As?

PS EDIT:-
The Dates in your CSV do NOT import well.
Instead of ± 1705 use circa 1705 or ABT 1705
Instead of > 1705 use AFT 1705
Instead of < 1705 use BEF 1705
If you have other Date formats let me know.
Attachments
CSV.csv
(283 Bytes) Downloaded 94 times
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

juliusrw
Newbie
Posts: 4
Joined: 08 Apr 2015 20:09
Family Historian: V6

Re: Importing from Excel

Postby juliusrw » 09 Apr 2015 22:22

Wonderful, that did the trick, It must have been the date format.

I had worked out how to get an empty project

The file is a true csv - even if you save Excel files as csv some import routines are sniffy about them. So I routinely take them through notepad and resave. I had tried both ANSI and utf-8 as well - your recoding of the dates in the test file has worked fine.
So next a bit of find and replacing in the original excel file.

I may look at the original plug-in to see if I can get it to import the occupation and marriage date fields I have - any tips?

Many thanks for your help

Julius

User avatar
tatewise
Megastar
Posts: 15426
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: Importing from Excel

Postby tatewise » 10 Apr 2015 10:55

Oddly my first import of the test data worked OK with original Date formats.

See the Knowledge Base > Family Historian Plugins especially under the Developer Guide for advice on Plugins.

To add Occupation and Marriage details you obviously need to add columns to your data depending on exactly what details you have. e.g.
OccuDate, OccuPlace, Occupation,
Marr1Date, Marr1Place,
Marr2Date, Marr2Place (these two for Spouse2 if any)

The two Plugin functions that will need modify are: CheckFamily() and AddEvent()

AddEvent() is easy to change and needs an extra parameter occupation.
The code will be similar to that for eventplace except the pointer will be ptrEvent that already exists, because the occupation text is added directly to the OCCU attribute and not a sub-field.
(Be aware the comments on eventplace are wrong, having been pasted from eventdate and not corrected.)

Code: Select all

   if occupation ~= nil then
      fhSetValueAsText(ptrEvent, occupation)  -- set value of occupation using parameter
   end

Finally the main function needs to add:

Code: Select all

   AddEvent(ptrind,'OCCU',data['OccuDate'],data['OccuPlace'],data['Occupation'])

CheckFamily() needs to return ptrFam pointer to Family record to allow addition of MARRiage event.
Then in main function you need to change:

Code: Select all

if data['Spouse1'] ~= nil then
   local ptrfam = CheckFamily(data['ID'],data['Spouse1'],individual[data['ID']],data['Sex'],'SPOU')
   AddEvent(ptrfam,'MARR',data['Marr1Date'],data['Marr1Place'])
end
if data['Spouse2'] ~= nil then
   local ptrfam = CheckFamily(data['ID'],data['Spouse2'],individual[data['ID']],data['Sex'],'SPOU')
   AddEvent(ptrfam,'MARR',data['Marr2Date'],data['Marr2Place'])
end

If anything does not make sense then please ask.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry


Return to “Importing and Exporting”

Who is online

Users browsing this forum: No registered users and 1 guest