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.

Fun

Think about what will happen if two people hit that route at the exact same time... – Convolve

I don't know? I'm guessing something bad – Fun

See my answer about what issues your current code has. – Convolve

Answer 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);

Gaming

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. – Convolve

@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 – Boohoo

@Convolve 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. – Anette

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. – Ireneirenic

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

@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? – Boohoo

Answer 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)
}

Studdard

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

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

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

Where are you putting this function? – Laughton

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

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 – Pyotr

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. – Cufic

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 – Hawkweed

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

@Studdard 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. – Eustace

Answer 3

EDIT: see this answer for a better solution; I'll keep my answer below but note that it has issues like not escaping values and using unreasonable amounts of memory if generating large files.

You're unnecessarily creating a file on the disk; that induces disk IO and will cause issues if two people request that URL at the exact same time (two instances of the framework will write to that same file and bad stuff will happen such as serving a corrupted file or crashing with an exception).

Use this instead :

Route::get('/all-tweets-csv', function() {
    $tweets = Tweets::all();

    // the csv file with the first row
    $output = implode(",", array('tweet text', 'screen name', 'name', 'created at'));

    foreach ($tweets as $row) {
        // iterate over each tweet and add it to the csv
        $output .=  implode(",", array($row['tweet_text'], $row['screen_name'], $row['name'], $row['created_at'])); // append each row
    }

    // headers used to make the file "downloadable", we set them manually
    // since we can't use Laravel's Response::download() function
    $headers = array(
        'Content-Type' => 'text/csv',
        'Content-Disposition' => 'attachment; filename="tweets.csv"',
        );

    // our response, this will be equivalent to your download() but
    // without using a local file
    return Response::make(rtrim($output, "\n"), 200, $headers);
});

Convolve

Be sure to see Erik's answer below in regards to this not properly escaping values which could lead to improperly formatted CSVs very quickly... as well as memory issues if dealing with large files. – Tannen

@Tannen thanks for bringing that up; I edited my answer and added a warning at the top and a link to the better answer. – Convolve

Answer 4

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();
}

Fai

Answer 5

I was following along with these examples and none of them were working for my for my code. I am attaching the code I wrote for my project as an example. This does not address the OP, just as an example guide. My data is in the form of an array of objects

public function Export($data) {
    $headers = array(
        "Content-type" => "text/csv",
        "Content-Disposition" => "attachment; filename=summary.csv",
        "Pragma" => "no-cache",
        "Cache-Control" => "must-revalidate, post-check=0, pre-check=0",
        "Expires" => "0"
    );
    $columns = array('Period', 'RevenueArea', 'Subs');

    $callback = function() use ($data, $columns)
    {
        $file = fopen('php://output', 'w');
        fputcsv($file, $columns);

        foreach($data as $items) {
            fputcsv($file, array($items->PERIOD, $items->REVENUE_AREA, $items->SUBS));

        }
        fclose($file);
    };
return Response::stream($callback, 200, $headers)->send();
}

If you noticed on my return statement I am including ->send() which generates the csv file and initializes the download. Where as ->sendContent(); just dumps the date to the screen. If you stumble across this post with the same problem I was having of the file not downloading, make sure to checkout the official documentation.

https://api.symfony.com/2.3/Symfony/Component/HttpFoundation/StreamedResponse.html

Configurationism

Thanks @BOB , your answer was correct and working... i do not know how people given Up-Vote on all above stupid answer..... thanks your contributaion ... :-) – Tarryn

This worked for me as well. +1 – Crozier

Answer 6

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);

Kythera

Answer 7

Try This

public function exportCsv(Request $request)
{
   $fileName = 'tasks.csv';
   $tasks = Task::all();
$headers = array(
            "Content-type"        => "text/csv",
            "Content-Disposition" => "attachment; filename=$fileName",
            "Pragma"              => "no-cache",
            "Cache-Control"       => "must-revalidate, post-check=0, pre-check=0",
            "Expires"             => "0"
        );

        $columns = array('Title', 'Assign', 'Description', 'Start Date', 'Due Date');

        $callback = function() use($tasks, $columns) {
            $file = fopen('php://output', 'w');
            fputcsv($file, $columns);

            foreach ($tasks as $task) {
                $row['Title']  = $task->title;
                $row['Assign']    = $task->assign->name;
                $row['Description']    = $task->description;
                $row['Start Date']  = $task->start_at;
                $row['Due Date']  = $task->end_at;

                fputcsv($file, array($row['Title'], $row['Assign'], $row['Description'], $row['Start Date'], $row['Due Date']));
            }

            fclose($file);
        };

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

Gertudegerty

Answer 8

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'

Wyn

Answer 9

I faced the same issue and none of the solutions worked for me. So I found here https://mcmap.net/q/332749/-use-laravel-to-download-table-as-csv that I need to add send() to stream.

I change this return Response::stream($callback, 200, $headers);

Into this return Response::stream($callback, 200, $headers)->send();

Working with Laravel and Inertia?

Ensure that you are not sending Inertia or VueJs/ReactJs requests, you need to send a simple HTTP get or post request.

Maybe you want to use anchor tag <a target="_blank" :href="route('abc', { params: form }"> Export </a>

Crozier

Answer 10

try this

 $file_name = "abc";
    $postStudent = Input::all();
    $ck = DB::table('loan_tags')->select('LAN')->where('liabilitiesId', $postStudent['id'])->get();
    $i = 0;
    foreach ($ck as $row) { 

                  $apps[$i]['LAN'] = $row->LAN;
                $apps[$i]['Account_number'] =   $postStudent['account_number'];
                $apps[$i]['Bank_Name'] =  $postStudent['bank_name'];
                $i++;
    }

    ob_end_clean();
    ob_start();
    Excel::create($file_name, function($excel) use($apps){
            $excel->sheet('Sheetname', function($sheet) use($apps){

               $sheet->row(1, array(
                     'LAN', 'Account number' , 'Bank Name'
                ));
                $k = 2;
                 foreach ($apps as $deta) {
                     $sheet->row($k, array($deta['LAN'],   $deta['Account_number'], $deta['Bank_Name']
                    ));
                    $k++;
                 }
            });
        })->download('xlsx');

Hoopes

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