Export WordPress Custom Post Data to CSV format

While doing a recent evaluation of a customer site we needed to export a bunch of data for each post type into a CSV. I needed:

• Post Title
• Author Display Name
• Pretty URL
• Published Date
• Modified Date

While there are some plugins out there like export all urls it doesn’t support custom post types that have their own custom taxonomy. Instead of “hacking” the plugin I turned to WP CLI to get my data out.

This query exports the fields above for a the page post type. If you wanted to do another one change the value of WHERE p.post_type='page' to your custom post type name.

wp db query "
SELECT 
    CONCAT('\"', REPLACE(p.post_title, '\"', '\"\"'), '\"') AS Title,
    CONCAT('\"', u.display_name, '\"') AS Author,
    CONCAT('\"', 
        CASE 
            WHEN (SELECT option_value FROM wp_options WHERE option_name = 'permalink_structure') = '' 
            THEN CONCAT((SELECT option_value FROM wp_options WHERE option_name = 'siteurl'), '/?p=', p.ID) 
            ELSE CONCAT((SELECT option_value FROM wp_options WHERE option_name = 'siteurl'), '/', p.post_name, '/')
        END
    , '\"') AS URL,
    CONCAT('\"', p.post_date, '\"') AS Published_Date,
    CONCAT('\"', p.post_modified, '\"') AS Modified_Date
FROM wp_posts p
LEFT JOIN wp_users u ON p.post_author = u.ID
WHERE p.post_type = 'page' AND p.post_status = 'publish'
" --allow-root --skip-column-names > page_export.csv

This is possible with the plugin listed above, but to relate the custom post type entry to the custom taxonomy entries I used the query below which is not possible in export all urls currently. Specifically the plugin doesn’t support custom taxonomies, but if you’ve related your custom post type to plain WordPress categories, then the plugin would export as expected.

wp db query "
SELECT 
    CONCAT('\"', REPLACE(p.post_title, '\"', '\"\"'), '\"') AS Title,
    CONCAT('\"', COALESCE(t.name, 'No Category'), '\"') AS Category,
    CONCAT('\"', u.display_name, '\"') AS Author,
    CONCAT('\"', p.post_date, '\"') AS Published_Date,
    CONCAT('\"', p.guid, '\"') AS URL
FROM wp_posts p
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'document_taxonomy'
LEFT JOIN wp_terms t ON tt.term_id = t.term_id
LEFT JOIN wp_users u ON p.post_author = u.ID
WHERE p.post_type = 'documents' AND p.post_status = 'publish'
" --allow-root --skip-column-names > document_export.csv

If you have another custom post type with custom taxonomy change documents to your custom post type name and document_taxonomy to the registered name of your custom taxonomy.