* Exporting To Excel To Edit, Then Importing Back

Importing from another genealogy program? This is the place to ask. Questions about Exporting should go in the Exporting sub-forum of the General Usage forum.
Post Reply
avatar
Faisal2110
Newbie
Posts: 1
Joined: 29 Jun 2020 18:04
Family Historian: V6

Exporting To Excel To Edit, Then Importing Back

Post by Faisal2110 »

Good evening.

Apologies but I am new to the program. I have find it quite easy to use although I am stil finding some issues but I'm getting there!

I have quite a large tree that spans a few countries. A relative has kindly stated that, if I can export the data for excel, he will kindly edit the dates and make changes to spellings of names etc and return the file to me. I have been trying to read up on this and find something about a query tool etc but not fully sure if this can be done. I'm assuming I somehow export to .csv, he edits and then import. Is it as simple as that? Can someone please advise how I can do this?
User avatar
Valkrider
Megastar
Posts: 1563
Joined: 04 Jun 2012 19:03
Family Historian: V7
Location: Lincolnshire
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by Valkrider »

That is possible but would not be a recommended way of working. On export and on reimport you may lose some data you may lose media links etc.

If your relative can do the editing within 30 days he/she could use the free 30 day trial of FH to do the editing. Alternatively there are free genealogy programs that they could use such as Gramps and many others. However, using a different program to FH will not be an easy import process.

Decide on exactly how you want to do it and let us know and then specific advice can be given.
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Exporting To Excel To Edit, Then Importing Back

Post by Mark1834 »

Another potential trap when moving data between applications is around character sets. This is particularly relevant when using characters that are specific to individual languages. Family Historian uses UTF-16 by default, which is very good at handling international characters, but many office-based programs such as Excel will have much simpler defaults, such as ASCII or ANSI. This will cause data loss and corruption, undoing the editing process and possibly even corrupting your original data.

As Valkrider points out, the most appropriate method will depend on whether your relative will be editing a few records or hundreds. In general, editing a copy of the data (either electronically or longhand) is safer, but more long-winded.
Mark Draper
User avatar
tatewise
Megastar
Posts: 28333
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by tatewise »

Mark, please explain your assertion that MS Word and Excel do not support Unicode (UTF-8 & UTF-16) characters.
In my experience, all modern office products fully support Unicode and have done so for many years.
I believe that FH was actually one of the later products to adopt Unicode.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Exporting To Excel To Edit, Then Importing Back

Post by Mark1834 »

I didn’t say they don’t support it. I said they may not by default. That is based on my early experience with importing a GEDCOM file into Excel and processing it with VBA. All the £ signs disappeared from my probate entries! I didn’t pursue exactly where the incompatibility was (the text import or the VBA processing) as I found a much better way to achieve the same thing via Python. With the right settings it’s probably fine - just don’t bet your database on everything being set up correctly!
Mark Draper
User avatar
tatewise
Megastar
Posts: 28333
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by tatewise »

I suspect things have moved on, or as you say perhaps the problem was elsewhere.
As far as I am aware Unicode is now widely supported by default.
I have done nothing in Windows 10 with Office 365 to change any character mode settings.
However, there is a technique for maintaining Unicode characters in CSV files migrated from FH to Excel as explained in Exporting query results to MS Access (17886).
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Exporting To Excel To Edit, Then Importing Back

Post by Mark1834 »

And that is the key general point - it is not sufficient to just support a format or character set. When copying data between applications as text files, both applications have to be set to use the same character set (and date format, to avoid m/d/y - d/m/y - y/m/d corruptions), otherwise data loss and corruption can occur. Not all applications will have the same default settings.
Mark Draper
avatar
BrianMc
Newbie
Posts: 3
Joined: 27 Dec 2021 11:59
Family Historian: V7

Re: Exporting To Excel To Edit, Then Importing Back

Post by BrianMc »

A similar question. I have my wife's family tree on both Ancestry & MyHeritage and although I have FH7 on my device I have prevaricated for nearly over a year in taking the leap and actually start using the latter.

Now decided it is the time but I before I do this I just want to ensure everyone is accounted for on both websites before I download one (a further question anticipated) to FH. I have downloaded a .csv file to excel from MH but with Ancestry no such luck and all I have been able to obtain from that site is a GEDCOM file which I imported to Family Historian.

