How to process large CSV files with Laravel

Lokman Musliu Founder and CEO of Lucky Media

December 19, 2023 · 3 min read

How to process large CSV files with Laravel

Importing Large CSV files in Laravel

Handling big CSV files is pretty standard in the business world, especially when you have a lot of data to analyze, report on, or move around. If you’re using Laravel and need to process big CSV files, you’re in the right place. We’ll show you the best way to do this without slowing down your app.

Memory and performance

First off, let’s talk about the elephant in the room: memory and performance. Going through a big CSV can use a lot of memory and can slow down your app. You might think about increasing the memory limit or making the timeout longer. But that’s not the best fix.

Using Simple Excel by Spatie

Instead of the band-aid approach, we’re going to use a package called Simple Excel by Spatie. If you’re nodding because you expected Spatie to have a solution, you’re not alone.

composer require spatie/simple-excel

Assuming your CSV file is ready, we’ll use SimpleExcelReader to open it. By default, it returns a LazyCollection, which is a better way to handle your data without using too much server memory. This means you can process the file little by little, so your app stays fast.

$rows is an instance of Illuminate\Support\LazyCollection

Using Laravel Jobs

Now, before we dive into code, let’s set up a Laravel Job to manage our CSV processing.

php artisan make:job ImportCsv

Now here is what our ImportCsv job looks like:

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Spatie\SimpleExcel\SimpleExcelReader;

class ImportCsv implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    /**
     * Create a new job instance.
     */
    public function __construct()
    {
        //
    }

    /**
     * Execute the job.
     */
    public function handle(): void
    {
        SimpleExcelReader::create(storage_path('app/public/products.csv'))
            ->useDelimiter(',')
            ->useHeaders(['ID', 'title', 'description'])
            ->getRows()
            ->chunk(5000)
            ->each(
				// Here we have a chunk of 5000 products
			);
    }
}

Here’s the game plan:

Chunking the CSV: We’re going to break that file into manageable pieces, giving us a LazyCollection to play with.
Job Dispatching: For each chunk, we’ll send out a job. This way, we’re processing in batches, which is way easier on your server.
Database Insertion: Each chunk will then be inserted into the database, nice and easy.

Chunking the CSV

With our LazyCollection ready, we’ll slice the CSV into chunks. It’s like cutting a big sandwich into small pieces – easier to manage.

php artisan make:job ImportProductChunk

For every piece of the CSV, we’ll create and fire off a job. These jobs are like hardworking workers, each taking a chunk and carefully inserting the data into your database.

namespace App\Jobs;

use App\Models\Product;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldBeUnique;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Str;

class ImportProductChunk implements ShouldBeUnique, ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public $uniqueFor = 3600;

    /**
     * Create a new job instance.
     */
    public function __construct(
		public $chunk
	) {
        //
    }

    /**
     * Execute the job.
     */
    public function handle(): void
    {
        $this->chunk->each(function (array $row) {
            Model::withoutTimestamps(fn () => Product::updateOrCreate([
                'product_id' => $row['ID'],
				'title' => $row['title'],
				'description' => $row['description'],
           ]));
        });
    }

    public function uniqueId(): string
    {
        return Str::uuid()->toString();
    }
}

Ensuring Uniqueness

Remember to use $uniqueFor and uniqueId in your jobs. It’s like giving each worker a unique ID, so you don’t have two people doing the same job.

Dispatching Jobs

Back in our ImportCsv job, we’ll dispatch a job for each chunk within the each method. It's like saying, "You get a chunk, and you get a chunk – everybody gets a chunk!"

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Spatie\SimpleExcel\SimpleExcelReader;

class ImportCsv implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    /**
     * Create a new job instance.
     */
    public function __construct()
    {
        //
    }

    /**
     * Execute the job.
     */
    public function handle(): void
    {
        SimpleExcelReader::create(storage_path('app/public/products.csv'))
            ->useDelimiter(',')
            ->useHeaders(['ID', 'title', 'description'])
            ->getRows()
            ->chunk(5000)
            ->each(
				fn ($chunk) => ImportProductChunk::dispatch($chunk)
			);
    }
}

Your chunks are ready to be processed separately, without any memory issues. If you’re in a hurry, just add more workers, and your data will be handled even faster.

Conclusion

Processing large CSV files in Laravel doesn’t have to be a headache. With the right tools and approach, you can keep your application running smoothly while dealing with all that data.