* Importing from Excel

[jfw]

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!

[tatewise]

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 fhugdownloads:gedcom|> 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:
fhugdownloads:contents:acc2ged|> Utility ~ ACC2GED (CSV to GEDCOM)
fhugdownloads:contents:csvtogedcomcensusimport.zip|> Utility ~ Census CSV to GEDCOM Conversion
fhugdownloads:contents:fix2ged_ged2fix|> 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.

[jfw]

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

[tatewise]

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.

[jimlad68]

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.

[RSellens]

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]

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

[tatewise]

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.

[juliusrw]

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

[tatewise]

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.

[juliusrw]

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

[tatewise]

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.

[juliusrw]

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

[tatewise]

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

See the plugins:index|> 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.