Import Data from any database into Wordpress

I recently had to find a way to import hundreds of pages into a Wordpress site from a Access database. I considered two solutions: query the Access db and insert the data into Wordpress or query the Access db and write an xml file that would match a Wordpress export.

I went for the second option because there are some database stuff I am yet not too sure about and I was afraid I would get corrupted data.

First you need to get php to connect to your Access database, but you need to have the database on your local computer as an Access file. I had never done it before but it was pretty straight forward (I work on xp pro so I can only give you instructions for this operating system).

First you  an ODBC connection to your Access database

• Go to the control panel and open Administrative tools
• Double click on “Data Sources (ODBC)”
• Click on the “System DSN” tab and click “Add…”
• Select the driver corresponding to the database you want to connect to. In my case “Microsoft Access Driver (*.mdb)”
• I the next screen fill in the Data Source Name and press “Select” to locate the data file on your machine
• Click “Ok” and you’re done

Now you can use php to connect to this database using the odbc_connect method. A simple query look like this:

<?php

$conn=odbc_connect('put_here_the_name_of_your_DataSourceName','','');

if (!$conn){
	exit("Connection Failed: " . $conn);
}

$sql = "SELECT * FROM Mytables";

$result = odbc_exec($conn,$sql);

if (!$result){
	exit("Error in SQL");
}

$fetchedRes = array();
		
while($rows = odbc_fetch_array($result))
{
	$fetchedRes[] = $rows;
}

echo "<pre>".print_r($fetchedRes)."</pre>";

?>

Now that we can retrieve data from the existing db we need to write the xml file to import that into Wordpress. The best way to understand what the Wp import function expect is to create a post, export it and look at the xml file generated.

The main elements in this xml file are:

• Header with general info about the blog liek Title, path, etc…
• Categories, enclosed in a <wp:category> element
• Posts/attachments enclosed in a <item> element.

So all we have to do is create xml elements with the right data in them. Here is an example of a script that pulls data from Access and create a ready to import xml file. Before you dive into the copy paste job remember, you need to know what you are doing and be familiar with php. I am not responsible for any loss of data/time

