SciFi and Fantasy Book Club discussion

CBRetriever wrote: "this is what works seamlessly for me

export csv file
open Excel
use the Excel menu to open the csv (Browse and change the All Excel files to All files)

it will open and not ask you to select divi..."

I'll give that a try CBRetriever. Thanks for the info :).
The main issue for me though is that for a big portion of my books the read dates are missing. So I can't really sort the authors by books that I read, or for a specific time period, because a lot of that data is missing.

CBRetriever wrote: "this is what works seamlessly for me

export csv file
open Excel
use the Excel menu to open the csv (Browse and change the All Excel files to All files)

it will open and not ask you to select divi..."

You don't actually even need to insert any columns. You can run your pivot table on the .csv file as as is (unless you are having issues with the Author name).

Exclusive shelf and Author in the rows area
Author in the values area (select count as value type).

Count of Author
Exclusive Shelf Author Total
read Robert B. Parker 47
Anne McCaffrey 44
Raymond E. Feist 31
Gordon R. Dickson 28

Can this all be done possibly with Calibre? I mean instead of Excel.

yeah, you could do a count, but I'm so used to inserting a num column for other types of pivot tables, that I usually add one

as far as read dates being missing, assign an arbitrary read by date like 1/1/1900

For the Excel pivot table method people are describing, here are some step-by-step instructions with pictures that some people might find helpful. It’s a lot easier than you might think, even if you aren’t comfortable with Excel.

Some Notes
1. The pictures are in spoiler tags to limit clutter.

2. These steps don’t include the “Additional Authors” column which may have other authors you’d like to include. That would take a little extra manipulation.

3. The instructions look long, but I timed myself and it took me 23 seconds from the time I opened the export, including bonus step #9, so it’s fast once you’ve had some practice.

The Steps
1. Export your books. I think most know how to do this, so I’m going to be a little skimpy on the details for this step: My Books / Import and export (under “Tools” a bit below your shelf list on the left) / “Export Library” button near the top. This will take a little bit of time, then you’ll see a “Your export” link below the button. Click that and open the file with Excel.

2. Make sure your cursor is in a cell with the data. If you haven’t done anything except open the file, you should see your cursor in cell A1 (where it says “Book Id”) which is fine. Then select Insert / Pivot Table.
(view spoiler)[ (hide spoiler)]

3. You should be able to leave everything on the defaults here and just click “OK”.
(view spoiler)[ (hide spoiler)]

4. Find the “Author” field in the field list, then drag and drop it into the “Rows” box. (You could also use the “Author l-f” field if you would prefer to see them listed by last name first.)
(view spoiler)[ (hide spoiler)]

5. Drag the same exact field into the Values box. It should automatically change to “Count of Author” which is what you want.
(view spoiler)[ (hide spoiler)]


6. Now find the “Exclusive Shelf” field and drag and drop that into Filters. If you've only shelved Read books, you can skip this step. Otherwise, you’re probably going to want to filter out the to-read shelf. This will give you that option.
(view spoiler)[ (hide spoiler)]

7. If you did step 6, look at the top of the pivot table that’s been generated for you and you’ll find the filter you created. Click the drop-down button to the right of the All, and choose your “Read” shelf. If you have custom exclusive shelves and want to include more than one in your results, click the “Select Multiple Items” checkbox and then it will let you select multiple shelves. When done, click OK.
(view spoiler)[ (hide spoiler)]

8. Now right-click somewhere under “Count of Author” in Column B and select Sort / Sort Largest to Smallest. This will put your most frequently-read authors at the top.
(view spoiler)[ (hide spoiler)]

9. At this point you’re essentially done, but here’s a bonus step to make this a little more useful. Double-click on any author in your list. A “Show Detail” window will pop up. Select “Title” then click OK.
(view spoiler)[ (hide spoiler)]

The result of the bonus step 9 is that now you can expand an author to see which books you read by them.
(view spoiler)[ (hide spoiler)]

YouKneeK wrote: "For the Excel pivot table method people are describing, here are some step-by-step instructions with pictures that some people might find helpful. It’s a lot easier than you might think, even if yo..."

YouKneeK - this is incredibly helpful! I have just followed your instructions and they have worked perfectly for me. Thank you so much! I feel like an Excel Wizard now!

The instruction is terrific, YouKneeK! - now I have only to find out why all the data are exported into one row with my excel ^^'

