★ Streaming a large export as a CSV file to the browser
Tags: laravel, php, performance
Published: 2020-01-02 23:43:48

---
title: ★ Streaming a large export as a CSV file to the browser
published: true
date: 2019-11-29 13:00:00 UTC
tags: laravel, php, performance
canonical_url: https://freek.dev/1515-streaming-a-large-export-as-a-csv-file-to-the-browser
---

A while ago, I created an easy to use framework agnostic PHP package to read and write CSV and Excel files called [spatie/simple-excel](https://github.com/spatie/simple-excel). Behind the scenes, generators are used to ensure low memory usage, even when working with large files. Today I added a method that allows you to stream CSV files to the browser. In this small blog post, I'd like to demonstrate how you can use it.

## The problem #

I'm currently building [Mailcoach](https://mailcoach.app), an app to self-host your newsletters. In Mailcoach, people can subscribe to email lists. These lists can potentially contain a large number of subscribers. In the UI of Mailcoach, there is a button to download a CSV containing all subscribers.

![Screenshot](https://freek.dev/uploads/media/stream-csv/download.png)

Here's how you could implement the download of that export.

```php
class SubscribersExportController
{
    public function __invoke()
    {
        $exportFile = storage_path('exports/subscribers.csv');

        $exportCsv = SimpleExcelWriter::create($exportFile);

        Subscriber::each(function (Subscriber $subscriber) use ($exportCsv) {
            $exportCsv->addRow($subscriber->toArray());
        });

        return response()->download($exportFile)->deleteFileAfterSend();
    }
}
```

That `each` static method on a model will, behind the scenes, retrieved all the models in a chunked way. This will keep memory usage low.

The code above does the job, but it has two significant drawbacks that become apparent when you want to export a great many rows.

1. The code above writes a file on disk. For large lists, this export file could take could some disk space.
2. The download will only start after the entire export file has been created. For large exports, the user must potentially wait a long time.

## The solution #

Both of these problems can be solved with the new capabilities of [spatie/simple-excel](https://github.com/spatie/simple-excel).

Using `streamDownload` and `toBrowser`, you can stream the export to the browser while you're fetching results from the database.

```php
class SubscribersExportController
{
    public function __invoke()
    {
        $subscriberCsv = SimpleExcelWriter::streamDownload('subscribers.csv');

        Subscriber::each(function (Subscriber $subscriber) use ($subscriberCsv) {
            $subscriberCsv->addRow($subscriber->toArray());
        });

        $subscriberCsv->toBrowser();
    }
}
```

We've solved the two problems with the previous code. No file will be created on disk. The download will immediately start, even if there is a large number of subscribers.

This solution will only work for CSV files. Excel files are not streamable.

## In conclusion #

Take a look at [spatie/simple-excel on GitHub](https://github.com/spatie/simple-excel) to know more about the package.

Together with Mailcoach, we'll release a video course on how Mailcoach is built. You'll learn a lot of useful things, similar to what you read in this post, to improve your code. To be notified when Mailcoach and the video course launch, subscribe to the mailing list at [Mailcoach .app](https://mailcoach.app).