Our Folks 2.0 - Goggans Genealogy

Search  

DNA Info and Matching Segments Help

Preparing the Match Segments CSV File

After the match segment data is downloaded from 23andMe, there are a number of steps required to get it ready for upload to the web tool. In general, the downloaded file can be modified per the steps below, or a pre-formatted file (from me) can be used and you can paste the data from the download into it.

Here are the steps to modify the download from 23andMe (with Column ID's):

First, remove all rows that don't have a chromosome number, Start, End - these are matches that have not shared their ancestry with you (they will be skipped otherwise).

    1. Insert new column "rowid" - an ID field for the database that will be filled in automatically - enter zero in every row in the sheet
    2. Insert new column "user" - your username for the website/tool (assigned and provided by me - this allows you to see your data) - enter in every row
    3. Insert new column "treeperson" - this is your full name (or what you want displayed in the tool) - enter in every row
    4. Insert new column "relfamid" - the relationship of the match/segment (if known) in the format P, PM, PP, M, MP, MM, etc. - fill in U where not known
    5. Insert new column "treeid" and leave the rows blank - this is for future linking to a person in a family tree on another website
    6. Displayname - make sure this column doesn't have commas (like Johnson, Jr.), any non-alphanumeric characters - 23andMe allows users to insert them, but they won't upload to this database
    7. Surname - leave as-is (make sure there are no commas or special characters)
    8. Chromosome Number - leave as is
    9. Chromosome Start Point - leave as is (make sure not formatted with commas)
    10. Chromosome End Point - leave as is (make sure not formatted with commas)
    11. Genetic Distance - cM length for the match segment - round all of these numbers to no more than 2 decimals (e.g. 45.22)
    12. # SNPs - number of SNiPs in the matching segment - leave as is
    13. Full IBD - TRUE/FALSE (if parents were also tested) - leave as is or blank
    14. Link to Profile Page - a URL that takes you to the matches' 23andMe profile page (if public and/or you are connected) - leave as is
    15. Sex - leave as is or blank - not used
    16. Birth Year - leave as is or blank - not used
    17. Set Relationship - 23andMe's predicted relationship to the match person, or what you have entered as the known relationship - you must remove the commas from all values in this column (e.g. remove the comma from "2nd Cousin, Twice Removed")
    18. Predicted Relationship - same as above but not overwritten by user's input - must remove all commas
    19. Relative Range - 23andMe's estimated range for the more distant relationships to the match person (e.g. 2nd to 4th Cousin) - leave as is
    20. Percent DNA Shared - the total amount of shared DNA with the match person - these values must be converted from a percentage to a number with 4 decimals (e.g. 7.52% becomes 0.0752)
    21. # Segments Shared - the total number of matching segments for this match person - leave as is
    22. Maternal Side - not used (TRUE/FALSE if parent(s) tested) - leave as is
    23. Paternal Side - not used (TRUE/FALSE if parent(s) tested) - leave as is
    24. Maternal Haplogroup - not used - leave as is
    25. Paternal Haplogroup - not used - leave as is

All remaining columns must be deleted - Family Surnames, Family Locations, etc. etc. through Family Tree URL

This is a portion of what a properly setup sheet would look like with headings shortened (blue headings are columns inserted, yellow ones must be formatted carefully as described above):



I will perform a check on files that you provide (either in Excel or CSV format), and then attempt upload. If there are errors, I can rectify the simple ones or get back to you if something major is going on.

Go to the Help Table of Contents or Next Page