YouKneeK - I recently did a similar set of Pivot Table instructions for someone another group I'm in and yours are AWESOME. Mine was like "Click the thing, drag to this, drag that to there, done".

Kudos for taking the time to explain it so well. :D

Gabi wrote: "The instruction is terrific, YouKneeK! - now I have only to find out why all the data are exported into one row with my excel ^^'"

you need to just do the open, not the open with and not the import and it should work (parse it out).

CBRetriever wrote: "Gabi wrote: "The instruction is terrific, YouKneeK! - now I have only to find out why all the data are exported into one row with my excel ^^'"

you need to just do the open, not the open with and ..."

I got it. I have to save the export, then import it into an existing Excel file via "data from text", where I can define that commas are used as field separation

Thanks Lizzie, Gabi, and Becky, I’m glad you thought they were useful! LOL Becky. :) I somehow end up writing a lot of instructions for random things at work, so I’ve had a lot of practice.

Gabi, I’m glad you got it working. For me it has always been as simple as clicking “OK” when the pop-up window shows up. I wonder if it’s something specific to your browser, or maybe it’s retaining a setting you changed in the past?

Here’s what I see when I click on the “Your export” link in my version of Firefox. I just click “OK” and the file opens in Excel with everything already parsed out correctly into individual cells.
(view spoiler)[ (hide spoiler)]

Here’s what I see in Internet Explorer. I just click “Open” and it opens correctly in Excel.
(view spoiler)[ (hide spoiler)]

YouKneeK wrote: "Thanks Lizzie, Gabi, and Becky, I’m glad you thought they were useful! LOL Becky. :) I somehow end up writing a lot of instructions for random things at work, so I’ve had a lot of practice."

Same... My job right now is literally writing technical solution documentation... but still! Kudos! :)

No problem Corinne, I'm happy it helped! :)

Gabi wrote: "CBRetriever wrote: "Gabi wrote: "The instruction is terrific, YouKneeK! - now I have only to find out why all the data are exported into one row with my excel ^^'"

you need to just do the open, no..."

no, no, no don't import it - that then uses the commas in King, Stephen to split that name into two fields.

Open excel
Menu - Open File
Browse
change Excel files to All Files
select your csv files
and it should automatically parse it correctly w/o any interaction on your part

Great instructions YouKnee. Here's my 2 cents addition:
After a bit of playing I found you can drag 'Exclusive Shelf' into the 'FILTERS' box in the Pivot Table Fields to give you a drop down at the top of your table. You can then use that to filter by 'Read'. Otherwise the count includes anything on your 'To-Read' shelf too.

Thanks Andrew. :)

The Exclusive Shelf filter is actually mentioned above in step 6 (creating it) and step 7 (applying it). But I almost forgot to include those steps because I don't actually shelve To-Read books on GR. I added a book to my To-Read shelf just so I could get a proper screen shot for the instructions. :)

If you didn’t already discover it on your own, you might also like “bonus” step 9. In 4 mouse clicks, it converts it into an expandable list so you can expand an author to see which of their titles you’ve read.

CBRetriever wrote: "Gabi wrote: "CBRetriever wrote: "Gabi wrote: "The instruction is terrific, YouKneeK! - now I have only to find out why all the data are exported into one row with my excel ^^'"

you need to just do..."

The way I did it worked perfectly. Each row is correct. There was no separation between first and surname.

And without great surprise my most read author with 25 books is Terry Pratchett, followed by UKLG with 10.

This is a real beginner's question, but am I correct in thinking that every time I want to find out what my new reading stats are (like in another few months when I complete a series or something), I do the same process as described above again?

Lizzie wrote: "This is a real beginner's question, but am I correct in thinking that every time I want to find out what my new reading stats are (like in another few months when I complete a series or something),..."

Yeah- unless you just start adding to the spreadsheet as you go. IMO that would be easier. It's something I do anyway because I track a few different reading stats.

Lizzie wrote: "YouKneeK - this is incredibly helpful! I have just followed your instructions and they have worked perfectly for me. Thank you so much! I feel like an Excel Wizard now!"

We have all leveled up our Excel magic due to master wizard YouKneek! Which one of us will be the dark apprentice she can't control, that creates the one evil spreadsheet to rule them all!!?

LOL Hank, I think this group has a few master Excel wizards running around, at least one of which is a moderator, which means more potential dark apprentices who might get into evil data crunching competitions. Things could go downhill around here very fast!

