The Challenge of Importing Large CSV File
Importing large CSV files into a database is a common but challenging task. If not handled correctly, it can lead to slow performance, timeouts, or server crashes. When dealing with thousands or even millions of records, processing everything in a single request is inefficient and can overload your system.
In this guide, we’ll explore the best practices to import large CSV files in Laravel using Jobs and Queues. By leveraging asynchronous processing, chunked imports, and queue workers, we’ll ensure a fast, scalable, and optimized import process. Let’s dive in! 🚀
Right after this introduction, you’ll find a video demonstrating what we can achieve. So, stay patient and read till the end!
Step-by-Step Guidance on Importing Large CSV Files with User Data
Step 1: Create a Job for Importing CSV Data
Generate a job using Artisan:
php artisan make:job ProcessUserImportChunkJob
Open app/Jobs/ProcessUserImportChunkJob.php and modify it.
<?php
namespace App\Jobs;
/**
* ProcessUserImportChunkJob handles the processing of user data chunks for bulk import.
*
* This job implements ShouldQueue to process large CSV imports asynchronously by
* handling chunks of user data and inserting them into the database in bulk.
*
* @property array $chunk An array containing a chunk of user data to be imported
*
* @implements \Illuminate\Contracts\Queue\ShouldQueue
* @uses \Illuminate\Bus\Queueable
* @uses \Illuminate\Queue\InteractsWithQueue
* @uses \Illuminate\Foundation\Bus\Dispatchable
* @uses \Illuminate\Queue\SerializesModels
* @uses \Illuminate\Bus\Batchable
*/
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Bus\Batchable;
use Illuminate\Support\Facades\DB;
class ProcessUserImportChunkJob implements ShouldQueue
{
use Dispatchable, InteractsWithQueue, Queueable, SerializesModels, Queueable, Batchable;
protected array $chunk;
public function __construct(array $chunk)
{
$this->chunk = $chunk;
}
public function handle()
{
/**
* Inserts a chunk of user records into the users table.
*
* Uses Laravel's query builder to perform a bulk insert operation
* with the chunk of data stored in $this->chunk property.
* This method is part of the batch processing strategy for large CSV imports.
*
* @throws \Illuminate\Database\QueryException When the insert operation fails
*/
DB::table('users')->insert($this->chunk);
}
}
Step 2: Create a Controller to Process the CSV
Generate a controller to show import form, handle file upload and dispatch jobs:
php artisan make:controller UserImportController
Open app/Http/Controllers/UserImportController.php and modify it.
<?php
namespace App\Http\Controllers;
use App\Jobs\ProcessUserImportChunkJob;
use Illuminate\Contracts\View\View;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Bus;
use Illuminate\Support\Facades\Storage;
class UserImportController extends Controller
{
/**
* Display the user import form.
*
* @return \Illuminate\View\View Returns the view for user import interface
*/
public function showForm(): View
{
return view('user-import');
}
/**
* Handle CSV file upload and process user import in batches.
*
* This method validates and processes a CSV file containing user data.
* It maps CSV columns to user fields and chunks the data for batch processing.
* The file is processed line by line to prevent memory issues with large files.
*
* Expected CSV structure:
* - Column 0: Name
* - Column 1: Email
* - Column 2: Password
*
* @param \Illuminate\Http\Request $request The HTTP request containing the CSV file
* @throws \Illuminate\Validation\ValidationException If file validation fails
* @return \Illuminate\Http\RedirectResponse Redirects back with success message
*
* Features:
* - Validates CSV file (max 20MB)
* - Processes in chunks of 500 records
* - Uses Laravel's job batching system
* - Automatically removes processed file
* - Skips CSV header row
*/
public function uploadCsv(Request $request)
{
$request->validate([
'csv_file' => 'required|mimes:csv,txt|max:20480',
]);
$file = $request->file('csv_file');
$filePath = $file->store('csv'); // Store the file in storage/app/csv
// Define the columns we want to map (adjust index if needed)
$selectedFields = [
'name' => 0, // Name is in column index 0
'email' => 1, // Email is in column index 1
'password' => 2, // Password is in column index 2
];
$batch = Bus::batch([])->dispatch();
$chunkSize = 500;
$chunks = [];
// Open file stream for reading
if (($handle = Storage::readStream($filePath)) !== false) {
$firstLine = true;
while (($row = fgetcsv($handle, 1000, ",")) !== false) {
if ($firstLine) {
$firstLine = false;
continue; // Skip header row
}
$chunks[] = [
'name' => $row[$selectedFields['name']],
'email' => $row[$selectedFields['email']],
'password' => $row[$selectedFields['password']],
];
if (count($chunks) === $chunkSize) {
$batch->add(new ProcessUserImportChunkJob($chunks));
$chunks = [];
}
}
if (!empty($chunks)) {
$batch->add(new ProcessUserImportChunkJob($chunks));
}
fclose($handle);
}
// Delete the file after processing
Storage::delete($filePath);
return redirect()->back()->with('success', 'CSV file uploaded successfully! Processing started.');
}
}
Step 3: Create Blade Files for the Import Form
Create a Layout File
Inside resources/views/layouts/app.blade.php, create a basic layout:
<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>@yield('title', 'Laravel App')</title>
<!-- Tailwind CSS -->
<script src="https://cdn.tailwindcss.com"></script>
<script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
@stack('styles')
</head>
<body class="bg-gray-100">
<!-- Navbar -->
<nav class="bg-gray-900 text-white p-4">
<div class="container mx-auto flex justify-between items-center">
<a href="{{ url('/') }}" class="text-xl font-bold">CSV Import App</a>
<ul class="flex space-x-4">
<li><a href="{{ route('csv.form') }}" class="hover:text-gray-300">Upload CSV</a></li>
</ul>
</div>
</nav>
<!-- Page Content -->
<div class="container mx-auto mt-10 p-6 bg-white shadow-md rounded-lg">
@yield('content')
</div>
@stack('scripts')
</body>
</html>
Extend Layout in the Import Form
Now, modify resources/views/user-import.blade.php to extend the layout:
@extends('layouts.app')
@section('content')
<div class="max-w-lg mx-auto mt-20">
<div class="bg-white shadow-md rounded-lg p-6">
<h2 class="text-xl font-semibold text-gray-800 text-center mb-4"> Import User</h2>
<!-- Validation Errors -->
@if ($errors->any())
<div class="mb-4 p-3 bg-red-100 text-red-700 rounded-md">
<ul>
@foreach ($errors->all() as $error)
<li>{{ $error }}</li>
@endforeach
</ul>
</div>
@endif
<!-- Success Message -->
@if (session('success'))
<div class="mb-4 p-3 bg-green-100 text-green-700 rounded-md">
{{ session('success') }}
</div>
@endif
<form id="csv-upload-form" action="{{ route('csv.upload') }}" method="POST" enctype="multipart/form-data">
@csrf
<div
class="flex flex-col items-center justify-center w-full py-6 bg-gray-100 border-2 border-gray-300 border-dashed rounded-lg cursor-pointer hover:bg-gray-200 transition">
<input id="csv_file" type="file" name="csv_file" class="hidden" accept=".csv" required>
<label for="csv_file" class="flex flex-col items-center cursor-pointer">
<svg class="w-12 h-12 text-gray-500 mb-2" fill="none" stroke="currentColor" stroke-width="2"
viewBox="0 0 24 24">
<path stroke-linecap="round" stroke-linejoin="round" d="M12 4v16m8-8H4"></path>
</svg>
<span class="text-gray-600" id="file-label">Click to upload a CSV file</span>
</label>
</div>
<button type="submit" id="upload-btn"
class="w-full bg-blue-600 hover:bg-blue-700 text-white py-2 px-4 rounded-lg mt-4 transition duration-300 hidden">
Upload & Process CSV
</button>
</form>
</div>
</div>
<script>
document.getElementById('csv_file').addEventListener('change', function(event) {
let fileName = event.target.files[0]?.name || "Click to upload a CSV file";
document.getElementById('file-label').textContent = `Selected: ${fileName}`;
document.getElementById('upload-btn').classList.remove('hidden');
});
</script>
@endsection
Step 4: Create a Route for CSV import functionality
Open routes/web.php and modify it.
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\UserImportController;
/**
* Web routes for CSV import functionality
*
* Route definitions for handling CSV user import operations:
* - GET '/' : Displays the CSV import form
* - POST '/import' : Handles the CSV file upload and import process
*
* @uses App\Http\Controllers\UserImportController
*/
Route::get('/', [UserImportController::class, 'showForm'])->name('csv.form');
Route::post('/import', [UserImportController::class, 'uploadCsv'])->name('csv.upload');
require __DIR__ . '/auth.php';
Step 5: Set Up Laravel Queues
Laravel supports different queue drivers like database, Redis, and Amazon SQS. we will use the database driver.
Configure the Queue Driver
Update the .env file to use the database queue driver:
QUEUE_CONNECTION=database
Then, create the necessary database table for queues:
php artisan queue:table php artisan migrate
Step 6: Start Development server in a separate terminal:
php artisan serve
Step 7: Run the queue worker in a separate terminal:
php artisan queue:work --tries=3
Notes & Tips
-
Ensure Proper File Storage Access: Run the following commands to allow Laravel to access uploaded files smoothly:
php artisan storage:link chmod -R 777 storage/app
The first command creates a symbolic link to the
storagefolder, while the second grants full permissions to thestorage/appdirectory. - Use Redis for Performance: If dealing with very large files, consider switching to Redis for faster queue processing.
- Monitor Queue Jobs: Use
php artisan queue:failedto check for failed jobs andphp artisan queue:retry allto retry them.
Conclusion
Using Laravel’s Jobs and Queues, we can efficiently handle large CSV imports without performance issues. By breaking the data into chunks and processing them asynchronously, we prevent timeouts and improve scalability. If you need further enhancements, consider using Redis for even better performance.
For more insightful tutorials, visit our Tech Blogs and explore the latest in Laravel, AI, and Vue.js development!
