Lois & Clark Fanfic Message Boards
Topic Options
#166541 - 09/25/11 11:41 PM Fanfic database updated!
LaurenW Offline
Hack from Nowheresville

Registered: 09/01/03
Posts: 123
Loc: Ohio Valley Sauna
Get your piping hot fanfic data here! The fanfic database spreadsheet file is current to stories from the archive's April 15, 2012 upload, and is available for you to download and search, sort and filter to your heart's content.

This post updated May 12, 2012. Since this post went up in September 2011, we've added new fields to the spreadsheet, requiring an edit on instructions below. The story word count field (column E) is new, and all other columns have shifted over.

While you can view the data online as a Google doc , you can't do much there beyond simply viewing -- and simply searching by pressing CTRL-F to bring up a search dialog. (Though if you were to upload a copy of the sheet into your own Google docs, you'd be able to do a whole lot more.)

For more powerful sorting and filtering options, you'll want to download the sheet for use in Excel or OpenOffice.

Besides adding another year's worth of stories, we've done some data cleanup. LabRat has been entering new themes, and I've split the title and author fields each in two, which will enable better alphabetical sorts on titles and author names.

The file is currently using three levels of sort -- author last name, author first name and story title -- but you can change that.

Speaking of sorting and filtering, here are some things you can do in Excel or OpenOffice -- a totally free office suite, highly recommended! Spreadsheet gurus, feel free to laugh at what follows. But if you're a spreadsheet novice, like I am, maybe you'll find it helpful. And if you have any tips to share, that'd be awesome.

Excel and OpenOffice work very much alike. I've noted where the steps are slightly different. The most annoying difference is that OpenOffice wants to ignore the freeze we put on the first row, which we're using as a header row to label what the different columns are for.

For the record I'm using Excel 2003 and OpenOffice 3.2.1. If you're using different versions and these directions aren't working out for you, try Googling on things like Excel 2007 filtering tips, Excel 2010 filtering tips, etc.

Now load that file into your spreadsheet program, because here comes the fun....

* Do some simple searching by pressing CTRL-F -- or by clicking Edit and then Find.

You know the word chocolate will show up somewhere in story titles and descriptions, don't you? Typing chocolate in the Search field and clicking the Find button is the fastest way to find it. (Minor difference noted: OpenOffice's Find feature is called Find & Replace in the Edit menu. You don't need to type anything in the Replace field.)

* Sort by story title. Here's how to do it: Click into the cell of any story title and click the A-Z sort button on your toolbar. Ta-da!

* Sort by story size, largest first. Who wrote the longest story hosted at lcfanfic.com? Try this and see:

Click into the cell of any story's filesize field, then click the Z-A sort button on your toolbar to see a list of the stories sorted by size, largest first. It's not LabRat who holds the crown anymore. Sorry, Chief! Your "Masques" is now third in length -- but still an excellent read!

* Sort by author and then by story size -- a two-level sort. If your goal is to settle in with a good, long read from your favorite author, this sort will help you find it.

Click the Data menu, then the option called Sort. In the dialog window that appears, choose the author's last name field from the top pick list, then check the Ascending radio button to the side. For the Then by field underneath, choose file size from the list, then check the Descending button.

