I’m trying to export some data from a custom table to CSV. Ideally it would download the file immediately when clicking a button.
By searching the web I came up with a lot of pieces of code, but whatever I do, it doesn’t work. The main problem is: I get the desired output, but it just gets echoed within the admin area instead of creating a CSV file that can be downloaded.
I guess it is because my script runs inside WP admin and the WP headers get sent before the CSV header.
The only thing that comes to my mind now to prevent this: write the script in an external file and run this in a blank browser window. But then I’m in trouble getting WordPress user emails and names to be included (based on an ID from my custom table).
Right now I’m running this code in my custom plugin’s main file:
class export_table_to_csv{
private $db;
private $table_name;
private $separator;
function __construct($table_n, $sep, $filename, $eventid){
global $wpdb;
$this->db = $wpdb;
$this->table_name = $table_n;
$this->separator = $sep;
$this->eventid = $eventid;
$generatedDate = date('d-m-Y His');
$csvFile = $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/csv; charset=UTF-8');
header("Content-Disposition: attachment; filename="" . $filename . " " . $generatedDate . ".csv";" );
header("Content-Transfer-Encoding: binary");
header("Content-length: " . filesize($csvFile));
echo $csvFile;
exit;
}
function generate_csv(){
$csv_output = '';
$table = $this->db->prefix . $this->table_name;
$csv_output = $csv_output . "Name". $this->separator ."Email". $this->separator. "Value1". $this->separator ."Value2";
$csv_output .= "n";
$values = $this->db->get_results("SELECT userid,value1,value2 FROM " . $table . " WHERE eventid=".$this->eventid);
foreach ($values as $rowr) {
$fields = array_values((array) $rowr);
$UserID = $fields[0];
$V1 = $fields[1];
$V2 = $fields[2];
$user_info = get_userdata($UserID);
$first_name = $user_info->first_name;
$last_name = $user_info->last_name;
$Eml = $user_info->user_email;
$Fullname = $first_name." ".$last_name;
$csv_output = $csv_output.$Fullname.$this->separator.$Eml.$this->separator.$V1.$this->separator.$V2;
$csv_output .= "n";
}
return $csv_output;
}
}
EDIT:
Based on Nick’s code I tried another approach now.
Im calling the following as a separate file:
<?php
$EventID = $_REQUEST["event"];
$location = $_SERVER['DOCUMENT_ROOT'];
include ($location . '/wp/wp-config.php');
include ($location . '/wp/wp-load.php');
include ($location . '/wp/wp-includes/pluggable.php');
// Name for File
$Loc = get_post_meta($EventID, 'tloc', true);
$Dat = get_post_meta($EventID, 'tdat', true);
$y = substr($Dat, 2, 2);
$m = substr($Dat, 4, 2);
$d = substr($Dat, 6, 2);
$TheDate = $d.$m.$y;
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=Res_".$Loc."_".$TheDate.".csv");
header("Pragma: no-cache");
header("Expires: 0");
$file = fopen('php://output', 'w');
$csv_header .= "Name; Email; Value1; Value2";
$csv_header = "n";
fputcsv($file, $csv_header);
global $wpdb;
$csv_output = "";
$table_name = $wpdb->prefix . "nameoftable";
$Res = $wpdb->get_results("SELECT userid, value1, value2 FROM $table_name WHERE eventid=$EventID");
foreach ($Res as $rowr) {
$fields = array_values((array) $rowr);
$UserID = $fields[0];
$V1 = $fields[1];
$V2 = $fields[2];
$user_info = get_userdata($UserID);
$Eml = $user_info->user_email;
$first_name = $user_info->first_name;
$last_name = $user_info->last_name;
$Fullname = $first_name." ".$last_name;
$csv_output .= $Fullname;
$csv_output .= "; ";
$csv_output .= $Eml;
$csv_output .= "; ";
$csv_output .= $V1;
$csv_output .= "; ";
$csv_output .= $V2;
$csv_output .= "n";
fputcsv($file, $csv_output);
$csv_output = "";
}
exit();
?>
Now, a correctly named CSV file gets downloaded, but it is empty. If I delete the header stuff and use “echo” instead of “fputcsv” the data is there. What am I doing wrong?
I think you do not need to echo csvfile. I had similar approach for generating user list as csv and here is a code which works.The thing is that I have it on a separate php file and the link on dashboard is pointing that separate php file. When clicked, it generates a csv and download starts. You could also create custom page and then in functions.php or somewhere else, write this functions and pre-check if is_page(‘target_page’) then run this function and then redirect back to where you are.