So far, so good and now I'm looking to ‘publish’ (is that correct?) a similar list of all individuals to the one I have from MH into excel so I can compare both side by side. That though a little challenging and I have tried everything to produce and export such a file. Can anyone help please?
Brian
User avatar
ColeValleyGirl
Megastar
Posts: 5464
Joined: 28 Dec 2005 22:02
Family Historian: V7
Location: Cirencester, Gloucestershire
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by ColeValleyGirl »

Run the All Individuals query and then via the Save Results to File icon
Screenshot 2022-11-05 095952.jpg
Screenshot 2022-11-05 095952.jpg (1.33 KiB) Viewed 2446 times
save as a csv. If the query results don't show the details you want, you may need to copy the query to a custom one and add the extra details -- ask if you need help with this.
User avatar
tatewise
Megastar
Posts: 28333
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by tatewise »

An alternative approach is to import the Ancestry tree into FH and clean up as advised in the FHUG Knowledge Base.
Similarly, import the MyHeritage tree into another FH Project and clean that up too.

Then use the File > Merge/Compare File... command to compare the data in the two Projects.

That is just a summary and the details need to be discussed in a separate thread because this thread focuses on Excel.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
avatar
BrianMc
Newbie
Posts: 3
Joined: 27 Dec 2021 11:59
Family Historian: V7

Re: Exporting To Excel To Edit, Then Importing Back

Post by BrianMc »

Thank you both.

This is where I am but cannot see or find the Save to command other then the Icon or File in the bar above. I have successfully produced a list of individuals and taken a screenshot to show you the result but I am unable to share this with you as even after multiple attempts to reduce the file size I am told its still too larger to upload.

Brian

PS I just noted your comment Mike re the thread being for Excel. My apologies as I was using/trying Excel to compare these files at the time of posting.

UPDATE: I have found it and downloaded the file - thank you!
User avatar
tatewise
Megastar
Posts: 28333
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by tatewise »

We are happy for you to continue with this thread regarding an Excel solution.

I was just saying that to pursue the File > Merge/Compare File... alternative it would be best to start a new thread.

BTW: I am not sure the All Individuals Query route gives you everything you need.
It only investigates Individual records in a limited way and not all the facts against each Individual nor all the other records such as Family, Note, Source, Media, etc.
Those are all included by the File > Merge/Compare File... command.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
ColeValleyGirl
Megastar
Posts: 5464
Joined: 28 Dec 2005 22:02
Family Historian: V7
Location: Cirencester, Gloucestershire
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by ColeValleyGirl »

tatewise wrote: 05 Nov 2022 18:56 BTW: I am not sure the All Individuals Query route gives you everything you need.
hence my suggestion that it might be necessary to create a custom query...

As MyHeritage exports Name, Gender, relationship to root, Birth date and place, and death date and place, the All Individuals query should be fine for doing a comparison if the death date and place is added.
User avatar
tatewise
Megastar
Posts: 28333
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by tatewise »

Brian, could you go into more detail about what you want to check?
You say "I just want to ensure everyone is accounted for on both websites".
What exactly does that mean?
Do you just want to check that an entry exists for each person but their details don't matter?
Do you want to check that the facts recorded against each person are the same or at least similar?
Do you want to check that the relationships (parents, spouses, children) between them are the same?

Does it really matter?
Presumably, you intend to transfer all the details from Ancestry to FH and all the details from MyHeritage to FH.
Having done that, you will have two Projects and would need to merge all those details into one composite Project.
That would ensure that everyone from both Ancestry and MyHeritage is accounted for and that the same people from both are merged into one record. That is what File > Merge/Compare File is designed to achieve.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
avatar
BrianMc
Newbie
Posts: 3
Joined: 27 Dec 2021 11:59
Family Historian: V7

Re: Exporting To Excel To Edit, Then Importing Back

Post by BrianMc »

The Individual route did work, and I saved & downloaded a .csv file, but unlike previous downloads of these types of files which I am familiar with, this one did not want to allow me separate the columns. I'm sure there is a way around that as I found with other projects.

The attached (now formatted) screenshot is from the csv I downloaded from MH, just a simple family/given name & a dob to help differentiate the multiple use of the given name throughout the generations. Hope this helps in how I am trying to, not so eloquently, explain myself. This was the starting point with MH and I wanted to do the same with Ancestry and place them side by side on a worksheet and look for those anomalies but they only provide downloading a GEDCOM file.