(The look of the dialog window differs a little between Excel and OpenOffice. In Excel you can actually choose the column label from the dropdown pick lists -- AUTHORLAST and FILESIZE -- because the header row stays frozen. In OpenOffice, you'll need to choose the column letter: First sort by Column I, the column for author last names, then by Column D, where file sizes are kept.)

Now you're ready to click the OK button to view your new sort.

(OK, technically this should've been a three-level sort: author last name, then by first name, then by file size. I was lazy.)

* Filtering for Next Gen fics rated G. Wracking your brain for a story to let your young nephew read? Isn't everyone? Filtering to the rescue.

Filtering lets you isolate those rows that contain a value or values you're filtering for, temporarily hiding all the other rows. Once you've filtered out a set of stories, you can carry out other actions, such as sorting. There are varying levels of complexity to filtering, but AutoFilter gives you the easiest ride.

Click the Data menu, then the option called Filter, and at right, the pop-out option called AutoFilter. Notice that each cell on the first row now sports a nifty little arrow by its column header. Click the arrow in column N, the TIMEFRAME field, and click to select Next Gen from the list of choices it gives you. Notice your spreadsheet got a whole lot shorter -- scrolling to the end doesn't take nearly as long, and lots of rows seem to be missing. Congratulations -- you've successfully filtered for Next Gen stories!

Let's go further and find those G-rated stories: Click the arrow under column M, the RATING field, and select G. (Nephew may vote for PG13, but stick to your guns -- less explaining you'll have to do to his parents.) And the story selection shrinks again to only Next Gen stories rated G.

Does nephew have a short attention span? Find the shortest stories by doing a sort -- clicking into any FILESIZE field and then clicking the A-Z button. You'll find that Marcus Rowland has written the shortest Next Gen fic that's rated G, "Owls Over Metropolis." Nephew is sure to be a Harry Potter fan, so Marcus' story will be a perfect fit. It's a cute story with a twist!

There's a gotcha here with OpenOffice: If the first row happens to be a story record that goes away after a filter has been performed, you can lose the autofilter selection arrows. To avoid the inevitable freakout (mine was short-lived), be sure to click yes if OpenOffice prompts you about using the first line as the column header.

To turn off autofiltering in Excel, simply click Data/Filter/Autofilter again, and all the hidden rows are restored. In OpenOffice, that also works -- if you can still see those little arrows. If you can't see them, you may need to back up by clicking Edit/Undo, over and over, until all rows are restored. Beating your head with your mouse does nothing to bring them back, trust me.

* Filter for all stories whose title contains the word Lois.

You can't sort for this because the word could appear anywhere in the title. Filtering to the rescue again -- but you'll need to go beyond AutoFilter for this one.

Frankly, Excel's Data/Filter/Advanced Filter option scares me. I can't figure it out and should probably Google for tips, but that's a project for another day. OpenOffice's Advanced Filtering is scary too, but thankfully OpenOffice slips in another level of filtering, called Standard, that makes perfect sense. I'll stick with that for now.

In OpenOffice, click the Data menu, then Filter, then Standard Filter. A more complex-looking dialog with a grid of choices appears, but it's easy to use. On the first row, choose Column G (which has the title) as your field name; choose the option Contains as your condition; and type in Lois as your value. (OpenOffice may try to be helpful and autofill the value field with a story title, but just delete anything beyond the word Lois.) Click OK.

Congratulations! You should now have a filtered list of 151 stories whose titles contain the word Lois.

Now what if you want a group of all stories with titles containing Lois or Ultrawoman? Back to Data/Filter/Standard Filter we go. The first row of the grid should be, as before:

Field name: Column G; Condition: Contains; Value: Lois

On the second row of the grid, use this:

Operator: OR; Field name: Column G; Condition: Contains; Value: Ultrawoman

Click OK, and now we're up to 154 stories. (Clark/Superman wins this contest, by the way: There are 231 stories whose titles contain one of those words.)

* Let's make filtering even more complicated!

For a more complex bit of filtering, make the grid look like this:

Field name: Column G; Condition: Contains; Value: Lois
Operator: AND; Field name: Column T; Condition: Contains; Value: Revelation
Operator: AND; Field name: Column A; Condition: Begins with; Value: KERTH
Operator: AND; Field name: Column K; Condition: Contains; Value: 20
Operator: AND; Field name: Column D; Condition: >=; Value: 100

What'd all that mean? We filtered for a story that: 1) has a title containing the word Lois; 2) and contains the word Revelation in any theme field; 3) and has a value in the award field beginning with KERTH, meaning it won or was nominated for a Kerth award; 4) and was written since the year 2000; 5) and is larger than or equal to 100Kb.

The sole survivor of that filter is Shayne Terry's "Dear Lois," another excellent read. And Shayne's first story in our fandom, if I remember right.

There are many, many complex filter operations you can perform.

OpenOffice gotcha: If your filter operation didn't work, check for leftover values from last time in the grid of choices. You might need to choose -none- from all the Field name rows to clear them out.

Anyway, to those still awake to read this, happy searching, sorting and filtering!

And you Excel/spreadsheet gurus, feel free to jump in here with your own suggestions and tips.

Lauren

Top
#166542 - 09/26/11 04:32 AM Re: Fanfic database updated!
Darth Michael Online   content
Nobel Peace Prize Winner

Registered: 04/30/07
Posts: 7868
I'm gotten a bit of sensory overload right now, so I'll keep to jawdrop cool

Michael
_________________________
I go by Michael on the Archives.

Top
#166543 - 09/26/11 11:57 AM Re: Fanfic database updated!
MrsMosley Offline
Merriwether

Registered: 07/28/06
Posts: 1879
Loc: Florida
Wowzers! Thanks, Lauren!
_________________________
lisa in the sky with diamonds

Top
#166544 - 09/26/11 12:03 PM Re: Fanfic database updated!
Lynn S. M. Offline
Pulitzer

Registered: 02/18/10
Posts: 3363
Loc: Delaware
I'm impressed! To borrow LabRat's sentence: Thank you for all of your hard work on this one.

Joy,
Lynn

Top
#166545 - 09/29/11 02:16 AM Re: Fanfic database updated!
LaurenW Offline
Hack from Nowheresville

Registered: 09/01/03
Posts: 123
Loc: Ohio Valley Sauna
Thanks, guys! I hope you're liking the data.

We've updated the spreadsheet with a new column called THEMESCOMBINED, which consolidates themes from the four individual themes column into one for easier filtering. So please ignore what I said earlier about having to run multiple filter operations to find stories by theme -- just do your filtering on Column T. smile

If you're using OpenOffice's Standard filter feature, or have figured out Excel's Advanced filter, it'd be worth downloading the spreadsheet file again for better theme searches.

If you have an iPad, be sure to visit the Google Docs spreadsheet page using it -- you get autofiltering and sorting capabilities built in. This is interesting considering that you don't get these capabilities on the sheet using Internet Explorer, Firefox or Chrome (Google's own browser!). Of course if you were to import the Excel version of the sheet into your own Google Docs account, you'd have full privileges on the sheet as the document owner, and could do sorts and autofiltering in your browser.

