* Exporting To Excel To Edit, Then Importing Back
-
- Newbie
- Posts: 1
- Joined: 29 Jun 2020 18:04
- Family Historian: V6
Exporting To Excel To Edit, Then Importing Back
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?
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?
- 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
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.
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.
- 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
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.
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
- 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
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.
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
- 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
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
- 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
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).
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
- 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
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
Re: Exporting To Excel To Edit, Then Importing Back
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
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
- 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
Run the All Individuals query and then via the Save Results to File icon
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.Helen Wright
ColeValleyGirl's family history
ColeValleyGirl's family history
- 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
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.
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
Re: Exporting To Excel To Edit, Then Importing Back
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!
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!
- 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
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.
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
- 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
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.
Helen Wright
ColeValleyGirl's family history
ColeValleyGirl's family history
- 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
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.
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
Re: Exporting To Excel To Edit, Then Importing Back
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?
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 (407.57 KiB) Viewed 2318 times
- 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
@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.
I have a modified query (attached) which may be more useful for you BUT it does have a bit more information than you require.
- 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
From An Overview of Queries (which you might find helpful in the future)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.
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:
- Use numerical columns for Day Month and Year in your queries if you intend to export them to Excel.
- If you’re very comfortable with Excel, explore the techniques in How to Work with Dates Before 1900 in Excel
Helen Wright
ColeValleyGirl's family history
ColeValleyGirl's family history
- 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
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.
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
- 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
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.
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.
Helen Wright
ColeValleyGirl's family history
ColeValleyGirl's family history
- 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
Sorry, missed the bit about Ancestry GEDCOM into FH in his first posting
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry