Export data

When we need to export all data from a specific post type, for instance, our copy team needs a spreadsheet with all the blogs that are currently in a site to work on them or to have as backup, we need to follow these steps:

1. Create a new page template

First of all, we are going to go into our filesystem for the project we need to extract the data from and create a new php file with all the template pages. We can call this page-csv.php, for instance (but it is not relevant).

2. Add the following script into the template

This is a model for the type of script we will need to add to that template. You’ll need to modify it to fit your needs. This one extracts posts from the type ‘resource’ and creates a CSV with eight fields, including Title, Date, Type, Topic…

page-csv.php

<?php
    /*
        Template Name: Page CSV
    */
?>
<?php
// The columns of the CSV
  $list = array(array('Link URL','Image URL','Title','Date','Type','Topic','Meta Title','Meta Description'));
  // The full list of posts we are interested in extracting
  $awards_args = array(
      'post_type' => 'resource',
      'posts_per_page' => -1,
      'orderby' => 'date',
      'order' => 'DESC'
  );
    // Execute the query to get the posts
    $awards_query = new WP_Query( $awards_args );
    // Loop through the posts
    while ( $awards_query->have_posts() ) : $awards_query->the_post(); ?>
    <?php
        /*
            The following lines extract concrete data from the document. Replace with the data you need from your document.
        */
        // Get the type taxonomy
         $term_obj_list = get_the_terms($post->ID, 'resource_type' );
         $type_string = ($term_obj_list) ? join(', ', wp_list_pluck($term_obj_list, 'name')) : '';

        // Get the topic taxonomy
        $term_obj_list = get_the_terms($post->ID, 'resource_topic' );
        $topic_string = ($term_obj_list) ? join(', ', wp_list_pluck($term_obj_list, 'name')) : '';

        // Get meta title and meta description
        $meta_title = get_post_meta(get_the_ID(), '_yoast_wpseo_title', true);
        $meta_description = get_post_meta(get_the_ID(), '_yoast_wpseo_metadesc', true);
    ?>

    // Push the data into the CSV, ordered to fit the columns defined above
    <?php array_push($list, array(
        get_the_permalink(),
        get_the_post_thumbnail_url(),
        htmlentities(get_the_title()),
        get_the_date('d-F-Y'),
        $type_string, $sector_string, $meta_title, $meta_description
    )) ?>
    <?php endwhile; ?>
    <?php wp_reset_query();
    // Create the .csv file in the root of the project
    $fp = fopen($_SERVER["DOCUMENT_ROOT"]."/wp-content/themes/uni-wp-theme/insights.csv", "a+");
    // Add the information to the .csv file
    foreach ($list as $line) {
        fputcsv($fp, $line);
    }
    fclose($fp);
?>

3. Upload the new template to FileZilla

We’ll need to have the template available in the environment we need to extract the data from, so we can upload it directly to FileZilla at the root of the environment we are interested in.

4. Create a new page that uses the template

Once we have our template available, we’ll enter the WordPress dashboard and create a new page that uses this new template. We do not need to publish it, only save it and then visit it.

5. Get the CSV

Once we visit the page the CSV should have been created at the root of the project. We can go back into FileZilla, hit right click -> refresh to see the updated file list and we should have our CSV.

Note

If we need to change something in the script and re-run it, we’ll need to empty or delete the .csv file before doing it, since if we do not do that it will just paste the new data in the previous CSV.

6. Make it pretty

With our CSV, we can go into Google Sheets and import it to create a new sheeet for the team that needs it.

7. Erase the script and the CSV and the page from WP

To finish, we can erase both the template / script and the CSV from the project. We also need to delete the draft we created in WP. Basically, we need to restore the project to how it was before we started this process.