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!

About Aaron D. Campbell

Owner and lead developer at BlueDog, Aaron has 10+ years of web development experience, it a regular core contributor to the WordPress project, and has released many WordPress plugins.
This entry was posted in PHP and tagged , , . Bookmark the permalink.

8 thoughts on “Howto: Create and Stream a CSV with PHP

  1. spe says:

    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.

  2. Sangeeth says:

    Thanks for this article! It was very useful.

    However I’m facing a small issue. I’m getting an extra blank line when I open the .csv file in excel. Any idea what this could be and how to remove it?

    I also modified the script to create a .txt file. But here too an extra character is coming.

  3. Sangeeth says:

    Thanks for the quick response, Aaron!

    Here’s my code below.

    header(“Cache-Control: must-revalidate, post-check=0, pre-check=0”);
    header(‘Content-Description: File Transfer’);
    header(“Content-type: text/csv”);
    $fileName = ‘somefile.csv’;
    header(“Content-Disposition: attachment; filename={$fileName}”);
    header(“Expires: 0”);
    header(“Content-Transfer-Encoding: binary”);
    header(“Pragma: public”);

    $dataString = “Start of the stringrnrn”;
    $dataString .= “Concatenate rest of the string”;

    $fh = @fopen( ‘php://output’, ‘w’ );
    fwrite($fh, $dataString);

    fclose($fh);

    exit;

    • Manish says:

      Hello there,

      The white space is causing this kind of problem. Try to print this array on the browser, you’ll see some extra space. This is the culprit.

      This extra space is coming from any of your include file. When you start writing the code, do not leave any space on top of the code.

      I was having the same problem & got this fixed by just removing extra space from one of my included file.

      Manish

      • Lem says:

        Thanks Manish, I would never have noticed that if you hadn’t mentioned it. I had a space before the opening php tag in my csv script, which was being transferred into the output file, resulting in a space before the first cell in the CSV.

Leave a Reply

Your email address will not be published. Required fields are marked *

Note: If you are replying to another commenter, click the "Reply to {NAME} ↵" button under their comment!