Export Query to compressed CSV file in laravel

Exporting your database records or a calculated array of data to a CSV file is a very common use case. Here in this article, I'm trying to give you a utility class for achieving this.

What concerns this article covers:

Get data from both chunked queries and arrays
Reduce the size of the output file by compressing it
Be ready for concurrent multiple requests
Delete old files in the folder

Now, let's get our hands dirty.

A utility class for exporting

I going to define a new Utilities namespace in my app folder and add a class of ExportToCSV into it. I expect this class to do two main jobs, One is exporting CSV from a query, and two is from an array of data. I also mentioned that it chunks the query to make it optimized in favor of our memory usage. You can learn more about chunking from this link.

So, our ExportToCSV class is something like below with two public functions:

<?php

namespace App\Utilities;

use Illuminate\Contracts\Database\Query\Builder;

class ExportToCSV
{
    public function exportFromQuery(Builder $query)
    {
        //
    }

    public function exportFromArrayData(array $data)
    {
        //
    }
}

Okay, first things first, let's create a method for query export.

Exporting to CSV file

After creating exportFromQuery() with one $query parameter, let's constrain it to be an instance of Illuminate\Contracts\Database\Query\Builder, this helps us to make sure that it has the chunk() method in it.
So, we need to open a CSV file and start iterating over our data for filling up the file. But where do we open it? Storage is needed and I will add a property for it in our class.

Also, we must specify the header for the CSV file. So, there is another property for our headers.

use Illuminate\Contracts\Filesystem\Filesystem;

class ExportToCSV
{
    protected Filesystem $storage;
    protected mixed $file;
    public array $headers;
    // ...

    public function exportFromQuery(Builder $query, array $headers)
    {
        $this->query = $query;
        $this->headers = $headers;

        $this->storage = $this->storage ?? app('filesystem')->disk(config('filesystems.default'));

        // Write to csv file in append mode(a+)
        $this->file = fopen($this->storage->path($this->fileName), 'a+');
        fputcsv($this->file, $this->header);

        $this->query->chunk($this->chunkSize, function($iterableData){
            foreach ($iterableData as $row) {
                $toArray = json_decode(json_encode($row), true);
                $res     = [];
                foreach ($this->headers as $key => $value) {
                    $res[$key] = data_get($toArray, $key);
                }
                $res = array_filter($res, fn ($item) => !is_array($item));

                fputcsv($this->file, $res);
            }
        });

        fclose($this->file);

        return $this->compressFilesAndGetDownloadStream();
    }
}

The same strategy is applicable for exporting from an array of data, we need to just change the query with a foreach over the requested array.

class ExportToCSV
{
    protected Filesystem $storage;
    protected mixed $file;
    public array $headers;
    // ...

    public function exportFromArrayData(?array $data = [], array $headers)
    {
        $this->data = $data;
        $this->headers = $headers;

        $this->storage = $this->storage ?? app('filesystem')->disk('default');

        // Write to csv file in append mode(a+)
        $this->file = fopen($this->storage->path($this->fileName), 'a+');
        fputcsv($this->file, $this->header);

        foreach ($this->data as $row) {
            $toArray = json_decode(json_encode($row), true);
            $res     = [];
            foreach ($this->headers as $key => $value) {
                $res[$key] = data_get($toArray, $key);
            }
            $res = array_filter($res, fn ($item) => !is_array($item));

            fputcsv($this->file, $res);
        }

        fclose($this->file);

        return $this->compressFilesAndGetDownloadStream();
    }
}

Compressing the output file(s)

Well, by now we have our CSV file with all of the records in it, next step is to compress it which refers to the compressFilesAndGetDownloadStream() method at the end of the above codes. For compressing, we can use the PHP ZipArchive class which you can learn about it from here.

Below we add compressFilesAndGetDownloadStream method to our class.

use Symfony\Component\HttpFoundation\StreamedResponse;
use ZipArchive;
// ...

class ExportToCSV
{
    // ...

    public function compressFilesAndGetDownloadStream()
    {
        $zip = new ZipArchive();
        $zip->open($this->storage->path($this->zipFileName), ZipArchive::CREATE | ZipArchive::OVERWRITE);
        foreach ($this->storage->allFiles() as $filePath) {
            if (strpos('.zip', $filePath)) continue;
            $zip->addFile($this->storage->path($filePath), $filePath);
        }
        $zip->close();

        return $this->storage->download($this->zipFileName);
    }
}

Being ready for multiple requests

Until now, we have covered our two main concerns of exporting from query or data array and compressing the result. It's time to take care of multiple concurrent requests for exporting data and deleting old generated files.

When it comes to speaking of multiple requests, there is a need for separating generated files. We can separate files of each request to a folder with a random name, I call it process ID and after compressing that folder, we can delete it. As we are speaking of concurrency, we should be careful about picking different names at the exactly same time. Using uuid() helps us to prevent duplication in naming files/folders. Refer to here and here

So, let's add a processId property to our class and initialize it in the constructor.

use Illuminate\Support\Str;

class ExportToCSV
{
    protected string $processId;

    public function compressFilesAndGetDownloadStream()
    {
        $this->processId = Str::uuid()->toString();
    }
}

And create a directory for this process before opening the CSV file.

// ...

$this->storage->makeDirectory($this->processId);
// Write to csv file in append mode(a+)
$this->file = fopen($this->storage->path("$this->processId/$this->fileName"), 'a+');

// ...

We also need to make sure that only files in this process's folder are going to be compressed. So, let's update compressing section by adding folder name and removing folder name from the file name by substr($filePath, strlen($this->processId) + 1).

$zip = new ZipArchive();
$zip->open($this->storage->path($this->zipFileName), ZipArchive::CREATE | ZipArchive::OVERWRITE);
foreach ($this->storage->allFiles($this->processId) as $filePath) {
    if (strpos('.zip', $filePath)) continue;
    $zip->addFile($this->storage->path($filePath), substr($filePath, strlen($this->processId) + 1));
}
$zip->close();

After compressing, we need to delete the folder by $this->storage->deleteDirectory($this->processId);

Deleting old files

Well, now it's time to delete old files in our target storage. For this purpose, I added a method that calculates the last modified time of each file and compares it to now. After that, if the modified time is greater than a specified day, it will delete it from the storage.

The oldFilesDueDays property can be configured according to the customer's need and we also have an option here for skipping deletion if the oldFilesDueDays was set to -1.

protected function deleteOldZipFiles()
{
    // Delete old files that longer than x day
    // For not deleting old files, -1 can be set to $oldFilesDueDays
    if ($this->oldFilesDueDays === -1) return;

    foreach ($this->storage->allFiles() as $old_file) {
        $fileCreatedAtDaysDiff = Carbon::now()->diffInDays(Carbon::parse($this->storage->lastModified($old_file)));
        if ($fileCreatedAtDaysDiff > abs($this->oldFilesDueDays)) {
            $this->storage->delete($old_file);
        }
    }
}

The method of deleteOldZipFiles() can be called before or after file compression.

Wrap up

We have covered exporting from a chunked query or a data array into a CSV file. Plus compressing it and deleting the process folder and old files.

The final code of this attempt is as below. I've glued different parts and refactored it to be more readable and customizable.

Blog