Howto: Create and Stream a CSV with PHP

CSVI 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.

PHP has some really handy functions for reading with CSV data, and fputcsv() is one of them. However, if you’re not creating a file you can’t use it, right? Actually you can, and the key 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!

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

  1. 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 >>>>>>>>>>>>>>

    1. 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. 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. 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;

    1. 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

      1. 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.

  4. Thanks for writing this, it worked great.

    One tip: if your first column is an “ID” and you use fputcsv() to output the title row such that the first characters are a capital “ID”, Excel will give you an error (something about an “SYLK file could not be opened”) when you try to open the file.

    You can easily solve the problem by using a lowercase “id” or by any other prefix like “Database ID”, etc.

    Thanks again!

  5. Hi Aaron,

    Thanks for the awesome code! Appreciate it.

    I’m trying to do something similar, but instead of “stream” the file, I want to upload it an FTP. Is there a way of doing this?

    Thanks in advance,

    Jay

  6. Hi Aaron,

    your example works… but how do you get it to play well with wordpress. My problem is that the user clicks a link and gets redirected to a page with your code, but wordpress has already started echo’ing a lot of stuff.

    any suggestion?

    1. For WordPress you can use something like admin-ajax or admin-post. Depending on your use case you could also add an endpoint rewrite or a query arg. No matter which of these you use, you need to hook in early enough and exit when you’re done so WP doesn’t continue to process.

  7. Hi Aaron
    I realise this was written a long time ago, but I am facing a problem that I don’t understand. I happen to be using cakePHP, but I think the problem I am having is PHP related. I apologize for the length of the question.

    I have a site where multiple datasets are available, and people can log on and then select variables from any of the datasets and then on submit the csv files for each of the datasets will be created with only the variables they have selected.

    The method loops through a well-formatted array of datasets (with the variables selected being listed). For each dataset it then sends the output to cakePHP view that is used to generate the csv output (each dataset filename will be different). At the end of the view the stream is closed (I thought!) with fclose.. Then control returns to the loop and the next dataset is sent… sounds lovely, but it does not work. Instead, it goes through the entire process and I end up with a single csv file that only contains the LAST dataset in the loop (with the appropriate filename) -any other csv files are simply not generated.

    I think this is a synchronous/asynchronous issue, but have not found a solution. Have you any thoughts on this?

    1. I’m honestly not sure if there’s a way to do multiple downloads in one script like that. I think you may need to make an asynchronous JS call to decide how many files are needed and then basically loop through those (in JS) and basically call the URL that generated the CSV X number of times, passing the parameters needed for each file. This would basically download them all one file at a time.

  8. Nice howto, but sometimes output buffering is enabled! In this case even large files will be cached in the memory and prevent your download stream. Simple add this row before your start the output:

    // clear previously cached output and disable the output buffer
    while (ob_get_level()) ob_end_clean();

Leave a Reply

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