Integrating Genealogy and Spreadsheets

Extracting Data from the Internet – Lesson 1 Ancestry.com

Downloading and inserting data from family history sites can be done a few different ways. In this post, I will show you one way to extract results from a search on Ancestry.com and manipulate the formatting in your Excel file. The objective here is to make a usable listing to annotate with subsequent research. Subsequent posts will go through additional ways to download and actually manipulate the extracted data. But for now, we dive into inserting and formatting….

So, first, here is a search result from Ancestry.com on the surname Keesberry:

To copy the data, you could highlight the whole thing by dragging your mouse, but a quicker (and easier) way is to …

Then, you use CTRL-V to deposit the data into your spreadsheet.

Right away, you want to be able to see when you did this and what your search was. Because we will be using the columns to format the data (you’ll see), it is best if you drop this information into column one, leaving it otherwise blank (your text can carry across the page, but don’t merge the cells).

Now we need to fix what we see to make it usable. First, we are going to highlight the column headers over the data. This will “select” all of the data we just imported so that we can remove all of the wrapped text (sometimes you have to click wrap text twice to get rid of all of the wrapped text – just make sure the “wrap text” button is not highlighted when you are done)…

Now we want to widen the columns so we can see the data. So, select columns E and F, then move your cursor to the edge of either of the columns until that + icon appears (see below). Double click, and the columns will resize so that all data is visible.

This looks much better now! It is visible and condensed. But, we aren’t quite done yet.

We still can’t see everything we want, so we’ll make a few more changes. We want to get the census reference in there. Remember that we did not remove the “merge” that was imported in this particular file, so columns C and D have merged cells. What cells are or are not merged varies with the site and merged data. It is easy to un-merge cells, but not as easy to re-merge them, so I usually wait until I have played with the data before I un-merge. Wrapping and un-wrapping test is much easier. In this case, we are now going to re-wrap the text in columns C and D.

Now, we have something can use!

In this example, I set up a background image. That was just for show so that you could see the columns we were working with. If you are going to use this data to make notes, or insert images, you wouldn’t want that background.

In another post, I will go into another type of data import and do more manipulating of the data, rather than just the format.