You are hereDorothyL / Memorable Books of 2011 on DorothyL / Processing Data For DL Best of Year Booklists-2011 and earlier

Processing Data For DL Best of Year Booklists-2011 and earlier


By Theresa de Valence - Posted on 13 December 2009

Here are the methods I've found most efficient for processing the posted Booklist data.

  1. Mark and copy the area of the email where the Best of Year Booklist is located.

  2. Move to your word processor and paste.

  3. If posters submitted their Best of Year Booklist in another format than that requested, you will need to do the following tasks (which can be skipped otherwise):
  • If posters submitted in AUTHOR FIRSTNAME AUTHOR LASTNAME format, then individually on each line, add a COMMA SPACE between AUTHOR FIRSTNAME and AUTHOR LASTNAME.
  • If posters used anything other than COMMA SPACE, then individually on each line, replace all other separations between fields, e.g. " - " (SPACE DASH SPACE), with COMMA SPACE.

  1. The rule for what you’re about to do here is to standardize the data, so that Excel’s pivot tables can process the author + title cells and accumulate all the ones which are the same.
  • Search for: SPACE SPACE
    and Replace with: SPACE
    Repeat this until your word processor reports "not found". The spaces are very hard to see, even for an old hand, so please don't skip this step. The maximum number of times I have had to do this was about 20, but usually it’s accomplished in 6-8 passes. It’s an important step to perform here, because it’s much easier to do now than it will be to find and remove all the excess spaces later.

  • Search for: COMMA SPACE
    and Replace with: TAB 1

  • If posters submitted their Best of Year Booklist in a different format, and you didn’t add a COMMA SPACE in between fields, then
    Search for: COMMA
    and Replace with: TAB

  • Search for: anything with a special character (e.g. accent grave, accent aigu, cedille, circumflex, tilde, umlaut, etc.) and
    Replace with: standard english characters
    It may be much easier to just delete and replace the characters than to search for them programmatically.

  • Note: it will also be necessary to convert all “smart quotes” to "straight quotes" and ’smart apostrophe’ to 'straight apostrophe' as well, but I can do that globally across the whole spreadsheet at the end.

  • For those posts where the title or author is all in caps, I don’t bother converting the data from uppercase to upper/lower case. Excel’s pivot tables can correctly lump things regardless of case. However, if you care, the text will look better upper/lower case.

  • When I first began this compilation in 2006, I went to the trouble to remove all the articles from the titles. Please DON’T do this.

  1. If posters submitted their Best of Year Booklist in another format than that requested, you will need to do the following tasks (which can be skipped otherwise):
  • Open a new blank spreadsheet. Mark and copy what you have in your word processor and paste it into any cell on the spreadsheet. Because you only mark one cell as the recipient of the data on the clipboard, Excel will take as many cells as it needs (and you don’t have to correctly mark the size of the receiving cell area).

  • If you’re using Excel (and perhaps other spreadsheets as well), click down once on the top AUTHOR LASTNAME cell and (while holding the click down), drag cursor until you get the last AUTHOR LASTNAME. You have now "marked" the text. Cut the text (right click, select cut OR Control-X) and paste (right click, select paste OR Control-V). I have never found a way to accomplish this with one cut and paste, so you will have to do this a couple of times to get it in the right order.

  • Remember to do this in a separate spreadsheet or you will confuse the DL Template spreadsheet.

  1. If the data were submitted in the requested format, this step follows Step 4.:
    Mark and copy what you have in your word processor and paste it into the the first available AUTHOR LASTNAME cell in your DL Template spreadsheet . Because you only mark one cell as the recipient of the data on the clipboard, Excel will take as many cells as it needs (and you don’t have to correctly mark the size of the receiving cell area). Note that all columns where VOLUNTEER is expected to enter data have alternating Blue and Orange headers.

  2. The person who originally emailed the Best of Year booklists to DL is known as the SUBMITTER. Copy their name from the email into the first SUBMITTED BY cell on the spread sheet. Copy the email address from the email into the first SUBMITTER EMAIL ADDRESS cell on the spread sheet. Note that I can do this by right-clicking on the email sender’s name info (on the email itself) and click "Add to Address Book" where my email program correctly separates the sender’s name from the email address. I don’t actually ever add those people to my address book, but I behave as though I do so as to programmatically separate the one from the other.

  3. If you’re using Excel (and perhaps other spreadsheets as well), when you position your cursor over the cell, you get a white-filled Plus Sign (+). If you hover over the lower right corner of the cell, it turns into a thin black PLUS SIGN (+). When you get the thin black PLUS SIGN, click down once and (while holding the click down), drag the SUBMITTED BY name down for all the their entries from their email. Do this for SUBMITTER EMAIL ADDRESS as well. It’s possible to do them both together if you mark both cells first.

  4. If you know the READER SEX, you can enter it (and copy the computations in cells READER MALE and READER FEMALE as well. It is not difficult to update this data when I assemble it, so if you find these instructions confusing, just skip it).

  5. If you know the AUTHOR SEX, you can enter it (and copy the computations in cells AUTHOR MALE and AUTHOR FEMALE as well. It is not difficult to update this data when I assemble it, so if you find these instructions confusing, just skip it).

  6. If you know whether the AUTHOR is a DL MEMBER, you can enter it (and copy the computations in cells AUTHOR IS DL MEMBER. We may be able to get a volunteer to supply this data, so if you don't know the answer, PLEASE just skip it).

  7. Add your own info in columns marked VOLUNTEER NAME and VOLUNTEER EMAIL ADDRESS. Copy the information to all active cells on your spreadsheet by using the method outlined in Item #8.


  8. You’re done! Onto the next entry!

    • 1. In the word processor's search/replace function, you will need to use a special key sequence to represent TAB. In Microsoft Word, tab is represented by "^t", in Framemaker tab is represented by "\t", in Open Office tab is represented by "I don’t know". Omit the quotes. Please tell me about others as you discover them.