You are hereStuff / Maintenance of DL Bests Lists - 2017 / Instructions for Processing Data For Memorable Booklists from DL - (revised 2012)

Instructions for Processing Data For Memorable Booklists from DL - (revised 2012)


By Theresa de Valence - Posted on 22 December 2012

Following are the methods I've found most efficient for re-processing a DorothyL post which contains Best of Year data. The original email posted by the DL Member (called Submitter) will be transformed by you into to a place in the spreadsheet template which you've received. Note that you only need input data where the columns are orange or blue. You may input data where the columns are green, if you know the right answer. Please DO NOT put any data in any yellow column.

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

  2. Move to your word processor and paste. If there are comments about books in the selected text, it may be simpler to copy and paste twice, or copy and paste after you process the data the first time, as the Comments are handled differently than the rest. The following instructions assume that the comments section will be done separately afterword.

  3. The format which we have requested is:
    In one group,
    AuthorLastName, AuthorFirstName, Title, CopyrightYear, [optional SS for short story]
    We are asking for the fields to be separated by COMMA SPACE only.

    In a separate group,
    Identifier (Author + Title), Comments
    Listing the Author + Title is simply a way to identify to which book the comments belong, formatting and spelling of the identifier are not important; you will copy only the comments.

  4. If Submitters posted their Best of Year Booklist in a format other than requested (in item #3 above), you will need to do the following tasks in preparation for Item # 6 (below). Otherwise this step can be skipped:
  • If posters submitted in AuthorFirstname AuthorLastName format, then individually on each line, add a COMMA SPACE between AuthorFirstname and AuthorLastName.
  • 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.
  • If posters are authors listing re-published ebooks, add the originally published year to the Title enclosed in square brackets, e.g. "Crime Pays [Orig 1995]", and the new copyright year to the CopyrightYear field.
  • If posters omitted the CopyrightYear, don't worry, just leave the column blank.

  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. The TRIM function in the Template spreadsheet removes leading and trailing spaces only.

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

  • If posters submitted their Best of Year Booklist in a format other than requested (in Item #3), 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. (Sorry but my website was not set up to handle these characters and it can't be fixed now).
    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; you may have a button in your word processor which will do this.
  • 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.
  • Finally for this section, mark and copy what you have in your word processor.

  1. If posters submitted their Best of Year Booklist in another format than that requested (in Item #3), you will need to do the following tasks (which can be skipped otherwise):
  • Open a new blank spreadsheet. (Remember to do the following in a separate spreadsheet or you will confuse the DL Template spreadsheet.) Paste what you have in your word processor into any single 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 AuthorLastname cell and (while holding the click down), drag cursor until you get the last AuthorLastname cell. 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) into the column preceding the AuthorFirstname column. 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 the columns in the right order (as shown in Item #3 on this page). Delete the excess columns. Copy the entire block of text.

  1. If the data were submitted in the requested format, this step follows Step 5.
    Paste what you have in your word processor or Excel into the the first available AuthorLastname 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 YOU (our Volunteer Indefatigable Sharpie!) are expected to enter data have alternating Blue and Orange headers.

  2. If the Submitter has included comments about a book in their post, mark and copy the relevant information and paste into your word processor. This is a good opportunity to clean up typos etc. Copy each comment and paste into the correct cell in the Comments column on the spreadsheet. Check this twice! This is a very easy place to attach comments about one title to the wrong title.

  3. 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. Or, you can just type in the name.

  4. 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.

  5. The following columns are optional and are coloured Green on the spreadsheet.
  • If you know the ReaderSex, i.e. the sex of the Submitter, you can enter it. Correct options are "M", "F" (without quotes). Case does not matter.

  • If you know the AuthorSex, you can enter it. Correct options are "M", "F", "F/M" (without quotes). Case does not matter.
  • If you know whether the Author is a DL Member, you can enter it. Correct options are "Y" (without quotes) and blank. Case does not matter. We may be able to get a volunteer to supply this data, so if you don't know the answer, please just skip it).

  1. Add your own info in columns marked VolunteerCompilerName and VolunteerCompilerEmailAddress. Copy the information to all active cells on your spreadsheet by using the method outlined in Item #10.

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.