<?php
	function importPanel()
	{
		//Create the odbc connection link
		$conn=odbc_connect('data_source_name','','');

		//if no connection exit;
		if (!$conn)
		  {exit("Connection Failed: " . $conn);}

		//request the categories from the category table
		// TOP is the MS SQL equivalent of LIMIT in MySQL
		$sql="SELECT TOP 10 category_name  FROM category";

		//execute the query
		$result = odbc_exec($conn,$sql);

		if (!$result)
		  {exit("Error in SQL");}

		$cats = array();
		
		//fetch each row into an array
		while($rows = odbc_fetch_array($result))
		{
			$cats[] = $rows['category_name'];
		}
		
		//Here we turn output buffuring on so everything that is printed (echo / print) will go into the buffer instead of being printed to the screen
		// This is usefull later when we save the buffer into a file
		ob_start();
		?>
		
		<!-- XML file header from wordpress export, just copy and replace values when necessary -->
		
		<?xml version="1.0" encoding="UTF-8"?>
		<!-- This is a WordPress eXtended RSS file generated by WordPress as an export of your blog. -->
		<!-- It contains information about your blog's posts, comments, and categories. -->
		<!-- You may use this file to transfer that content from one site to another. -->
		<!-- This file is not intended to serve as a complete backup of your blog. -->

		<!-- To import this information into a WordPress blog follow these steps. -->
		<!-- 1. Log into that blog as an administrator. -->
		<!-- 2. Go to Tools: Import in the blog's admin panels (or Manage: Import in older versions of WordPress). -->
		<!-- 3. Choose "WordPress" from the list. -->
		<!-- 4. Upload this file using the form provided on that page. -->
		<!-- 5. You will first be asked to map the authors in this export file to users -->
		<!--    on the blog.  For each author, you may choose to map to an -->
		<!--    existing user on the blog or to create a new user -->
		<!-- 6. WordPress will then import each of the posts, comments, and categories -->
		<!--    contained in this file into your blog -->

		<!-- generator="WordPress/2.8" created="2009-06-15 15:06"-->
		<rss version="2.0"	xmlns:excerpt="http://wordpress.org/export/1.0/excerpt/"
			xmlns:content="http://purl.org/rss/1.0/modules/content/"
			xmlns:wfw="http://wellformedweb.org/CommentAPI/"
			xmlns:dc="http://purl.org/dc/elements/1.1/"
			xmlns:wp="http://wordpress.org/export/1.0/"
		>
		
		<title>Blog name</title>
		<link>http://localhost/bnb-cms</link>
		<description>Just another WordPress weblog</description>
		<pubDate>Mon, 15 Jun 2009 14:05:59 +0000</pubDate>
		<generator>http://wordpress.org/?v=2.8</generator>
		<language>en</language>
		<wp:wxr_version>1.0</wp:wxr_version>
		<wp:base_site_url>http://localhost/bnb-cms</wp:base_site_url>
		<wp:base_blog_url>http://localhost/bnb-cms</wp:base_blog_url>

		<!-- END of XML file header -->
		
		<?php 
		//Here we loop through each category and create the xml for it
		
		foreach($cats as $cat): ?>

			<wp:category>
				<!-- You need to slug the name of the cat for the nicename. I provide a slug function at the end of the script -->
				<wp:category_nicename><?php echo slug($cat) ?></wp:category_nicename>
				<wp:category_parent><?php //put here the category parent if applicable. You might need to change your sql request to get that ?></wp:category_parent>
				<wp:cat_name><![CDATA[<?php echo $cat; ?>]]></wp:cat_name>
			</wp:category>

		<?php endforeach; ?>

		<?php

		/*
		* Here we create the posts.
		*/
		
		$sql="SELECT TOP 10 * FROM theOldPostsTable";

		$result = odbc_exec($conn,$sql);

		if (!$result)
		{exit("Error in SQL");}

		//Loop through each of the posts
		while($rows = odbc_fetch_array($result))
		{
			$title = $rows['BOOKINGTOWN'].", ".substr($rows['ADDRESS1'], 0, -1);
			$url = $this->slug($title)."-".$rows['HOSTBFID'].".html";
			$pubdate = date("D, d M Y H:i:s \G\M\T",time());
			$guid = get_bloginfo('url')."/?p=".$rows['HOSTBFID'];
			
			?>
				<item>
				<title><?php echo $rows['title'] ?></title>
				<link><?php echo slug($rows['title'])."-".$rows['id'].".html"; ?></link>
				<pubDate><?php echo date("D, d M Y H:i:s \G\M\T",time()) ?></pubDate>
				<dc:creator><![CDATA[admin]]></dc:creator>

				<?php 
				//here you will need to provide the category name of the post but I can't help you, it depends on how your db is structured
				if($cat): ?>
					<category><![CDATA[<?php echo $cat ?>]]></category>
					<category domain="category" nicename="<?php echo slug($cat) ?>"><![CDATA[<?php echo $cat ?>]]></category>
				<?php endif; ?>

				<guid isPermaLink="false"><?php echo get_bloginfo('url')."/?p=".$rows['id']; ?></guid>
				<description></description>
				<content:encoded><![CDATA[<?php echo $rows['description'] ?>]]></content:encoded>
				<excerpt:encoded><![CDATA[<?php echo $rows['shortdescription'] ?>]]></excerpt:encoded>
				<wp:post_id><?php echo $rows['id'] ?></wp:post_id>
				<wp:post_date><?php echo date('Y-m-d H:m:s', time()) ?></wp:post_date>
				<wp:post_date_gmt><?php echo date('Y-m-d H:m:s', time()) ?></wp:post_date_gmt>
				<wp:comment_status>open</wp:comment_status>
				<wp:ping_status>open</wp:ping_status>
				<wp:post_name><?php echo $this->slug($title) ?></wp:post_name>
				<wp:status>publish</wp:status>
				<wp:post_parent>0</wp:post_parent>
				<wp:menu_order>0</wp:menu_order>
				<wp:post_type>post</wp:post_type>
				<wp:post_password></wp:post_password>
				
				</item>

				
				<!-- You can also import pictures attached to the posts -->
				<item>
					<title><?php echo $rows['image_name'] ?></title>
					<pubDate>Mon, 15 Jun 2009 15:54:42 +0000</pubDate>
					<dc:creator><![CDATA[admin]]></dc:creator>
					<category><![CDATA[Uncategorized]]></category>
					<category domain="category" nicename="uncategorized"><![CDATA[Uncategorized]]></category>

					<guid isPermaLink="false">http://localhost/!path_to_your_upload_folder!/uploads/date_you_want_here/<?php echo $rows['image_name'] ?>.jpeg</guid>
					<description></description>
					<content:encoded><![CDATA[]]></content:encoded>
					<excerpt:encoded><![CDATA[]]></excerpt:encoded>
					<wp:post_id><?php echo $rows['id']*10 ?></wp:post_id>
					<wp:post_date>2009-06-15 16:54:42</wp:post_date>
					<wp:post_date_gmt>2009-06-15 15:54:42</wp:post_date_gmt>
					<wp:comment_status>open</wp:comment_status>
					<wp:ping_status>open</wp:ping_status>
					<wp:post_name><?php echo $rows['image_name'] ?></wp:post_name>
					<wp:status>inherit</wp:status>
					<wp:post_parent><?php echo $rows['id'] ?></wp:post_parent>
					<wp:menu_order>0</wp:menu_order>
					<wp:post_type>attachment</wp:post_type>
					<wp:post_password></wp:post_password>
					<wp:attachment_url><?php echo $rows['image_url'] ?>.jpeg</wp:attachment_url>
					<wp:postmeta>
					<wp:meta_key>_wp_attached_file</wp:meta_key>
					<wp:meta_value>2009/06/<?php echo $rows['image_name'] ?>.jpeg</wp:meta_value>
					</wp:postmeta>
				</item>

			<?php
		
		}
		
		//flush the buffer into a variable
		$export = ob_get_contents();
		ob_end_clean();

		//create a new file to store the exported data
		$fp = fopen('../export-from-access.xml', 'w');
		fwrite($fp, $export);
		fclose($fp);
		//close the odbc connection
		odbc_close($conn);

	}

	function slug($string)
	{
		$slug = trim($string);
		$slug= preg_replace('/[^a-zA-Z0-9 -]/','', $slug); // only take alphanumerical characters, but keep the spaces and dashes too...
		$slug= str_replace(' ','-', $slug); // replace spaces by dashes
		$slug= strtolower($slug); // make it lowercase
		return $slug;
	}
?>

Download the file custom import

Now go to your wordpress admin>tools>import and select “from wordpress” at the bottom. Upload your file and see how it goes. One limitation I ran into is that you can’t import post metadata that are serialized array. Simple text is ok though. You should be able to apply this process to any database type as long as php can connect to it.

Related Items and Services:
For a great range of Filing Cabinets , look no further than Kardex.
Printed Stationery at great prices.

• 
• 
• 
• 
• 
•