This was my initial intention as on MyHeritage I have 2219 People and on Ancestry 2228 plus a few unknowns. My wife and I have been meticulous in researching the available primary sources and only then adding these to those websites, but each has their own peculiarities, especially as these records are Eastern/Central European & Baltic States families and strange, to those who transcribe them, language.

So in answer to Mike’s question; do I just want to check that an entry exists for each person but their details don't matter? The answer is yes, the details and the referencing of sources would be attended to when FH7 is up and running and is the only place where the family research will reside with, as long as we have a subscription to these websites to hear if they have any ‘hints’ to share with us
.
Does it matter?
Yes, I believe it may do but we are discussing as a prelude to what is going to happen to the citations & sources plus notes we have placed into these websites as they are both obviously not compatible with each other. Another day, another longwinded question possibly for the answer? 😉
Attachments
Screenshot (1021).png
Screenshot (1021).png (407.57 KiB) Viewed 2318 times
User avatar
Valkrider
Megastar
Posts: 1563
Joined: 04 Jun 2012 19:03
Family Historian: V7
Location: Lincolnshire
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by Valkrider »

@Brian

I have a modified query (attached) which may be more useful for you BUT it does have a bit more information than you require.
All Individuals-CS-notree.fhq
(1.51 KiB) Downloaded 58 times
User avatar
ColeValleyGirl
Megastar
Posts: 5464
Joined: 28 Dec 2005 22:02
Family Historian: V7
Location: Cirencester, Gloucestershire
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by ColeValleyGirl »

BrianMc wrote: 06 Nov 2022 09:47 The Individual route did work, and I saved & downloaded a .csv file, but unlike previous downloads of these types of files which I am familiar with, this one did not want to allow me separate the columns. I'm sure there is a way around that as I found with other projects.
From An Overview of Queries (which you might find helpful in the future)
Saving Results to a File
Since ƒh V6 the Save Results to File menu offers two Save As Comma-Separated CSV File options for UNICODE UTF-8 and ANSI encoding, but some spreadsheet programs do not handle UNICODE UTF-8 well, so try both options, or even the Save As Text File options with either Tab character or Comma as the Column separators. See also How to Open UTF-8 in Excel 2013-2016 and How to Import CSV file that uses UTF-8 in Excel 2007.

You should note that Excel (as shipped) does not support Dates
When an Event happened, or an Attrribute was true.
earlier than 1 January 1900. There are two approaches to dealing with this:
User avatar
tatewise
Megastar
Posts: 28333
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by tatewise »

I believe Brian is not talking about FH Queries and exported CSV from FH.
So any advice regarding FH Queries and exporting CSV is not going to help him, except the general advice on importing various data formats to Excel.

Brian says he "downloaded a .csv file to excel from MH but with Ancestry no such luck". No mention of FH.
Also, the dates in his latest screenshot are not what FH produces.
That confirms my assertion that Brian is only exporting data directly from MH and Ancestry and not using FH at all yet.

So, I don't think Brian has transferred anything to FH yet.
However, I suggest that is his best policy. What have you got to lose?
i.e. Import the GEDCOM exported from MH and from Ancestry into FH to create two separate Projects.
Then produce a pair of CSV in compatible formats by using a Query or use the File > Merge/Compare File command.

I say again, the best solution in my opinion is to transfer both MH and Ancestry to FH via GEDCOM so you can use the same tools in FH to compare the details.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
ColeValleyGirl
Megastar
Posts: 5464
Joined: 28 Dec 2005 22:02
Family Historian: V7
Location: Cirencester, Gloucestershire
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by ColeValleyGirl »

Mike, please reread. Brian downloaded a Gedcom to FH from Ancestry and asked how to get a csv from that Gedcom.

I told him.

He has now saved a file from FH (via the All Individuals query), albeit encountering the known issue around Excel/UTF8/csv (which I have offered a solution for).

Brian also quite clearly said his screenshot is of the csv file produced by MH.

As he has not (per your suggestion) started a new topic about merging within FH, why do you keep reraising the subject here, when you've said yourself it would be best pursued in another topic. My own opinion is that it is a sledgehammer to crack a nut, and also introduces un-necessary complexity given the incompatibility of the two websites that Brian has pointed out.
User avatar
tatewise
Megastar
Posts: 28333
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Exporting To Excel To Edit, Then Importing Back

Post by tatewise »

Sorry, missed the bit about Ancestry GEDCOM into FH in his first posting :oops:
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
Post Reply