Making a CSV file

Many users attempting to use the chromosome mapping tool are having trouble making a CSV file from their spreadsheet list of DNA segments, so I have put a template CSV file in my downloads area.

Plus here is a good online tutorial for making a CSV file:

http://www.computerhope.com/issues/ch001356.htm

Mac users have trouble making a windows compatible CSV file. One solution is to use google docs. Another is to use the the program “Numbers.” OpenOffice does not do the line endings compatibly with a PC. A comment from a very knowledgable user indicates that you must save the CSV in Excel 2011 as “Windows Comma Separated (.csv)” and yet another user said that in “LibreOffice spreadsheet, remember to save as text (.csv)” which is way down the list.

 

Thanks to my great tester Lisa, here are some step by step instructions for creating a CSV from a downloaded Excel file from 23andme:

  • 23andme: Download Ancestry Finder (AF)
  • Open in Excel (I use 2007, other versions might vary slightly), I also have Excel 2003 if these do not translate well)
  • Remove or Hide columns “B” through “I” by holding down left mouse pointer on “B” and pull through to column “I”, then click the delete key on your keypad
    OR
    You can highlight the same way, then right mouse click to reveal a menu, select “HIDE” and they will no longer be shown
  • Go to the top of your AF and put mouse pointer on column A1
  • From the HOME tab select “INSERT”, it should automatically insert a new row, if not click the drop down triangle and manually select to add a row, alternately you could select all of your data by pulling the mouse over it and moving it down one row. Also click the drop down triangle and manually select to add a column in order to add the column for “side.”
  • These instructions will make it easy to just paste the column headings even though the program will ignore other columns, paste these row headings. If you have a file that already has column headings then you do not need to insert a row but you do need to replace the column headings with these:
side MRCA Chr Start point End point cMs
  • SAVE AS: other format, a drop down box will appear, follow it until you see CSV (COMMA DELIMINATED), click SAVE. Be sure to recall where you are saving it as you will need to find it to run the program.
5 Comments

5 thoughts on “Making a CSV file

Click here to add your thoughts at the end of the comments
  1. Is it possible that 23andMe has added a new column that is not part of these instructions? After I hide columns B through I, I am left with A (MatchName), J (chromasome), K (SegmentStartInMegaBasePairs), L (SegmentEndInMegaBasePairs),
    M (SegmentLengthInMegaBasePairs), N (SegmentLengthInCentiMorgans)
    This doesn’t seem to match up with the new column headings:
    side (MatchName), MRCA (chromasome), Chr (SegmentStartInMegaBasePairs), Start point (SegmentEndInMegaBasePairs), End point (SegmentLengthInMegaBasePairs), cMs (SegmentLengthInCentiMorgans)

  2. I will go check, the idea is that “side” is a column that you insert yourself at the beginning, I guess that is not clear. Also most people insert the column MRCA at the end and put the common ancestor there. Lisa was using the match name since she wanted the chromosome browser picture rather the segment mapper one which does not need the side column. In fact the segment mapper can use csv files as is from most ways you download (just sort by largest segment).

  3. Kitty, Please excuse my lack of knowledge in the Question I ask!
    I am a 70 year old adopted male, I have been involved in DNA for
    many years. The first person who I matched was a Genealogist
    and when she found out that I was adopted, and matched as a third cousin she sent me her whole database which was started by her grandfather. This is all Irish! I continue to use her database and DNA to try to figure out who is my primary relative. I know my mother side, I have 8 half brothers and sisters which I found. Birth records are easy! But, my fathers side is more difficult. My question is, is there anyway that you can think of to combine graphically the interrelations I find in my genealogy
    with with Dna cousins. There are many tests that come back as 2nd to 3rd and 3rd to forth that I can find in my genealogy but I can’t tie them together. This is probably making no sense, but I guess that what I want is to use graphing and computer power to combine DNA result and genealogy in an effort to pull up the best possible person for testing. Whew! I get results from Dna but because I have so much data I get the feeling I am missing something that is obvious.

    I am sure most people would love to have this problem, I Just wish I was
    a lot more computer literate when it comes to this area of knowledge. Thank you for any reply that you give. Most sincerely Richard

    • Richard
      The graphic tools I have here (look under tools) are for displaying just the DNA data
      Have you looked at the methodology over at http://DNAadoption.com ? Or the tools at their partner DNAgedcom ?
      As I understand it you want to be building the trees of these close cousins and looking for their common ancestors. Since you have your mother’s tree what you are trying to do is figure out common ancestors on your father’s side.
      One word of warning, close cousins may not be as close as indicated if they are from an endogamous population such as Mennonites, Ashkenazi Jews, Polynesians, etc. so look at your ancestry composition also and compare it to your cousins on your mother’s side

  4. One more point, there is a tool Gworks, at DNAgedcom.com which compares Genealogy files (GEDcoms) which is very useful for comparing these unknown cousins to each other.

    Plus there is a tool under downloading family tree DNA that will download the GEDcoms from that DNA testing site

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.