A week or two ago, one of my Facebook friends (thank you whoever you were!) recommended this post by Mary from Searching for Stories blog: Spreadsheet Magic – Importing Data from Ancestry.com. Do go and look at the post, and Mary’s many other fantastic posts. I thought I knew a bit about Excel but it seems not.
This week I thought I’d try Mary’s strategies on my Irish research to see how it worked. Following her steps this is what I did.
Sign into Ancestry.com with your own subscription or at a library near you.
My Step 1:
Bring up the search dialogue box and I used the Birth, Marriage and Death records option.
I entered no names and no dates. In the place of birth I simply put “Clare, Ireland”. As I’m mainly interested in those who emigrated to Australia I put “Australia” against the place of death. I don’t care which state so I wanted to pick up as much info as possible. I also ticked the exact box for both, as I didn’t want anything random to come up. Finally, I chose records from Australia as I’m expecting that is the most likely source of useful information -though perhaps not exclusively. I made sure I had the maximum entries per page (50).
Of course, as with all record searches you need to understand (1) what records might include both birth and death information and (2) what records are held within the overall database. As it happens, for me this means a heavy focus on New South Wales. This will be only one component of my research strategies.
Following Step 2, I copied the very long URL into tinyURL.com to give me a short link.See what a difference it makes – from 399 characters down to 26! Thanks TinyURL!
Step 3: I opened a blank excel spreadsheet, chose the Data tab and clicked “from web” on the left hand side. In here I pasted my Tiny URL, pressed “Go” to bring up the data, then ticked the box to the left of the data. (in this I’m following Mary’s instructions exactly). Then click “Import”. Voila!
A dialogue asks you where you want to paste it. I think it’s safest to put each batch into a separate page within the spreadsheet. You can do what you want with it later. With some whizzing and whirring, the data is imported to Excel.
I named that page “Clare no YOB page 1” (my first 50 details)
I repeated the process until I captured all 304 entries. This was pretty tedious I have to agree.
I deleted all the “padding” info at the top and bottom except the line that said items 1-50/51-100 etc.
Repeated this for all six of my page tabs.
As I wanted the names with other data in separate columns beside it, I dragged and dropped “spouse” “birth” and “death” into separate columns for each page, making sure each page was formatted the same.
I copied each page into one consolidated page so that entries 1-304 followed each other sequentially. I still have a problem with it because if I sort by name it will do so by first name so I will probably end up putting in another column with just surname to sort.
Similarly, the dates will sort by day rather than year and place by the first part of the entry. Is this enough for me? I will probably live with the dates, but will put in a column for state so I can see the dispersal patterns for their migration.
Was this helpful? Did it save time? Yes, I found it very helpful and I certainly got faster as I went along. The big benefit though is that it saves any transcription errors on your part (but not by the first indexer).
Mary has said the process works with Family Search but I haven’t tried that. I did try it with Trove and my “County Clare” + Obituary search. It worked okay but would require more fiddling with, and as there are MANY entries, there’d be lots of repeating of all the steps.
I tried it this morning with My Heritage but it kept giving me error messages which included that I needed to sign in, which I already was with my current subscription.
Similarly I tried FindMyPast but their search options don’t allow me to have the Clare + Australia option (or am I missing something?), so that didn’t work.
However, I believe this is a super-helpful process for anyone looking for FANs (Friends, Associates, Neighbours) or those of us working on One Place Studies projects.
Once again my very sincere thanks to Mary for sharing her expertise, permitting me to publish how I used her strategies, and giving me a new skill. I encourage everyone to check out her blog.
12 thoughts on “Applying a lesson”
I have included your blog in this week’s Interesting Blogs on Friday Fossicking at
Thank you, Chris
LikeLiked by 1 person
This is a really useful function in Excel and I first came across it in Tessa Keogh’s video she published earlier this year. https://youtu.be/LSZxGYOQWX0 and managed to download quite a lot of useful data from one of our state sites. Thanks for the reminder, had forgotten about it already!!
LikeLiked by 1 person
See we have both gained. I am so far behind with reading and listening so will do a catch-up with Tessa’s info.
How clever! I will definitely have to try that.
LikeLiked by 1 person
Hope it goes well for you.
Thanks Pauleen, I am going to give it a try too.
I missed this post earlier. Thanks for resharing – will be useful at our Lake House where I have limited internet. Can download for later play. Look out Currys in Australia.
Jill, let me know if you want a lesson on getting your massaged data into Family Historian!
Glad it helped and now you’ve got an expert on tap as well.
FamilySearch has an “Export results” button which is even easier.