Use Laravel to Download table as CSV

Question

I am trying to export a database table using Laravel as a csv file. I would like the user to be able to select the Export as CSV button and download the table as a csv file. Currently I've gotten this code but It is not working:

my button:

<a href="/all-tweets-csv" class="btn btn-primary">Export as CSV</a>

my route:

Route::get('/all-tweets-csv', function(){

    $table = Tweet::all();
    $filename = "tweets.csv";
    $handle = fopen($filename, 'w+');
    fputcsv($handle, array('tweet text', 'screen name', 'name', 'created at'));

    foreach($table as $row) {
        fputcsv($handle, array($row['tweet_text'], $row['screen_name'], $row['name'], $row['created_at']));
    }

    fclose($handle);

    $headers = array(
        'Content-Type' => 'text/csv',
    );

    return Response::download($handle, 'tweets.csv', $headers);
});

It returns me this error:

 The file "Resource id #154" does not exist

And I've gathered that it is because it is trying to download a file that does not exist. Is there an alternative way I can go about modifying my code in order to download as a csv.

Solution 1

Almost everything is fine except this line:

return Response::download($handle, 'tweets.csv', $headers);

You should change this line into:

return Response::download($filename, 'tweets.csv', $headers);

Solution 2

I stumbled in here trying to see if Laravel had something built in by default - the answers for this question worry me a bit. I agree with @andré-daniel that the proper method is to not write a file first, but his implementation is manually putting together the values, which would fail if any value contained quotes, spaces, etc.

This is a more robust solution, using Laravel's Response::stream and php's fputcsv to format each line properly (will escape quotes, and quote necessary strings. see http://php.net/manual/en/function.fputcsv.php for details)

<?php

public function download()
{
    $headers = [
            'Cache-Control'       => 'must-revalidate, post-check=0, pre-check=0'
        ,   'Content-type'        => 'text/csv'
        ,   'Content-Disposition' => 'attachment; filename=galleries.csv'
        ,   'Expires'             => '0'
        ,   'Pragma'              => 'public'
    ];

    $list = User::all()->toArray();

    # add headers for each column in the CSV download
    array_unshift($list, array_keys($list[0]));

   $callback = function() use ($list) 
    {
        $FH = fopen('php://output', 'w');
        foreach ($list as $row) { 
            fputcsv($FH, $row);
        }
        fclose($FH);
    };

    return response()->stream($callback, 200, $headers)
}

Solution 3

Considering the current highest ranked answer this is the Laravel 5.7 CSV write, note the return changes.

<?php

public function download()
{
    $headers = [
            'Cache-Control'       => 'must-revalidate, post-check=0, pre-check=0'
        ,   'Content-type'        => 'text/csv'
        ,   'Content-Disposition' => 'attachment; filename=galleries.csv'
        ,   'Expires'             => '0'
        ,   'Pragma'              => 'public'
    ];

    $list = User::all()->toArray();

    # add headers for each column in the CSV download
    array_unshift($list, array_keys($list[0]));

   $callback = function() use ($list) {
        $FH = fopen('php://output', 'w');
        foreach ($list as $row) { 
            fputcsv($FH, $row);
        }
        fclose($FH);
    };

    return (new StreamedResponse($callback, 200, $headers))->sendContent();
}

Solution 4

Here is the complete code to download CSV

 $headers = array(
        'Content-Type' => 'application/vnd.ms-excel; charset=utf-8',
        'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
        'Content-Disposition' => 'attachment; filename=abc.csv',
        'Expires' => '0',
        'Pragma' => 'public',
    );

$filename = "doenload.csv";
$handle = fopen($filename, 'w');
fputcsv($handle, [
    "id",
    "name"
]);

DB::table("tablename")->chunk(100, function ($data) use ($handle) {
    foreach ($data as $row) {
        // Add a new row with data
        fputcsv($handle, [
            $row->id,
            $row->name
        ]);
    }
});

fclose($handle);

return Response::download($filename, "download.csv", $headers);

Solution 5

Everything looks good except this line:

return Response::download($handle, 'tweets.csv', $headers);

$handle does not point to the correct file path. It should be the full path to tweets.csv, for example:

return Response::download($file, 'tweets.csv', $headers);

where $file should be something like $file = '/path/to/download/tweets.csv'

View more solutions

Comments

[Admin]

Your answer works but the author's approach to the problem is bad; I suggest creating the csv in memory and serving it from there rather than creating a file and inducing unnecessary disk IO + possible issues if two people hit that route at the exact same time.

[Marcin Nabiałek]

@AndréDaniel, you are right, +1 for that but we don't know what exactly happens with the code (this code could be just a sample) and solution just for download file properly is as I showed

[Dan H]

Just noticed there are two Content-type headers. The correct one is text/csv, according to RFC 4180.

[Timo002]

I think this is a better solution. This does not leave a 'tweets.csv' file public on your server!

[DominikAngerer]

->toArray() wasn't necessary in laravel 5 for me - it even broke the foreach for some reason - removed it - everything works perfectly fine ;)

[Kerry Jones]

Where are you putting this function?

[Bill Garrison]

mine is saying there is no method "Stream" on the response object

[Benjamin Piette]

Thanks! And I personally like to add dates to file name, like: $filename = 'journeys-results-'.date('Y-m-j-Hi').'.csv'; - then: 'Content-Disposition' => 'attachment; filename='.$filename

[Pnar Sbi Wer]

@user2629998 creating the csv in memory would limit the csv size to the memory available. Don't think we could/should make the assumption that the csv file will be small. he could always generate a random file name every time someone hits the route to prevent problems with simultaneous calls.

[kapad]

This no longer works.. now you can either use the response helper.. like response()->stream($callback, 200, $headers) or directly use the symfony StreamedResponse class.

[Maurizio Brioschi]

this approch doesn't work because you should give write permission to the root directory and this is not acceptable. As "/" is a asci char, you can not set a full path.

[Maurizio Brioschi]

you should use an approch based in a response stream: return Response::stream($callback, 200, $headers);

[Marcin Nabiałek]

@MaurizioBrioschi Why should I? I was replying to OP problem and not answering what's the best way to do it. You also see this question is from 2014 and Laravel 4, right?

[Spialdor]

Hi, i'm trying to use this method for my angular app who call a Laravel api, but it return me an undefined reponse (in the network tab of chrome I see the values of the csv) but it doesn't download the file

[Dhawal M]

This works fine locally. But on the server I keep getting this error: "The content cannot be set on a StreamedResponse instance." any idea?

[pmiranda]

At least say that your answer is using a 3rd party library

[iDon'tKnoware]

@Erik solution works, but if you gonna use it you gonna need to verify and control what happens if the query get nothing, because in $list[0] will fail and return a 500 to the user because an Undefined offset in the array.