This method was successful. Your outcomes may vary.
UPDATE (09 NOV 2015): 23andme is retiring Countries of Ancestry (the tool formerly known as Ancestry Finder) as of November 11, 2015. I’m leaving this blog post out here for posterity, mainly due to vanity and the amount of time I put into creating my Excel sheet. 😉
Update: I have made some changes to the Import tool – you can download the latest version at the original link listed below. See more info at the bottom of the post.
After creating and writing up my last post on the process to automate the download of 23andme Ancestry Finder data files for your matches, I could not resist working on a way to combine all of those data files into one.
I figured Microsoft Excel was the best common denominator to use for this process – there are any number of tools I could use to combine the files and work with them at the command line, but that would limit the number of people who could use it.
So, here’s the quick scoop: My Excel spreadsheet is designed for use with the .CSV data files you have already downloaded from 23andme’s Ancestry Finder. (Again, see my Ancestry Finder Download Tool post for more info on that process.)
- It will import ALL .CSV data files in a selected folder
- It will add a column with the name of the 23andme user the data file came from. (It uses the data file name for that, so don’t rename them after you download them!)
Then it will make you a sandwich. A nice juicy data sandwich.
Each data row will have the source person AND matching person’s name in it – otherwise you would have a list of a few hundred thousand matches without knowing whom they belonged to!
UPDATE (09 NOV 2015): 23andme is retiring Countries of Ancestry, (the tool formerly known as Ancestry Finder) as of November 11, 2015. I’m leaving this blog post out here for posterity, mainly due to vanity and the amount of time I put into creating my Excel sheet. 😉
But, as mentioned in the previous update: you should be using the tools at DNAGedcom.com. Good luck to you!
UPDATE (11 July 2013): It has been more than a year since I created this tool and process, and the 23andme site has gone through some extensive changes. I have not had time to test if the process in this post nor the spreadsheet is still working properly with any possible data format changes.
Feel free to give it all a shot, but I recommend that you may first want to take a look at the DNAGedcom.com site, which has automated things in a much more user-friendly way!
If you are a 23andme customer, you may have noticed that they updated their Ancestry Finder (AF) tool to make it possible to see the matches of the people with whom you share genomes. You can also download this data in comma-separated-variable (.csv) format.
That. is. great. stuff.
I’m sure that people will soon come up with interesting new ways to triangulate their genetic matches and learn more about their ancestry. But, there is one problem: If you are sharing your results with a few hundred people and want to get all of their AF data files, it takes a drop-down selection, wait a few seconds for AF to reload, then a button click and a Save File As click FOR EACH PERSON IN YOUR LIST.
Holy carpal tunnel.
I suppose 23andme might make it possible within their system to get them all at once which will make this all moot. But, I thought there must be a better way to get all of these data files and I found a slightly clunky, yet completely workable method.
There may be more elegant ways to do this, but for now I created an Excel spreadsheet that will help you get you all the data with only a bit copying and pasting – along with some help from a Firefox add-on.
Genetic testing company 23andme uses “Research Snippet” questions to compare people’s answers to their DNA results.
Typically they are simple questions and often have obvious usefulness – such as “Have you ever undergone LASIK eye surgery?” I would think a comparison of certain locations of the response group individuals’ DNA could one day predict a the likelihood of a certain affliction.
But, I am quite amused by the 2nd question in this research snippet screen capture:
“Does the sound of other people chewing fill you with rage?”
YES. YES IT DOES. (Actually, not really.)
But I am left thinking about those who might answer “I’m not sure.”
“Hmm, well, it is not RAGE exactly, but mandibular gyrations DO cause me to be overcome with a certain combination of ENNUI and MUDEROUS INTENTION.”
For more information on 23andme’s research see their blog – this post in particular:
23andMe Research Team Presents Findings at International Human Genetics Meeting
As a consumer and genealogist, I submitted the following comment regarding the possible strict regulation of Direct to Consumer (DTC) genetic testing at Regulations.gov.
For more information on the issues surrounding this testing, see the post “A Sad Day for Personal Genomics” at Genomes Unzipped and Daniel MacArthur’s informative blog, including this post in particular: “Last chance: let the FDA know why you want direct access to your own genome”
My own comment submission is below – today is the last day to submit! Read More | Comments