Get your piping hot fanfic data here![/b] The fanfic database spreadsheet file is current to stories from the archive's [b]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
Thanks for all the hard work!