-
Only registered members can see all the forums - if you've received an invitation to join (it'll be on your My Summary page) please register NOW!
-
It's easier than ever before to check your entries from the 1881 Census - more details here
Excel to GEDCom conversion
- Thread starter Janet
- Start date
Hi Janet, Welcome to the forum.
I did a quick Google search that came up with some results. I've never tried any of them so can't comment.
Only advice I can give is to test these on a copy of your original data.
Let us know how you get on.
I did a quick Google search that came up with some results. I've never tried any of them so can't comment.
Only advice I can give is to test these on a copy of your original data.
Let us know how you get on.
Hi Janet.
The problem with what you suggest is that unless your data is highly structured you are unlikely to be able to export it in GEDCOM format.
Most Excel files I've seen have some sort of pictorial representation of families rather than columnar representation. Thus it would be impossible. If you could show a snippet of the layout of some of your data I might be able to advise further.
The problem with what you suggest is that unless your data is highly structured you are unlikely to be able to export it in GEDCOM format.
Most Excel files I've seen have some sort of pictorial representation of families rather than columnar representation. Thus it would be impossible. If you could show a snippet of the layout of some of your data I might be able to advise further.
AndyMick
LostCousins Star
It IS possible if you have all the right information. Initially I had only the father information for a person, so my conversion to GEDCOM didn't get it right with regard to the correct mother. That's been fixed now, still not optimal, but it works. So as Alexander says, it depends on your data format.
Thanks for the replies. For my family tree the info is held in data-only tables (but of my own design not GEDCOM), with other worksheets used for the graphics side of things. But pasting in a couple of lines of the data table here to show you didn't work very well so I'm not sure how to get the snippet to you, Alexander. What's best to do, do you think?
Tims suggestion is a good one them more eyes than just mine will see it.
Ahh, sorry, I think there is a restriction on "New Members" for the safety of the site, that stops people with less then x posts uploading files. I think x might be 5. As that was your 5th post, can you check again?
Bryman
LostCousins Megastar
The Sample FTree xls file was uploaded just fine and I have been able to view, so congratulations on getting that far.
It looks as if you have most of the necessary information and generally in a form that could be converted to Gedcom format but might need a tailored program to achieve that. Unless some of the utilities suggested by Tim can accept column titles to define the types of information that the data relates to, ie POB in line 2 to denote Place of Birth, etc. Since you have data duplicated from several censuses, you would need to specify which data values should be preferred (or delete the unwanted values).
Also, some of the data would need to be manually 'enhanced' to include county/country for places and to make dates acceptable, ie DOB value of 1882/3 should be changed to a single year depending on the month in which the birth took place. There could be quite a lot of work to do in reviewing/modifying each entry before running the conversion.
It might need a bit of trial and error to get the output exactly right but what you have so far looks promising. Even if the chosen utility does not get all of the information into Gedcom format, it should be possible to achieve most and greatly lessen the amount of manual tweaking needed later. Alexander/Tim can probably be more specific.
It looks as if you have most of the necessary information and generally in a form that could be converted to Gedcom format but might need a tailored program to achieve that. Unless some of the utilities suggested by Tim can accept column titles to define the types of information that the data relates to, ie POB in line 2 to denote Place of Birth, etc. Since you have data duplicated from several censuses, you would need to specify which data values should be preferred (or delete the unwanted values).
Also, some of the data would need to be manually 'enhanced' to include county/country for places and to make dates acceptable, ie DOB value of 1882/3 should be changed to a single year depending on the month in which the birth took place. There could be quite a lot of work to do in reviewing/modifying each entry before running the conversion.
It might need a bit of trial and error to get the output exactly right but what you have so far looks promising. Even if the chosen utility does not get all of the information into Gedcom format, it should be possible to achieve most and greatly lessen the amount of manual tweaking needed later. Alexander/Tim can probably be more specific.
Sorry but there just isn't going to be any utility out there that can do the work for you as that is a unique format with interdependencies. In particular what will be very difficult is the relationships. GEDCOM works on the principal of 2 main types of data about people, INDIviduals, FAMilies. Individuals can appear in many families. Typically one family as a child and one or more families as a parent. Each type of data INDI/FAM has then got a series of facts attached. eg: CENSus facts, BIRTh facts, DEATh facts etc. Your data isn't structured like that.
I suspect the very best you could achieve would be to have a list of individual with no relationships as it would take a herculean task to create families from the limited info in the spreadsheet. That's before even considering the completely non standard formats of data - eg: dates such as Bryman pointed out. (for info the GEDCOM equivalent of your census date of birth as 1882/3 would be ABT 1883 which is usually taken as shorthand for in 1883 or the year before). However your data also has ~1835 which is effectively the same thing ABT 1835. These sorts of inconsistencies would need to be fixed.
From the first line you could create GEDCOM FACTs eg:
1 CENS
2 DATE 1891
2 PLAC 8 Cedar Place, Armley
and
1 OCCU Druggist's clerk
2 DATE 1891
and
1 BIRT
2 DATE ABT 1883
However I can't see how you could create a family record. Which usually looks like...
0 @F1@ FAM
1 HUSB @I1@
1 WIFE @I2@
1 CHIL @I3@
1 CHIL @I4@
In order to convert that spreadsheet into a GEDCOM file you would need considerable programming skills to write a suitable conversion program and even then it would take a lot of trial and error, testing and fixing. A MASSIVE task not for the feint hearted. Sorry. I'm not sure if you are fluent in any computer programming languages but if not then you need to resign yourself to the fact that the spreadsheet is never going to convert to a GEDCOM in any meaningful way. If you are fluent in computer languages then be aware that even after a conversion you would need a huge amount of manual tiding up afterwards. You simply don't appear to have the data in the sheet necessary to build a GEDCOM without a lot of conversion work before hand.
Really sorry to be the bearer of bad news but this is not in any way a simple task. If it were my tree I'd start entering the data in a family tree program from scratch, I'd not even contemplate trying to write a conversion routine and I've got 35 years experience writing computer programs.
I suspect the very best you could achieve would be to have a list of individual with no relationships as it would take a herculean task to create families from the limited info in the spreadsheet. That's before even considering the completely non standard formats of data - eg: dates such as Bryman pointed out. (for info the GEDCOM equivalent of your census date of birth as 1882/3 would be ABT 1883 which is usually taken as shorthand for in 1883 or the year before). However your data also has ~1835 which is effectively the same thing ABT 1835. These sorts of inconsistencies would need to be fixed.
From the first line you could create GEDCOM FACTs eg:
1 CENS
2 DATE 1891
2 PLAC 8 Cedar Place, Armley
and
1 OCCU Druggist's clerk
2 DATE 1891
and
1 BIRT
2 DATE ABT 1883
However I can't see how you could create a family record. Which usually looks like...
0 @F1@ FAM
1 HUSB @I1@
1 WIFE @I2@
1 CHIL @I3@
1 CHIL @I4@
In order to convert that spreadsheet into a GEDCOM file you would need considerable programming skills to write a suitable conversion program and even then it would take a lot of trial and error, testing and fixing. A MASSIVE task not for the feint hearted. Sorry. I'm not sure if you are fluent in any computer programming languages but if not then you need to resign yourself to the fact that the spreadsheet is never going to convert to a GEDCOM in any meaningful way. If you are fluent in computer languages then be aware that even after a conversion you would need a huge amount of manual tiding up afterwards. You simply don't appear to have the data in the sheet necessary to build a GEDCOM without a lot of conversion work before hand.
Really sorry to be the bearer of bad news but this is not in any way a simple task. If it were my tree I'd start entering the data in a family tree program from scratch, I'd not even contemplate trying to write a conversion routine and I've got 35 years experience writing computer programs.
Bryman
LostCousins Megastar
Reluctantly, I think I must agree with Alexander, unless you can find an existing utility which will create Gedcom formatted output from data entered in identified columns. There could still be quite a lot of 'data cleaning' required before any conversion might be attempted but if such a tool can be found then it might save a great deal of time and effort. Even if such a conversion only handles certain fields for individuals, it could give you a head start for an initial load into a family tree program.
As a Lost Cousins Star, you probably have a substantial amount of data to be entered and I would suggest that any help with the initial load is worth taking, as long as setup does not exceed the cost of going back to entering the data manually from scratch. I shudder at the thought.
Almost certainly, creation of family relationships will be easiest/best if performed manually once the individuals have been entered with all of their data. Best of luck if you do take this approach and please get back to this forum if you would like further suggestions in specific areas.
I use GenoPro with a separate tree for each surname to keep pictorial depiction of families more manageable. If I were faced with this situation, I think I would at least try to convert data for the members of one family and see how easy/complete the action would be. However, that might indicate that Alexander's suggestion for complete re-entry of data is the best approach. Good luck.
As a Lost Cousins Star, you probably have a substantial amount of data to be entered and I would suggest that any help with the initial load is worth taking, as long as setup does not exceed the cost of going back to entering the data manually from scratch. I shudder at the thought.
Almost certainly, creation of family relationships will be easiest/best if performed manually once the individuals have been entered with all of their data. Best of luck if you do take this approach and please get back to this forum if you would like further suggestions in specific areas.
I use GenoPro with a separate tree for each surname to keep pictorial depiction of families more manageable. If I were faced with this situation, I think I would at least try to convert data for the members of one family and see how easy/complete the action would be. However, that might indicate that Alexander's suggestion for complete re-entry of data is the best approach. Good luck.
AndyMick
LostCousins Star
Having converted a spreadsheet to GEDCOM with my own custom programme, the biggest issue I see with your spreadsheet is the lack of consistency in the relationship specification - column D. GEDCOM needs to know the family (as Alexander detailed at the end of his most recent post), so you need specify (in a way that a programme can understand) both the father and mother of a person (assuming you know them - they can be blank of course, although if you know folks are siblings it makes sense to identify a parent to link them together). As I did my programming in VB6, you should be able to do a similar thing in VBA (the Excel macro language) and we could use my VB6 code as a base on which to improve. As others have said, the ambiguities will need cleaning up too - a programme can't look at something and make an intuitive guess like a person can. You can also take it a small step at a time - for instance, you don't need to convert all the census data at the start if your immediate wish is to get the names and dates into another programme to produce a tree. Have you written any Excel macros? If not, then the easiest thing to do might be to do something simpler like converting your format to the format required by one of the available converter programmes, which could use a much simpler macro.
(As an aside I note that the Excel2GED converter also has problems with multiple wives/mothers - it's pleasing to see others have had the same issues I had!)
(As an aside I note that the Excel2GED converter also has problems with multiple wives/mothers - it's pleasing to see others have had the same issues I had!)
Many thanks to everyone who's been looking at my problem for me. I'm going to go through all your replies and will think things through now because I'm not a programmer though I have managed a few Excel macros/SQL queries when I worked. If I make progress I'll report back.
Janet, you might find these YouTube videos help. I haven't viewed them myself, but the creator of them saw this discussion and asked me to pass on the link.
If you do find them useful - or would have done back in March - please let us know.
If you do find them useful - or would have done back in March - please let us know.