Howto: Create and Stream a CSV with PHP
I find myself constantly creating csv files for users to download. Whether it’s a method for users to export their data to excel, a way for users to backup their hosted data, or just a simple way to send them report information, csv files are extremely useful. Normally I just create an actual file and link to it for the user to download. The files are usually cleaned up after a certain amount of time or after a certain number of newer files exist.
Recently however, I had a client that really wanted to be able to export data in csv format without ever creating a file on the webserver. Their concern was rooted in security, but the reality of the matter was that they were trying to obey the letter of the law with regards to company policies. Whether it was truly necessary or not is for another discussion. Instead, the technique is actually very useful so I thought I’d share.
The key to this is that the fopen function supports php input/output streams as wrappers.
Start by sending the headers to allow the user to download the csv file, then open a stream:
$fileName = 'somefile.csv';
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header('Content-Description: File Transfer');
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename={$fileName}");
header("Expires: 0");
header("Pragma: public");
$fh = @fopen( 'php://output', 'w' );
At this point, anything you send to the newly opened stream ends up in the downloaded csv file. I did this in WordPress, but you can pull your data from anywhere, just pass an array to fputcsv(). In this case, $data is an associative array with the field name as the key and the field value as the value.
global $wpdb;
$query = "SELECT * FROM `{$wpdb->prefix}my_table`";
$results = $wpdb->get_results( $query, ARRAY_A );
$headerDisplayed = false;
foreach ( $results as $data ) {
// Add a header row if it hasn't been added yet
if ( !$headerDisplayed ) {
// Use the keys from $data as the titles
fputcsv($fh, array_keys($data));
$headerDisplayed = true;
}
// Put the data into the stream
fputcsv($fh, $data);
}
// Close the file
fclose($fh);
// Make sure nothing else is sent, our file is done
exit;
As you can see, the actual code is quite simple. Here it is all put together:
$fileName = 'somefile.csv';
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header('Content-Description: File Transfer');
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename={$fileName}");
header("Expires: 0");
header("Pragma: public");
$fh = @fopen( 'php://output', 'w' );
global $wpdb;
$query = "SELECT * FROM `{$wpdb->prefix}my_table`";
$results = $wpdb->get_results( $query, ARRAY_A );
$headerDisplayed = false;
foreach ( $results as $data ) {
// Add a header row if it hasn't been added yet
if ( !$headerDisplayed ) {
// Use the keys from $data as the titles
fputcsv($fh, array_keys($data));
$headerDisplayed = true;
}
// Put the data into the stream
fputcsv($fh, $data);
}
// Close the file
fclose($fh);
// Make sure nothing else is sent, our file is done
exit;
I hope you find it useful!
Related Posts:
Aaron is the man behind the curtain here at Xavisys. He gets things done and really knows his way around the code.
the problem is that if two process , try to write to the same file that’s (somefile.csv) at the same time will not be so good huh? there should be a possibility to stream arrays to a csv structure just for downloading other wise you just got >>>>>>>>>>>>>>
I don’t think you understand what’s happening then. The filename (somefile.csv) is only being sent in the header so that it is offered as the suggested name for the user who’s downloading it to save it as. The whole point of this streaming is that it’s not actually writing this data anywhere on the server.