Access AncestryDNAHelper data with SQLite

#1 · 2019-03-07, 11:44 am

Quote from Ron Hager on 2019-03-07, 11:44 am
I came upon this tip (posted below) from Diana Todd on another site. I also am a SQLiteStudio user so I followed her directions and they work. This is one more SQLite tool we can use.
"I use AncestryDNAHelper to grab all the info on my matches. For those that don’t know, it is an extension for the Chrome browser; found at the Google https://chrome.google.com/webstore/detail/ancestrydna-helper/hjflmfphflaeehhpdiggobllgffelfee?hl=en-US
I use a free program called SQLiteStudio available at https://sqlitestudio.pl/index.rvt to open the database stored in Google’s folder in the AppData\Local\Google\Chrome\User Data\Default\databases\ in a subfolder named with a lot of letters and numbers which varies on every computer. It’s very fast, it makes you wonder why the extension can’t export the ancestors table without overheating your CPU and failing to create the ancestors.csv file. Don’t try it, use SQLiteStudio and it’ll have it done in a minute or less unless you have more than 3 millions lines in that csv file; then maybe a few seconds longer. I am not kidding, it is fast.
These are the column titles that can be exported from the database’s matches table. That’s all matches you scanned in that database, not just one person. The extension itself can save single person matches csv files easily.
“testid”,”matchid”,”name”,”admin”,”people”,”range”,”confidence”,”lastlogin”,”starred”,”viewed”,”private”,”hint”,”archived”,”note”,”imageurl”,”profileurl”,”treeurl”,”scanned”,”membersince”,”ethnicregions”,”ethnictraceregions”,”centimorgans”,”segments”,”matchurl”
Just about everything you need to know except who is in their linked or unlinked trees and your MRCA. (don’t I wish!)
I can’t open my nearly 3 million line ancestors.csv files in anything except a simple text editor or Beyond Compare 4 which I use to compare the previous ancestors.csv file with the current to get a “new ancestors” csv file. Even notepad in Windows 8.1 can handle the monster file though. Not OpenOffice; can’t handle that many lines and chokes on the truncated version it creates opening the csv even though it has far fewer lines.
Here’s the column titles in the ancestors table:
testid,matchid,surname,fullname,born,died,metaphone"

I came upon this tip (posted below) from Diana Todd on another site. I also am a SQLiteStudio user so I followed her directions and they work. This is one more SQLite tool we can use.

"I use AncestryDNAHelper to grab all the info on my matches. For those that don’t know, it is an extension for the Chrome browser; found at the Google https://chrome.google.com/webstore/detail/ancestrydna-helper/hjflmfphflaeehhpdiggobllgffelfee?hl=en-US

I use a free program called SQLiteStudio available at https://sqlitestudio.pl/index.rvt to open the database stored in Google’s folder in the AppData\Local\Google\Chrome\User Data\Default\databases\ in a subfolder named with a lot of letters and numbers which varies on every computer. It’s very fast, it makes you wonder why the extension can’t export the ancestors table without overheating your CPU and failing to create the ancestors.csv file. Don’t try it, use SQLiteStudio and it’ll have it done in a minute or less unless you have more than 3 millions lines in that csv file; then maybe a few seconds longer. I am not kidding, it is fast.

These are the column titles that can be exported from the database’s matches table. That’s all matches you scanned in that database, not just one person. The extension itself can save single person matches csv files easily.

“testid”,”matchid”,”name”,”admin”,”people”,”range”,”confidence”,”lastlogin”,”starred”,”viewed”,”private”,”hint”,”archived”,”note”,”imageurl”,”profileurl”,”treeurl”,”scanned”,”membersince”,”ethnicregions”,”ethnictraceregions”,”centimorgans”,”segments”,”matchurl”

Just about everything you need to know except who is in their linked or unlinked trees and your MRCA. (don’t I wish!)

I can’t open my nearly 3 million line ancestors.csv files in anything except a simple text editor or Beyond Compare 4 which I use to compare the previous ancestors.csv file with the current to get a “new ancestors” csv file. Even notepad in Windows 8.1 can handle the monster file though. Not OpenOffice; can’t handle that many lines and chokes on the truncated version it creates opening the csv even though it has far fewer lines.

Here’s the column titles in the ancestors table:
testid,matchid,surname,fullname,born,died,metaphone"

Tom Holden and DaiyuHurst have reacted to this post.

#2 · 2019-07-24, 7:50 pm

Beyond Compare is a most excellent tool.