Lizzie, Sarah’s suggestion of adding manually would be a good option if you’d rather keep using the same spreadsheet instead of doing the whole set of steps all over again.

Another option, especially if you're only going to be checking it every few months, would be to re-export your shelves, then copy and paste the data from the new export over top of the data from the old export in the workbook with your pivot table.

The only trick is that, if you created the pivot table with the default settings as per Step 3 in my instructions, it's only going to recognize the original cells it selected. In other words, if you had 300 books the first time you exported it and you add 5 more books, your pivot table will only see the original 300 rows and not the extra 5 rows. You can fix that problem permanently by changing the data source settings on your pivot table:

1. Click somewhere in your pivot table, and you should see an “Analyze” menu option show up at the top. Click that, then click “Change Data Source”.
(view spoiler)[ (hide spoiler)]

2. When the “Select a table or range” popup shows up, select the columns (The A through AE) above the data to select the entire column. You want your Table/Range to look like I have it in the below screen shot, with the reference at the end only having letters (columns) and no numbers (rows). $A:$AE instead of something like $A$1:$AE$300. Then click OK.
(view spoiler)[ (hide spoiler)]

After you add data, you’ll need to force the pivot table to crunch the latest data by right-clicking somewhere in your pivot table and selecting “Refresh”.

But if you just follow all the original steps from scratch each time, then you don't have to worry about any of the above junk I wrote. :) I'm not really sure it would save much time in the long run if you aren't running it often, since creating a pivot table is pretty fast.

Not a wizard of any kind, but I do know my way around a search engine.

Allison on the other hand, she really *is* the Dark Lord, so all applications for the post of Minion should be directed to her.

XD

Yes, beneath my bubbly exterior is a power hungry nothingness that yearns to crush all delight! I replace closed quotation marks with two apostrophes in strings! I put empty milk cartons back in the fridge and mix up the mild and extra hot salsas in the grocery store!

Don't ask me to do Excel anything, I will make all your vlookups pull OaFK quotes!

Allison wrote: "I replace closed quotation marks with two apostrophes in strings!"

MONSTER! O_O

LOL, I’m creating an extra strong Anti-Allison Firewall for my computer…

mwahahahaha! All shall love me and despair!

Goodbye goodreads, must disconnect from the internet....Allison is coming

Allison wrote: "XD

Yes, beneath my bubbly exterior is a power hungry nothingness that yearns to crush all delight! I replace closed quotation marks with two apostrophes in strings! I put empty milk cartons back i..."

As long as all light switches are toggled in pairs (none of that one up one down nonsense) we can still be friends.

Phillip wrote: "Allison wrote: "XD

Yes, beneath my bubbly exterior is a power hungry nothingness that yearns to crush all delight! I replace closed quotation marks with two apostrophes in strings! I put empty mil..."

You would not like my house. 4 way switches all over the place.

You just flip it the opposite way of whatever it is now to get the state that is different from the current state. If the room is dark, and the switch is up, flip it down. Magic! :P

My Most Read Authors based on my read bookshelf of 768 books are:
Roger Zelazny: 22
John D. McDonald: 21
Isaac Asimov: 20
George R. R. Martin: 20
Michel Crichton: 17
Robert E. Howard: 17
Ian Fleming: 14
Stephen King: 14
Brandon Sanderson: 14
Anne McCaffery: 13
Robert Heinlein: 11
L. Ron Hubbard: 11
Ernest Hemingway: 10
These are the authors with 10 or more books on my read shelf

Gary, other than John McDonald I have the same books. Now I am looking for John McDonald books , dam you , I have stacks of books to read on my to read shelf

here's mine:

Norton, Andre 50
Jance, J.A. 47
Whitney, Phyllis A. 37
Heyer, Georgette 34
Evanovich, Janet 26
Grafton, Sue 25
Box, C.J. 24
Lackey, Mercedes 22
Peters, Ellis 21
Leon, Donna 19
Roberson, Jennifer 19
Black, Cara 18
Goodkind, Terry 18
Hobb, Robin 18
Modesitt, L.E. 18
Tchaikovsky, Adrian 17

I can’t find this “most read authors” shelf. I even went to the website (which I almost never use) and looked there. Nope. Is this something GR adds to your shelves automatically or what?

Oh duh. I went to the first entry immediately because I’ve never looked at this thread and didn’t even bother reading the end of it. I misread 4 years ago as 4 days ago!! Oops.