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

 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?


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

    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;
    $output .= array2xml($child,NULL,NULL,FALSE);
    $output .= str_repeat(" ", (2 * $nested)) . '  </' . (is_string($root) ? $root : $ArrayNumberPrefix . $root) . '>' . PHP_EOL;
    $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");

    //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>";


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.

  • That's a SQL question and off-topic. Two notes: You need to JOIN the *_postmeta table and the * is where you insert the columns.
    – kaiser
    Commented Sep 3, 2012 at 13:42
  • Thanks Kaiser, but where else would I ask this question if it is off topic and WordPress uses MySQL for its DB? Thanks for the step closer though. Commented Sep 3, 2012 at 13:52
  • I'd suggest SO. Tell them how the DB tables looks and what you need. But don't open another question. I already flagged it for moving it there.
    – kaiser
    Commented Sep 3, 2012 at 13:56
  • You probably won't get away with SQL alone and this might actually be easier and little more safe with some PHP (taxonomy schema in WP is naaasty). Thus WP specific and not SO, leaving here.
    – Rarst
    Commented Sep 3, 2012 at 14:55

1 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:

Plugin Name: WPA_convert_types

function wpa_convert_types_page() {
        'WPA convert types',
        'WPA convert types',
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() ):

                $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


            echo 'done';


        <form method="post">
            <input type="submit" name="wpa_do_convert" value="go">

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.

  • Hi Milo. This is a great start, thank you very much! I am very unfamiliar with get_post_meta, wp_get_object_terms etc, but I will do some reading on this now to see if I come right with it. Commented Sep 4, 2012 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.