Export WordPress Comments to Excel

This is a simple trick to export WordPress Comments to Excel without a plugin. There are WordPress Plugins available to just about anything. But you don’t always have to install a plugin, sometimes you can easily do it with a quick PHP page. Even though using plugins is the recommended way to add functionality to your WordPress site. There are downsides to doing that.  When you activate a plugin what it does is, it will add  a new function to your current active theme’s functions.php file. If you deactivate the plugin that function will be removed from the plugin. So definitely it is good to use a plugin if you don’t understand WordPress or web development. However, you want to be careful with how many plugins you active on a site. The more you activate the more you are going to slow down your website.

This tutorial (Export WordPress Comments to Excel) is not only for WordPress Developers. Anyone can try this trick without even touching your WordPress files. I am going to explain how you can export WordPress comments to an Excel file without using any plugins. This file will contain all your WordPress comments with all the details like id, date, content etc. It is really simple to setup and does not require a plugin.

All you have to do is just create a php file and put that inside any folder of your WordPress installation. Follow below simple steps to export WordPress comments to excel file.

  1. Just create a new PHP file with any name you would like (but it should be a .php file)
  2. Copy paste below code to it.
  3. Save it to your WordPress installation directory.
  4. After saving it access that file directly.

Whenever you want to export WordPress comments to excel file you can visit this file.

For example if you are creating the file named export.php and saves it to your root directory. If your WordPress site is example.com then, in order to generate csv file with comments you need to visit/access http://example.com/export.php directly.

Note : Below code works if you put this file on your WordPress root folder. If you want to save this file inside another folder just change wp-load.php path on below code. For example if you are saving this file inside your wp-content folder then you have to change the code and add ../wp-load.php instead of ./wp-load.php. Because we need to access wp-load.php file which is located on root folder. If you are trying to access it from wp-content folder ../indicates that wp-load.php file is one folder outside.

// Export WordPress Comments to Excel
define( 'WP_USE_THEMES', false );
require( './wp-load.php' );
global $wpdb;
$query = "SELECT * FROM $wpdb->comments 
    WHERE 1 = 1 
    AND ( comment_approved = '1' OR comment_approved = '0' ) 
    ORDER BY comment_ID DESC";
$error_msg = "Error exporting comments...";
$result = $wpdb->get_results( $query, ARRAY_A ) or wp_die( $error_msg );
$header = array_keys( $result[0] );
$html = array();
$html[] = "<tr><td>" .implode( "</td><td>", $header ) . "</td></tr>";
foreach( $result as $row )
    $html[] = "<tr><td>" .implode( "</td><td>", $row ) . "</td></tr>";
$html = "<table>" . implode( "\r\n", $html ) . "</table>";
$fileName = 'Comments_' . date("Ymd") . '.xls';
header( "Content-type: application/vnd.ms-excel" ); 
header( "Content-Disposition: attachment; filename=$fileName" );
echo $html;

Here’s a little more detail on what this code actually does:

  1. It executes a SQL query against your WordPress database.
  2. The results are pulled back as an array.
  3. Loop through the array and built another array that contains an HTML table.
  4. Response the HTML back to the browser, but set the content type to MS-Excel.
  5. The browser will download the response as a file and open it with Excel.

Note : You need to protect the page. Please make sure that this file is not shown to public anyway. Other wise anyone accessing this file will be able to export WordPress comments to excel file and download it.

Give it a try today and be sure to post your questions or comments.

I’m an avid Technologist, while I do work on architecting solutions that span from the desktop to the data center, my passion is Web Infrastructure.
We will be happy to hear your thoughts

      Leave a reply