Export all posts to import as product items

Question

Up until now, my client has been using WordPress posts as his way to display his products. I now need to export all these posts into a CSV format so that I can format the CSV file and import it back into WP e-Commerce.

I've looked at number of options including plugins to export the posts, exporting directly from phpMyAdmin and using the XML export option within WP but none is coming out as clean as I would like it.

I think I basically need an SQL query that I can run to export only the information from the POSTS table, excluding any other rubbish such as ID numbers, ping status, guid etc.

All I need is:

• Post Title
• Post Content
• Custom Fields
• Categories

Here's the start of my Query

 SELECT * 
 FROM  `wp_posts` 
 WHERE  `post_status` =  'publish'

EDIT: I am open to better solutions if anyone thinks there is a better way to do this?

EDIT

Here's a solution I found on SO but I am not sure where to use this?

<?php
    function array2xml($array, $name='array', $standalone=TRUE, $beginning=TRUE)
    {
    global $nested;

    if ($beginning)
    {
    if ($standalone) header("content-type:text/xml;charset=utf-8");
    $output .= '<'.'?'.'xml version="1.0" encoding="UTF-8"'.'?'.'>' . PHP_EOL;
    $output .= '<' . $name . '>' . PHP_EOL;
    $nested = 0;
    }

    // This is required because XML standards do not allow a tag to start with a number or symbol, you can change this value to whatever you like:
    $ArrayNumberPrefix = 'ARRAY_NUMBER_';

    foreach ($array as $root=>$child)
    {
    if (is_array($child))
    {
    $output .= str_repeat(" ", (2 * $nested)) . '  <' . (is_string($root) ? $root : $ArrayNumberPrefix . $root) . '>' . PHP_EOL;
    $nested++;
    $output .= array2xml($child,NULL,NULL,FALSE);
    $nested--;
    $output .= str_repeat(" ", (2 * $nested)) . '  </' . (is_string($root) ? $root : $ArrayNumberPrefix . $root) . '>' . PHP_EOL;
    }
    else
    {
    $output .= str_repeat(" ", (2 * $nested)) . '  <' . (is_string($root) ? $root : $ArrayNumberPrefix . $root) . '><![CDATA[' . $child . ']]></' . (is_string($root) ? $root : $ArrayNumberPrefix . $root) . '>' . PHP_EOL;
    }
    }

    if ($beginning)
    $output .= '</' . $name . '>';

    return $output;
    }

    //connect to database and select database (edit yourself)
    mysql_connect("localhost", "username", "password");
    mysql_select_db("databasename");

    //Get all posts whose status us published.
    $result = mysql_query("SELECT * FROM wp_posts WHERE post_status = 'publish'");
    while($row = mysql_fetch_assoc($result))
    $posts[] = $row;

    //convert to array and print it on screen:
    echo "<pre>";
    echo htmlentities(array2xml($posts, 'posts', false));
    echo "</pre>";
?>

EDIT:

Right, so I created a php file called pull-posts.php and dropped the above code into the file then saved the file in the root of my localhost installation. I then changed the DB details and ran the script. Here's the error I am getting:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'username'@'localhost' (using password: YES) in C:\inetpub\wwwroot\Xampp-1.7.7\htdocs\project\pull-posts.php on line 40

Line 40 has this on it:

mysql_connect("localhost", "root", ""); mysql_select_db("DBName");

Any thoughts? Thanks.

Answer 1

You don't have to export and then re-import, you can do it all via the API in one go, no XML required. here's a quick example plugin:

<?php
/*
Plugin Name: WPA_convert_types
*/

function wpa_convert_types_page() {
    add_management_page(
        'WPA convert types',
        'WPA convert types',
        'manage_options',
        'wpa_convert_types',
        'wpa_convert_types_render_page'
    );
}
add_action('admin_menu', 'wpa_convert_types_page');

function wpa_convert_types_render_page() {

    if( isset( $_POST['wpa_do_convert'] ) ):
        $args = array(
            'posts_per_page' => -1
        );
        $posts_query = new WP_Query( $args );

        if( $posts_query->have_posts() ):
            global $post;
            while( $posts_query->have_posts() ):
                $posts_query->the_post();

                $new_product = array(
                    'post_type' => 'product',
                    'post_status' => 'publish',
                    'post_title' => $post->post_title,
                    'post_content' => $post->post_content,
                );

                $product_id = wp_insert_post( $new_product );

                // use get_post_meta, wp_get_object_terms
                // to get meta and categories,
                // use $product_id to associate with new product
                // via wp_set_object_terms, add_post_meta

            endwhile;

            echo 'done';

        endif;

    else:
        ?>
        <form method="post">
            <input type="submit" name="wpa_do_convert" value="go">
        </form>
        <?php
    endif;
}

You'll have to add meta and category handling, see the comments below the wp_insert_post line, but this should get you started.

EDIT- I keep forgetting that wp_insert_post lets you set taxonomy terms within the function, see the codex entry for adding categories.