Lauren

Top
#166546 - 09/29/11 04:12 AM Re: Fanfic database updated!
jackiek Offline
Top Banana

Registered: 12/15/04
Posts: 1390
Loc: Canada
Wow! Great work. Thanks!
_________________________
Superman: I hear you've been looking for me.
Lois: All my life.

Top
#166547 - 12/25/11 01:24 AM Re: Fanfic database updated!
LaurenW Offline
Hack from Nowheresville

Registered: 09/01/03
Posts: 123
Loc: Ohio Valley Sauna
Looking for a good holiday read? LabRat has been busy updating our fanfic database spreadsheet, tagging Christmas-themed stories, among other things. Want to find all 108 L&C Christmas stories? Download the latest version of the sheet and filter away. (Free OpenOffice Calc is recommended for super-easy sorting and filtering!)

How to filter?

* Load the new sheet into OpenOffice
* Click the Data menu and choose Filter/Standard Filter
* In the Filter criteria dialog, set your options like this...

Field name: Column T
Condition: Contains
Value: Christmas

... and then click OK.

That'll get you a filtered list of Christmas stories on the archive. To read one, just copy the Web address from the URL field (Column C) into your browser.

Merry Christmas and Happy Holidays!

Lauren

Top
#166548 - 12/25/11 08:30 AM Re: Fanfic database updated!
Marcus Rowland Offline
Kerth

Registered: 12/18/05
Posts: 2404
Loc: London, England
One thing I'd add to your suggestions on opening the database spreadsheet - the current version of Open Office isn't available for older Macs e.g. the G3, G4, G5 models and anything else pre the Intel chips) and some other computers with variant processors, but there is another version of the package called LibreOffice that does. It's also free and fully file-compatible with Open Office, I've just checked and it has no problems opening the database.


http://www.libreoffice.org

http://en.wikipedia.org/wiki/Libreoffice
_________________________
Marcus L. Rowland
Forgotten Futures, The Scientific Romance Role Playing Game

Top
#166549 - 12/25/11 05:48 PM Re: Fanfic database updated!
LaurenW Offline
Hack from Nowheresville

Registered: 09/01/03
Posts: 123
Loc: Ohio Valley Sauna
Thanks for the tip, Marcus. I've updated the database page with a link to LibreOffice.

Top
#166550 - 05/21/12 08:14 AM Re: Fanfic database updated!
LaurenW Offline
Hack from Nowheresville

Registered: 09/01/03
Posts: 123
Loc: Ohio Valley Sauna
Bumping this back to the top. The fanfic database has been updated again to include all stories up to April 15, 2012.

Notable updates in the last few months:

  • Story word count field has been added (Column E)
  • New sortable upload date field (Column L)
  • Themes combined (Column T) is where you'll want to perform your filter operations when searching on story themes.


Searching/sorting/filtering examples in previous posts have been edited to reflect updated column labels.

Lauren

Top
#166551 - 05/21/12 08:33 AM Re: Fanfic database updated!
VirginiaR Online   content
Nobel Peace Prize Winner

Registered: 04/24/11
Posts: 9357
Loc: USA
Wow! Thanks, Lauren, for all your hard work!
_________________________
VirginiaR.
"On the long road, take small steps." -- Jor-el, "The Foundling"
---
"clearly there is a lack of understanding between those two... he speaks Lunkheadanian and she Stubbornanian" -- chelo.

Top
#166552 - 05/21/12 08:49 AM Re: Fanfic database updated!
Deadly Chakram Online   happy
Pulitzer

Registered: 05/22/11
Posts: 4435
Loc: Connecticut
Very cool! thumbsup Thanks for all the hard work!
_________________________
Battle On,
Deadly Chakram

"Being with you is stronger than me alone." ~ Clark Kent

"It's all weird! We live in Metropolis!" ~ Jimmy Olsen

Top
#166553 - 05/21/12 09:18 AM Re: Fanfic database updated!
Lynn S. M. Offline
Pulitzer

Registered: 02/18/10
Posts: 3363
Loc: Delaware
Fantastic. Thanks for all the time and effort you put into this, Lauren. It will prove very useful.

Joy,
Lynn

Top

Moderator:  Karen, YConnell