FTAnalyzer - New Export to Excel function

[Tim]

FTAnalyzer has 3 new export functions. These can be found on the top menu bar under Export.

The 3 Exports are

• Individuals to Excel
• Families to Excel
• Facts to Excel

In this discussion, I'd like us to explain how we used one of the exports, to help fix errors or omissions, or to help us identify issues.

 

[Tim]

I used the Facts Export, by filtering on the Fact Type, I was able to see entries For Lost Cousins where I hadn't entered the date and also missing date entries for Occupations and For Children Status.

 

[PK-KTK]

I used Facts Export, and found I had a 12th column which was unlabeld. Filtered on it and found that 7 records have facts that had been recorded with a comma (,) in the comment/description field, thus pushing the rest of the comment/description into the Location Field, and onwards pushing other data into the next columns.
Although this didn't appear to be affecting the overall functionality or display of the data, I can now fix these entries thus making filtering other columns more effective.
I can also filter dates to easily see which ones are not formatted correctly in a format that works for me (I know there is the error report but this works better for me)

 

[Alexander Bisset]

Rather odd for a spurious comma to be in there as all the csv data should be in quotes. Can you look at the file in a proper editor NOT Excel (Excel mangles csv files in an attempt to be helpful). This should let you see if anything isn't getting quoted.

 

[PK-KTK]

the spurious comma was my own fault Alexander, I had entered commas in the description field when entering a long line of text, rather than keeping the description short and putting the longer detailed text into the Notes area (one reason why I've switched programs recently, I didn't have the extra area for longer notes before, now I do) - I was also using quotes within the description field to help with things like ship names, which I am also fixing.

As for doing what you are suggesting though, would Crimson Editor or Dreamweaver be considered and proper editor? and if so, what should I be looking for to know what isn't being quoted?

 

[Alexander Bisset]

Not sure about either, my comment was more the pet hate of using Excel for csv useful though it undoubtedly is at times. I've had many an occasion where people have loaded a csv I've created particularly one with lookup codes in it eg: product codes, and loaded into Excel then save it again completely destroying things like leading zeros, creating spurious dates by misinterpreting codes like 10-9999, treating codes like 23e5 as scientific notation numbers etc.

Excel is great but it can completely mess up your csv file if you don't treat all columns as the right format. Which is why the usual double click to open causes problems as it treats all columns as type general and not as text (which you want for codes).

 

[PK-KTK]

Excel is great but it can completely mess up your csv file if you don't treat all columns as the right format. Which is why the usual double click to open causes problems as it treats all columns as type general and not as text (which you want for codes).

And text format is no good for looking at the dates
I know my way around excel pretty well though so I usually format the columns appropriately when I know what they need to be.

What editor would you suggest I try? I've seen other posts where people have posted info that looks like the raw data, what program would have been used for that?

 

[Alexander Bisset]

Sorry I wasn't that clear. I'd recommend using Excel, however if you want to check things like where the quotes and commas are ie: to look at the raw data then any text editor will do even notepad. It's not much good for working out columns but a raw text editor is great for spotting , " and ' bugs.

However when I know the text has leading zero product codes, fields that will get mangled then I use the text import feature of Excel rather than a double click.

 

[Tim]

I use the Export Facts to Excel quite often and then create a pivot table to produce this type of report.


I find it useful as I can see what events (births, marriages, censuses etc.) have been recorded against each person.
I can explain how to create a pivot table if you are unfamiliar with creating one.

 

[PK-KTK]

I'm ok with the concept of pivot tables thanks Tim, I use them often enough for certain types of data searches

 

[Tim]

I'm ok with the concept of pivot tables thanks Tim, I use them often enough for certain types of data searches

That's good to know.

 

[emjay]

I wish I knew what pivot tables were. We used to have a table with drop leaves which stayed up when it was swivelled

 

[Tim]

I wish I knew what pivot tables were. We used to have a table with drop leaves which stayed up when it was swivelled

Now that sounds like a useful table

A pivot table takes your data and displays it in a different manner.

So in my example from the Facts export from FTA, every fact for a person are on unique lines, e.g they could have a RESIdense fact for 1841, for 1851, for 1861, a MARRiage fact etc., 4 lines in total.

The Pivot Table has allowed me to make a new table from that data and now I have one line per person and in each column has the count of the Facts. So in this example, in the RESI column it would say 3 and in the Marr it would say 1. So I can now see that I haven't entered a BIRTh fact and so then I can go and find one.