migrating from blogger to drupal

configure blogger

you need to change a few settings in your blogger account.

under settings -> publishing put your ftp server information.

under formatting set the maximum number of items in the index page to 999 posts (if you where patient enoug to actually post 999 posts on blogger then your don't deserve drupal go somewhere else).

for the rest of the settings use

Date Header Format

Day, Month DD, YYYY

Timestamp Format

DD/MM/YYYY HH:MM:SS AM

Timezone

Africa/Cairo

Date Language

English (United States)

Encoding

Universal (Unicode UTF-8)

Enable Float Alignment

No

under the settings -> archiving tab set

Archive Frequency

No Archive

Enable Post Pages

Yes

blank the other fields

use migration template

under settings -> template use the following code as your blogger template.

note the first line sets the minimum node id to be used when migrating, if you are moving to a new drupal installation then this could be set to 1, but if you are moving to an existing drupal installation then it should be larger than your last node id, an easy way to find largest node id is to run this sql query in mysql

> select MAX(nid) from node;

use this number instead of 100

<?php 
$nid = 100;

<Blogger>
$iid = '<$BlogItemNumber$>';
$nid++;

$node=array();

$node['title']=<<<ENDOFSTRING
<$BlogItemTitle$>
ENDOFSTRING;

if (trim($node['title']) == "") $node['title'] = "title";

$node['body']=<<<ENDOFSTRING
<$BlogItemBody$>
ENDOFSTRING;
$node['date']=strtotime('<$BlogItemDateTime$>');
$node['number']='<$BlogItemNumber$>';
$node['permalink']='<$BlogItemPermalinkURL$>';

echo "INSERT INTO node (nid, type, title, uid, created, changed, comment, promote, teaser, body, revisions, format) 
VALUES(".$nid.", 'story', '".addslashes($node['title'])."', 1, ".$node['date'].", ".$node['date'].", 2, 1, '', '".addslashes($node['body'])."', '', 3);\n\n";

<BlogItemComments>
$comment=array();
$comment['number']='<$BlogCommentNumber$>';
$comment['body']=<<<ENDOFSTRING
<$BlogCommentBody$>
ENDOFSTRING;
$comment['author']=<<<ENDOFSTRING
<$BlogCommentAuthor$>
ENDOFSTRING;
$comment['date']=strtotime('<$BlogCommentDateTime$>');

echo "insert into comments (nid, subject, comment, hostname, timestamp, thread, name) values(".$nid.", 'comment', '".addslashes($comment['body'])."', '127.0.0.1', '".$comment['date']."', '1/', '".addslashes($comment['author'])."');\n\n";

</BlogItemComments>
</Blogger>
?>

after saving the template publish the index page only.

inject data into mysql

now lets say we called the index file index.php next step is to run this generated php code to generate sql statements.

$ php index.php > index.sql

we can then feed this sql to the drupal database (you must have drupal fully configured first).

$ mysql -u username -p databasename < index.sql

fix database

now the data is in the database but some things need to be fixed, drupal relies on a sequences table to generate node and comment ids, you need to fix this table so that the next time you create a node or a comment it gets an id higher than what has been used in migration.

to find out the largest node id and comment id used run the following sql queries

> select max(nid) from node;
...
> select max(cid) from comments;

now that you know the numbers put them in the sequences table, use update statements if you already created nodes and comments before otherwise use insert statements.

> update sequences set id=MAX_CID where name='comments_cid';
...
> update sequences set id=MAX_NID where name='node_nid';

if the update statement doesn't work use insert instead

> insert into sequences values ('comments_cid', MAX_CID);
...
> insert into sequences values ('node_nid', MAX_NID);

fix teasers

now you migrated to drupal and everything works, but the front page shows only titles and links, no node teasers like it should do.

you can fix this from within drupal, simply create a php page node with the following code

<pre>

<?php 

$query = db_rewrite_sql('SELECT n.nid, n.body FROM {node} n');
$result = db_query($query);

while ($node = db_fetch_array($result)) {


  $teaser= node_teaser($node['body'], 1);

  $insert_query= db_rewrite_sql('UPDATE {node} n set n.teaser ="'.db_escape_string($teaser).'" where n.nid='.$node['nid']);
  db_query($insert_query);

  echo $node['nid']."\n";

}
?>

</pre>

simply visiting this node will fix the teasers

fix comment count

finally the only thing missing is to fix comment count, again this can be done from within drupal, by creating a php node with the following content

<pre>

<?php 

$query = db_rewrite_sql('SELECT n.nid, n.body FROM {node} n WHERE nid > 1');
$result = db_query($query);

while ($node = db_fetch_array($result)) {

db_query("insert into node_comment_statistics (nid) values(".$node['nid'].")");

_comment_update_node_statistics($node['nid']);

echo $node['nid']."\n";

}
?>

</pre>

again just visiting the node will fix everything, you probably want to clear the cache table by now just to make sure everything works smoothly.

future work

the above template doesn't do anything about permalinks and multiauthor blogs, maybe in the future we can figure out a way to convert blogger permalinks into drupal paths, and blogger author links into drupal author ids

useability

this is not a a good script, it is a half automated half manual process and shouldn't be done by newbies, you should have minimal knowledge of drupal, php and mysql before you use any of this stuff.

I've no interest in building a full fledged user friendly migration tool, maybe one day people will come up with a good xml standard for moving around blogposts and their comments.