Summary

The text provides a detailed guide on implementing queued imports for large Excel files using Laravel-Excel, specifically focusing on how to process large imports asynchronously in the background to prevent PHP timeouts and enhance user experience.

Abstract

The article "Laravel-Excel — P8: Queued Import" discusses the challenge of importing large Excel files into a Laravel application without causing PHP timeouts or making users wait for the import to complete. It explains how to combine batch inserting and chunk reading to manage memory usage efficiently. The core of the solution lies in leveraging Laravel's queue system with the WithChunkReading and ShouldQueue concerns to handle the import process in the background. The author guides the reader through configuring the Laravel queue, running necessary Artisan commands, and monitoring the import process through queue workers. The article also touches on the importance of explicitly calling queued imports and the need to restart the queue after modifying the import class. The guide is concluded with a brief introduction to the author's professional background and a call to action for readers to connect on various platforms.

Opinions

The author emphasizes the importance of using queues for handling large file imports to avoid PHP timeouts and provide a better user experience.
Queued imports are presented as a solution that not only addresses performance issues but also allows developers to work within the Laravel framework comfortably using concerns like WithChunkReading and ShouldQueue.
The author suggests that managing large imports is straightforward when using Laravel-Excel, provided that the Laravel application is correctly configured to utilize queues.
It is implied that developers should be familiar with Laravel's command-line interface (Artisan) to effectively use queued imports, as it involves running commands like php artisan queue:table and php artisan migrate.
The article conveys a sense of simplicity and elegance in the way Laravel-Excel and Laravel's queue system integrate to solve a common problem faced by developers dealing with large datasets.

Laravel-Excel — P8: Queued Import

The time has come to put it all together. We’ve slowly built on in the previous articles and have increased our speed. With batch inserting we were able to insert a batch of 100 rows (even though you can pick any number). With chunk reading, we eliminated the memory problem of loading the entire excel sheet into memory and instead loaded a 100 rows worth of data (even though again we could have increased or decreased that number to whatever we wanted).

But there’s still one more issue. What if the file is so large that it times PHP out? How do you import a file that takes 5 minutes to import? Even if PHP didn’t time out, would you want to make your user wait while the import was running? Of course not. That’s where queued imports shine.

How to Import with Queue?

First thing to note is that queued imports only work with chunk reading. That means that the WithChunkReading concern must be present. After chunk reading is implemented into the import, the only other concern left to implement is ShouldQueue. Could it be that simple? Yes.

Let’s create our UserController method and add our route and test. I’ll spoil it slightly, it won’t work right away unless you have something running.

Let’s call the route and see what happens. Well, it worked. The import was a success but it didn’t get sent to the queue and do it in the background. It just imported like everything else so far. You can see that the user was locked on the screen until the import finished.

Why didn’t the queue import work?

If it didn’t work, it’s because of the way that you have Laravel configured. Open your .env file and you’ll notice that the QUEUE_CONNECTION is set to sync. We need to prepare Laravel to be able to use queues. Let’s modify it so that we can use queues. Run the following commands:

php artisan queue:table

This generates the jobs migration where jobs will be inserted.

php artisan migrate

Creates the jobs table. All that’s left is to modify the QUEUE_CONNECTION to database and clear the config. Open your .env file and change the QUEUE_CONNECTION from sync to database.

QUEUE_CONNECTION=database

Finally clear the config cache.

php artisan config:clear

Let’s try our example again. Do you think it’s going to work?

Well, it was lightning fast this time. It looked like the job was created but nothing happened. Where’s the import? One last step. We need to make sure that our queue is working.

php artisan queue:work

Cool! We even get an awesome chunk message display when each chunk is complete and how long it took the queue to import it.

2022-10-08 18:10:58 Maatwebsite\Excel\Jobs\QueueImport ....................... 39.51ms DONE

2022-10-08 18:10:58 Maatwebsite\Excel\Jobs\ReadChunk ....................... 989.82ms DONE

2022-10-08 18:10:59 Maatwebsite\Excel\Jobs\ReadChunk ....................... 916.27ms DONE

2022-10-08 18:11:00 Maatwebsite\Excel\Jobs\ReadChunk ....................... 920.93ms DONE

2022-10-08 18:11:01 Maatwebsite\Excel\Jobs\ReadChunk ....................... 920.62ms DONE

2022-10-08 18:11:02 Maatwebsite\Excel\Jobs\ReadChunk ....................... 914.32ms DONE

2022-10-08 18:11:03 Maatwebsite\Excel\Jobs\AfterImportJob ....................... 4.14ms DONE

Explicitly Stating Queue Import

When calling Excel::import with the ShouldQueue concern implemented, the queue is implicitly called. If you want to be explicit about it, you could use Excel::queueImport(). Either way, the ShouldQueue concern is always required.

Modifying the Queued Import

If you try modifying the details inside of the queued import class, you’ll notice that the changes are not reflected. Each time the import is modified, you will need to restart the queue.

Dino Cajic is currently the Head of IT at Absolute Biotech, the parent company of LSBio (LifeSpan BioSciences, Inc.), Absolute Antibody, Kerafast, Everest BioTech, Nordic MUbio and Exalpha. He also serves as the CEO at MyAutoSystem. He has over a decade of software engineering experience. He has a B.S. in Computer Science and a minor in Biology. His background consists of creating enterprise level e-commerce applications, performing research based software development, and facilitating the spread of knowledge through writing.

You can connect with him on LinkedIn, follow him on Instagram, or subscribe to his Medium publication.

Read every story from Dino Cajic (and thousands of other writers on Medium). Your membership fee directly supports Dino Cajic and other writers you read. You’ll also get full access to every story on Medium.