Monday, 6 January 2020

Exporting GoodReads reviews to a WordPress blog

In a previous post, I discussed wanting to take a backup of my book reviews from GoodReads. In this post, I’ll go into how I did that in more detail and provide my code for anyone who wants to do something similar. It’s quite lengthy, so details under the fold.

Although I’ve already got a blog (you’re reading it!), I decided not to mirror my book reviews here. I post normal content so infrequently that anyone who wanted to read the blog but wasn’t interested in book reviews would be inundated with content they didn’t want. In the end, I spun up an additional WordPress instance on my web space (something that my host, Krystal Hosting, makes very easy to do) to keep the reviews completely isolated from everything else.

Once I created the WordPress instance, I needed to import my existing content to the fresh blog. To do this, used a guide I found online. That guide is pretty barebones, so some more details follow.

  • Export your data from GoodReads (My Books > Tools > Import and Export). This lets you download a CSV file that you’ll need to manipulate before importing to WordPress.
  • Install the extensions Advanced Custom Fields and Really Simple CSV Importer to your WordPress installation. To do dry runs for importing, there’s a debug plugin for RS-CSV-Importer that you can find at Gist. Download the file from that page, and upload it to the same directory as the main RS-CSV-Importer plugin of your WordPress installation (should be ${WORDPRESS_INSTALL}/wp-content/plugins/really-simple-csv-importer. WordPress should detect it automatically on the Plugins page. When you activate it, doing an import will just do a dry run with additional debug info. To import for real, disable the debug plugin.
  • Use ACF to create whatever additional fields you want to bring over from GoodReads. I created a field group called ‘Book Reviews’ and the following fields:
    • Author (code gr_author)
    • Rating (code gr_rating)
    • ISBN10 (code gr_isbn10)
    • ISBN13 (code gr_isbn13)
    • Publisher (code gr_publisher)
    • Year of publication (code gr_publication_year)
  • In your CSV file, remove any columns you don’t want
  • Some columns need to be named to match WordPress field names, and others will be named to match the custom fields you created above (set the column heading to be the field code, not the label).
  • The following columns are must be named as below:
    • post_title (I made this the title of the book)
    • post_date (I used the date added for this. This should be in format yyyy-mm-dd)
    • post_content (the review itself)
    • post_tags (I used the bookshelves for this)
      • I also did some Excel-fiddling to add my rating to the end of this column as an additional tag, e.g. 3-star, 4-star etc
  • Create two additional columns in your file:
    • post_type (should be ‘post’)
    • post_status (if you don’t include this, the posts default to draft. I included it and set it to ‘publish’)

Note: Be careful when editing the CSV file in a spreadsheet program. It may mess up the ISBN columns. I made sure that the ISBN10 column had a custom format of 0000000000 and ISBN13 used 0000000000000 (i.e. 10 zeroes and 13 zeroes) which kept them displayed properly, including any leading zeroes. I also changed the format of the date this way. This is something you’ll need to set every time you open the file in a spreadsheet program.

I then spent loads of time cleaning up the CSV file. I had used GoodReads internal cross-references to refer to books and authors, which came across in the data dump. I tried to write regexs to find and fix these, but never got something that worked properly, so there was a lot of manual work to do that.

Also, it looked like the file that was created by GoodReads wasn’t UTF-8, so various special characters got mangled. The best I could do here was eyeball the file looking for them to fix manually, so I suspect I missed a lot.

You can import the file into WordPress from menu Tools > Import > CSV

Note: despite what RS-CSV-Importer says, all text fields don’t need to be quoted, any CSV files that matches the spec will work (I spent ages trying to do that before just trying it without having everything quoted and it worked just fine).

This should load all the reviews from the file into WordPress, turning shelves into tags. I already use a custom theme, so hacking it a bit more to include the custom fields on the post was pretty easy. For example, to get the author:

$author = get_post_meta($post->ID, 'gr_author', true);
echo "<h3>By $author</h3>";

You’ll need to edit your own theme to display the custom fields, and this will depend on the theme and if you use a child theme. There’s some documentation on child themes in the WordPress Theme Handbook.

That takes care of existing reviews, but I also want to add all my new ones to this blog as I write them. Yes, I could just copy and paste, but that’s dull and I’d likely forget, as well as being more awkward to bring over the additional metadata (author, ISBN etc) that I wanted to keep. I couldn’t think of any completely automated way to do it, now that GoodReads has removed WordPress from the ‘post to blog’ facility (obviously for our own good, and not just to keep eyeballs on their own site!). The best I could come up with was to write a bookmarklet that I run after writing my review on GoodReads which grabs the review ID from the URL, and calls a PHP script that I wrote to copy the review into the WordPress installation.

So it turns out it’s easy enough to access the WordPress API, as long as you put your script within your WP installation and require_once the WordPress load script. The GoodReads API is also straightforward to use, once you get a developer API key, and returns data in XML format, which PHP can parse with the SimpleXML extension. You can download the script I developed below. If you’re happy using the fields I suggested above then all you’ll need to do is to add your own developer key at the top of the script and set the WordPress user that should the posts should be created under. You can then copy the script somewhere under your WordPress installation.

You can find the bookmarklet that I mentioned below as well. You’ll need to edit the URL near the start to point to the URL of the import script. Then you just need to go to a GoodReads review page and click the bookmarklet, which will transfer the review to your WordPress and load the new post.



No Comments »

No comments yet.

Leave a comment

RSS feed for comments on this post | TrackBack URL

Powered by WordPress

%d bloggers like this: