export to csv wordpress

Question

I need to export data in one table in a csv file. I'm able to get the data fine but the CSV file is not being generated by the browser.

My code is like this: its the problem with headers. I'm getting only the output with commas separated values but not getting csv file.

/* Converting data to CSV */

public function CSV_GENERATE($getTable)
{
    ob_clean();
    global $wpdb;
    $field='';
    $getField ='';

    if($getTable){
        $result = $wpdb->get_results("SELECT * FROM $getTable");
        $requestedTable = mysql_query("SELECT * FROM ".$getTable);
        // echo "hey";die;//var_dump($result);die;

        $fieldsCount = mysql_num_fields($requestedTable);

        for($i=0; $i<$fieldsCount; $i++){
            $field = mysql_fetch_field($requestedTable);
            $field = (object) $field;         
            $getField .= $field->name.',';
        }

        $sub = substr_replace($getField, '', -1);
        $fields = $sub; # GET FIELDS NAME
        $each_field = explode(',', $sub);
        $csv_file_name = $getTable.'_'.date('Ymd_His').'.csv'; 
        # CSV FILE NAME WILL BE table_name_yyyymmdd_hhmmss.csv

        # GET FIELDS VALUES WITH LAST COMMA EXCLUDED
        foreach($result as $row){
            for($j = 0; $j < $fieldsCount; $j++){
                if($j == 0) $fields .= "\n"; # FORCE NEW LINE IF LOOP COMPLETE
                $value = str_replace(array("\n", "\n\r", "\r\n", "\r"), "\t", $row->$each_field[$j]); # REPLACE NEW LINE WITH TAB
                $value = str_getcsv ( $value , ",", "\"" , "\\"); # SEQUENCING DATA IN CSV FORMAT, REQUIRED PHP >= 5.3.0
                $fields .= $value[0].','; # SEPARATING FIELDS WITH COMMA
            }
            $fields = substr_replace($fields, '', -1); # REMOVE EXTRA SPACE AT STRING END
        }

        header("Content-type: text/x-csv"); # DECLARING FILE TYPE
        header("Content-Transfer-Encoding: binary");
        header("Content-Disposition: attachment; filename=".$csv_file_name); # EXPORT GENERATED CSV FILE
        header("Pragma: no-cache");
        header("Expires: 0"); 
        header("Content-type: application/x-msdownload");
        //header("Content-Disposition: attachment; filename=data.csv");

        return $fields; 
    }

Answer 1

This is working perfectly now. we can use this as a plugin. I modified this post. thanks to sruthi sri.

Hope this helps some one :)

<?php

class CSVExport
{
/**
* Constructor
*/
public function __construct()
{
if(isset($_GET['download_report']))
{
$csv = $this->generate_csv();

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"report.csv\";" );
header("Content-Transfer-Encoding: binary");

echo $csv;
exit;
}

// Add extra menu items for admins
add_action('admin_menu', array($this, 'admin_menu'));

// Create end-points
add_filter('query_vars', array($this, 'query_vars'));
add_action('parse_request', array($this, 'parse_request'));
}

/**
* Add extra menu items for admins
*/
public function admin_menu()
{
add_menu_page('Download Report', 'Download Report', 'manage_options', 'download_report', array($this, 'download_report'));
}

/**
* Allow for custom query variables
*/
public function query_vars($query_vars)
{
$query_vars[] = 'download_report';
return $query_vars;
}

/**
* Parse the request
*/
public function parse_request(&$wp)
{
if(array_key_exists('download_report', $wp->query_vars))
{
$this->download_report();
exit;
}
}

/**
* Download report
*/
public function download_report()
{
echo '<div class="wrap">';
echo '<div id="icon-tools" class="icon32">
</div>';
echo '<h2>Download Report</h2>';
//$url = site_url();

echo '<p>Export the Subscribers';
}

/**
* Converting data to CSV
*/
public function generate_csv()
{
$csv_output = '';
$table = 'users';

$result = mysql_query("SHOW COLUMNS FROM ".$table."");

$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output = $csv_output . $row['Field'].",";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].",";
}
$csv_output .= "\n";
}

return $csv_output;
}
}

// Instantiate a singleton of this plugin
$csvExport = new CSVExport();

Answer 2

I'm a late bloomer, but made a small 'improvement' to the code you guys worked on and would like to share. If code pasted in the main plugin .php file you don't need to go through the 3 steps. Just change the values at the bottom of the script as required. I like to keep it neat though with plenty of comments for you guys.

For beginners who might need this and to add flexibility for everyone to use:

1. First add global variable define('MY_PLUGIN_DIR', plugin_dir_path(__FILE__));
2. After that add require_once(PARTS_MY_PLUGIN_DIR . '/databasestuff/table_to_csv.php')
3. Under your_plugin_directory/databasestuff/table_to_csv.php save the following class and change the last few lines as required.

4. Make adjustments to the last few lines

   class export_table_to_csv{
   
     private $db;
     private $table_name;
     private $separator;
   
   
     function __construct($table_n, $sep, $filename){
   
       global $wpdb;                                               //We gonna work with database aren't we?
       $this->db = $wpdb;                                          //Can't use global on it's own within a class so lets assign it to local object.
       $this->table_name = $table_n;                               
       $this->separator = $sep;
   
       $generatedDate = date('d-m-Y His');                         //Date will be part of file name. I dont like to see ...(23).csv downloaded
   
       $csvFile = $this->generate_csv();                           //Getting the text generated to download
       header("Pragma: public");
       header("Expires: 0");
       header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
       header("Cache-Control: private", false);                    //Forces the browser to download
       header("Content-Type: application/octet-stream");
       header("Content-Disposition: attachment; filename=\"" . $filename . " " . $generatedDate . ".csv\";" );
       header("Content-Transfer-Encoding: binary");
   
       echo $csvFile;                                              //Whatever is echoed here will be in the csv file
       exit;
   
     }
   
   
     function generate_csv(){
   
       $csv_output = '';                                           //Assigning the variable to store all future CSV file's data
       $table = $this->db->prefix . $this->table_name;             //For flexibility of the plugin and convenience, lets get the prefix
   
       $result = $this->db->get_results("SHOW COLUMNS FROM " . $table . "");   //Displays all COLUMN NAMES under 'Field' column in records returned
   
       if (count($result) > 0) {
   
           foreach($result as $row) {
               $csv_output = $csv_output . $row->Field . $this->separator;
           }
           $csv_output = substr($csv_output, 0, -1);               //Removing the last separator, because thats how CSVs work
   
       }
       $csv_output .= "\n";
   
       $values = $this->db->get_results("SELECT * FROM " . $table . "");       //This here
   
       foreach ($values as $rowr) {
           $fields = array_values((array) $rowr);                  //Getting rid of the keys and using numeric array to get values
           $csv_output .= implode($this->separator, $fields);      //Generating string with field separator
           $csv_output .= "\n";    //Yeah...
       }
   
       return $csv_output; //Back to constructor
   
     }
   }
   
   // Also include nonce check here - https://codex.wordpress.org/WordPress_Nonces
   if(isset($_POST['processed_values']) && $_POST['processed_values'] == 'download_csv'){  //When we must do this
     $exportCSV = new export_table_to_csv('table_name',';','report');              //Make your changes on these lines
   }
   

Keep in mind:

1. Table prefix will be added to the table name.
2. This script uses core WordPress functions meaning that the last 3 lines is literally all you have to change for this to work.