Tuesday, July 17, 2007

Blogger to mySQL

The goal of this process was to get the Blog of the Dead (the story blog I work on) into a mySQL database at another location so new readers could start from the beginning then read the blog chronologically starting with the first gripping hours up through where I got bored with the whole thing. Blogger really wasn't designed to relive a story or sequential posts but it suited my purposes at the time.

The mySQL table uses the post date and time as the primary key and includes the author, Blogger permalink, post title and post body. I didn't save comments. Originally I looked at the Google API set but that only seemed to work with recent posts and you had to have feeds enabled.

Note: this only works for Bloggers using the classic templates (like me).

Step 1: Save your existing template

Copy the code for your existing template and save it somewhere locally, you will need to restore it later

Step 2: Replace your template

<Blogger><$BlogItemDateTime$>»<$BlogItemTitle$>»<$BlogItemAuthor$>»<$BlogItemPermalinkUrl$>»<$BlogItemBody$>
«</Blogger>

Step 3: In Settings | Formatting

  1. set Show to 999 days on main page
  2. set Timestamp format to MM/DD/YYYY HH:MM:SS AM|PM
  3. set Convert line breaks to no

Step 4: In Settings | Archiving

  1. set Archive Frequency to No Archive

Step 5: View your blog

Save the source for your blog somewhere locally

Step 6: Reset everything

Reset your formatting and archiving settings then template back to their original settings and content.

You should now have a text file that will need a little massaging to get into mySQL. I used UltraEdit to get the date/time into an ISO standard format and convert Windows CR/LF combinations to simple CR's. The resulting text file can be locally loaded with no column enclosures, no escape characters, \r plus the line terminator character as record ends, and the column separator character as your column separator. I'll revist this later with more details instructions with SED, etc.; I simply wanted to get my notes stored somewhere.

No comments: