Family Historian User Group

I have seen that sometimes people include things like titles as part of names, so one can distinguish which say "William" it is. (e.g. ", Esq.", ", Rev." ",Capt." ", King of England", etc.)

Obviously, those things could go in a Title field, or go at the beginning of the name. There are some disadvantages at the beginning of the name, and putting in a Title Field, means its not as easy to distinguish people when working in FH.

That said, when I have commas in the name field, and I export to work with the data in a "csv" in something like Excel, where I want to Parse the data to columns, the commas cause a problem.

I have been thinking of using the Search and Replace Plugin to swap commas with semicolons, but that does not seem conventional.
Is there another obvious fix I am missing?
thanks
Trent

Presumably, your name format is John /Smith/, Esq that typically displays as John SMITH, Esq
Would it cause you a problem to just remove the comma completely to produce John SMITH Esq

Moving to the beginning of the name as Esq John /Smith/ treats Esq as a forename and sorts near Estha.

Another option is to use a Unicode character that looks like a comma but is actually something else. There are plenty.
Then I suspect Excel won't use it as a column separator.

tparkhill wrote: ↑

28 Sep 2022 15:35

putting in a Title Field, means its not as easy to distinguish people when working in FH.

Have you tried customising the Property box and/or the records window to display the Title?

This post does highlight a common problem in selecting names (often via the select dialogue). The name is rarely enough - an informal qualifier is often useful - but ideally without "misusing fields".

You can:

1. filter by date (if known)
2. click on the expand "icon"
3. scroll horizontally to see other columns

But sometime you end up taking pot-luck and having to reverse out!

We also have the problems mentioned previously of distinguishing addresses: St Marys (The Cathedral, The Stadium, The Locality etc.)

We have [[Privacy Brackets]] on some fields (why not all?).
Why can't we have {{Annotation Brackets}} on all fields - which won't print in reports or display in diagrams (except by choice), and won't export (except by choice).
But {{Annotation brackets}} would show in selection dialogues

Then we could have:
William Jones {{butcher in Colne}}
William Jones {{My Grandfather}}
St Marys {{CoE Church, Allonby}}
St Marys {{Stadium, Southampton}}
St James {{Cathedral, Newcastle}}
etc.

An amazing number of fields support a local Note which does offer [[privacy]] brackets.
So if the selection dialogue displays those local Note fields where appropriate that may offer a solution now, whereas waiting for a feature like {{brackets}} may take a long time.
Fields that support a local Note include almost every type of record, Name, Fact, Place, Parents family, Spouse family, and Associated Person.
Also, several fields have a subsidiary Type Descriptor that is not well supported by FH but has much potential.

All interesting.
Customizing the Property box could help, but then not there in Focus Window and other key places.
And I worry about things like < or { etc.
I have tons of Place names where someone included these for some reason.
So I have to merge <England> with England, etc.

I agree it is a bit broader; its about "things we might do to make something look the way we want when printed" vs. other places where we might do database operations, Geocode, etc. . . . especially if we export data.

Admittedly, I may be pushing the edges on exporting. I have been using a lot of external software for things (e.g. plotting my American immigrant ancestors vs. Ancestry's DNA analysis, or nationality of immigrants vs. birthplace and historic maps - e.g. see what the original nationalities of my Palatine immigrant ancestors was (Walloon, Huguenot, German, etc.) images below)
Thank you all for your thoughts on this.
Trent

tatewise wrote: ↑

28 Sep 2022 16:15

An amazing number of fields support a local Note which does offer [[privacy]] brackets.
So if the selection dialogue displays those local Note fields where appropriate that may offer a solution now, whereas waiting for a feature like {{brackets}} may take a long time.
Fields that support a local Note include almost every type of record, Name, Fact, Place, Parents family, Spouse family, and Associated Person.
Also, several fields have a subsidiary Type Descriptor that is not well supported by FH but has much potential.

Trouble is you can end up configuring those columns in the selection dialogues to make them endlessly long (and then you find you want one more column)!

We have notes for genealogical information that belongs with a fact (or source, or place etc.) and which we want to publish.

Then there are [[privacy brackets]] (non GEDCOM?) for that sort of information but which you don't want to risk publishing.

Then there are your sort of working notes and annotations which probably don't mean anything to anyone but you and which may only have relevance whilst sorting out a particular bit of genealogy. The OP's issue with being able to tell his Williams apart (or others with telling places apart etc) come to mind.

For the later you don't really want to use a field (type, descriptor etc. - that may already be being used for something else) which has to then be configured into the various displays and dialogues - and which may only be accessible via the All tab or by customising Property Box tabs. I think you want something that you can actually put in the name field or in the place field etc.

Imagine trying to select a place - Frankfurt - its "nation" depends on the date of the event. If we could annotate it so the place lists offers:

Free Imperial City of Frankfurt, Holy Roman Empire {{to 1806}}
Principality of Frankfurt {{1806-1810}}
Grand Duchy of Frankfurt {{1810-1813}}
Free City of Frankfurt, , The German Confederation {{1815-1866}}
City of Frankfurt, Province of Hesse-Nassau, Prussia {{1866- etc.
(EOE etc.!)

OK that is not available now and we have to kludge our way around the problems; but if we don't air possible ways to improve the situation, we will never ask CP for any improvements.

Do we lift these exchanges to wish list requests - or are you saying why bother?

I have only just picked up this thread - I have not read it all but there seems to be a lot of discussion about a non-existent problem here.

FH, like any good database exports csv files with any field with commas in parentheses as below, any software that can read csv files will recognise these

"Agbrigg, Yorkshire, England",,"53.6664, -1.47647",,53.6664,-1.47647
"Aldershot, Hampshire, England",,"51.2494, -0.763015",,51.2494,-0.763015
"Alford, Lincolnshire, England",,"53.2591, 0.177735",,53.2591,0.177735

Sorry, would have been easier with a picture. (see below).

It started with my problem, That sometimes people use commas in the Name field (e.g. James I, King of England). Unless there is a setting I am missing, when I export from a Query to a csv, I end up with extra commas, and when importing a csv to something like Excel (to manipulate the data), the extra commas move the data to other columns.

In Libre Office when I do a CSV import I get the following options (and result)
Note the text delimiter setting (highlighted).

I pasted the text that Little.Auk included in their post into a csv file and then opened that in Excel using the Data->From Text/CSV option and it loaded correctly. It also does that if I just open the csv file directly into Excel. So if you're importing a CSV into Excel and the commas in the place name are being interpreted as column separators then it suggests the fields in the CSV are not surrounded by inverted commas.

I didn't say, but the snippet I posted was from an FH Query exported as a CSV file.

FH automatically puts quote marks around strings that contain commas when exporting CSV files. If you have problems opening these in Excel etc. you need to modify the import program settings as per the LibreOffice post.

Opening a CSV file from within Excel prompts for these as part of the file opening process.

Another option in FH is the Save as Text option which allows you to specify the Tab character as the delimiter, instead of a comma.

This works in spreadsheets, but is especially useful for word processors, as you can set the spacing between tabs to give a table style layout.

tparkhill wrote: ↑

28 Sep 2022 21:25

Sorry, would have been easier with a picture. (see below).

It started with my problem, That sometimes people use commas in the Name field (e.g. James I, King of England). Unless there is a setting I am missing, when I export from a Query to a csv, I end up with extra commas, and when importing a csv to something like Excel (to manipulate the data), the extra commas move the data to other columns. ...

I tried your example, and exported it two ways:-
Save Result Set As ... / Comma Separated CSV File
Save Result Set As ... / Comma Separated CSV File (ANSI)

Both were saved as .CSV files, with the title / headings in row 1, and I have .CSV set to open in Excel automatically. The one exported as a Comma Separated CSV File (ANSI) automatically opened on double click with James I, King of England in the correct Excel columns. The other didn't parse autmatically into columns, despite the names being surrounded by quotes. Further, I can't find the right combination when I try to do Text to Columns in Excel on that one. A visual check in Notepad++ shows no difference but the ANSI version is half the size on the disk suggesting that the encoding is different.

AdrianBruce wrote: ↑

29 Sep 2022 15:45

suggesting that the encoding is different.

I'd guess UTF8...

And I'd guessed wrong -- it's UTF16 as far as I can tell.

edited to add (from a Microsoft support thread ending in 2017):

Excel does support UTF16 CSV Files. But only if the TABULATOR character is used as a delimiter,

then Excel separates the columns correctly when opening the .csv File.

A workaround if you need Unicode is to open the file first in Notepad, and then save it as a UTF8 file. The Excel problem has existed in Excel since at least 2009, so isn't going to get fixed anytime soon by Microsoft, but a wish list request for UTF8 format in this situation, or a report to CP might be worthwhile.

(LibreOffice Calc is reputed to work fine).

Looks like I have more experimenting to do , a lot of thoughts here. Thanks!

Interesting Notepad++ was mentioned . . . I just downloaded that yesterday.
My current goal is exporting from Ancestor Query with added Birth Year and Surname, so I can separate the birth locations for each family line. That is current reason to get the correct Lat/long,, so I can create a KML file with a path to import into Google Earth Pro. Basically, trying to get it to plot the migration paths for each family line, with dates at nodes. Found the GE Pro KML code, just need to add in the strings of coordinates for each path. Was thinking someone would have created an app to do this from Gedcom, but looks like not. Although, at the risk of again exposing my ignorance, using the Ancestor Query has made it clear that apparently I don't know what an "ancestor" is. I was thinking it would just give me my direct ancestor family lines (if I sorted by Surname and dates), but it seems to give wife's ancestors also and seems to have some aunts/uncles/cousins in file. So much to learn.

Another workaround was posted in June 2020 in Exporting query results to MS Access that does not need any processing of the UTF-16 CSV file.
BTW: The workaround is for MS Excel not MS Access and you don't need to remember the save with tab method.

Does anyone know whether this problem has been reported to CP?

Im not sure we can expext CP to fix an MS problem

But i already suggested reporting to CP as i suspect a utf8 option for the csv was an oversight when they switched from utf16 as default to utf8 as default.

PS Acess is a hammer to crack a nut for anyone who doesnt use it regularly. How many workarounds do we need?

ColeValleyGirl wrote: ↑

29 Sep 2022 16:10

A workaround if you need Unicode is to open the file first in Notepad, and then save it as a UTF8 file. The Excel problem has existed in Excel since at least 2009, so isn't going to get fixed anytime soon by Microsoft, but a wish list request for UTF8 format in this situation, or a report to CP might be worthwhile.

(LibreOffice Calc is reputed to work fine).

It is not necessary to edit the CSV file if you need Unicode encoding - As per my post above use the Save as Text file option and select Tab Character as the separator and FH will give you a tab separated file.

When you enter the file name give it the suffix .csv. The file should now import into Excel - which recognises both comma delimited and tab delimited files as csv.

Missed that - sorry!

Returning to the original reason for this post, it is a scenario that many of us must have experienced. For example, in 5 generations of my own family tree the name William Rollin appears 12 times!

Could I suggest that the Custom ID field could be a possible solution. If it is not being used for custom ID numbers, then it can easily be repurposed.

It is ideal for quick reminders (a sort of electronic Post-It note) as it can be set to display in the Records Window and the Property Box and holds 60 plus characters.

in the custom ID field you can enter notes to help identify people such as "Son of Thomas", or "Husband of Susannah". Or you could put in research reminders like "Find on 1881 Census" or Purchase Birth Certificate"

I have attached an example.

Peter, I am fascinated by how you display Custom Id in the Focus Window, i.e. the pane on the left (not the Property Box on the right which is quite separate and not necessarily displayed).
Perhaps that was a typo and you mean Records Window as in your screenshot.

OOPs!

I have edited it.