Laravel 10: An Example of Importing a Large CSV File into a Database

Understanding CSV Files

CSV (Comma-Separated Values) is a simple file format used to store tabular data, such as spreadsheets and databases. Each line of the file represents a data record, with fields separated by commas.

CSV files are a popular choice for data exchange between applications, as they are easy to generate, read, and parse.

Setting Up the Laravel Project

First, we need to set up a new Laravel project. If you don’t have Laravel installed, you can follow the official documentation to install it. After installing Laravel, create a new project with the following command:

laravel new laravel-csv-import

Next, navigate to the project directory and install the necessary package for handling CSV files:

composer require league/csv

Creating a Model and Migration

For this example, we’ll import a CSV file containing user information. First, create a User model and migration using the following Artisan command:

php artisan make:model User -m

In the generated migration file, add the necessary fields to the users table:

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->string('phone');
    $table->timestamps();
});

Run the migration with the following command:

php artisan migrate

Building the Import Class

Create a new import class using the Artisan command:

php artisan make:import UsersImport --model=User

In the generated UsersImport class, update the model() method to return a new User instance. Also, implement the map() method to map each CSV record to the User model fields:

use App\Models\User;

class UsersImport implements ToModel, WithMapping
{
    public function model(array $row)
    {
        return new User([
            'name' => $row['name'],
            'email' => $row['email'],
            'phone' => $row['phone'],
        ]);
    }

    public function map($row): array
    {
        return [
            'name' => $row[0],
            'email' => $row[1],
            'phone' => $row[2],
        ];
    }
}

Creating a Controller

Now, create a new controller to handle the CSV file import:

php artisan make:controller CsvImportController

In the generated controller, add the necessary methods to handle file uploads and import the CSV data:

use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;

class CsvImportController extends Controller
{
    public function importForm()
    {
        return view('import-form');
    }

    public function import(Request $request)
    {
        $request->validate([
            'csv_file' => 'required|file|mimes:csv,txt',
        ]);

        try {
            Excel::import(new UsersImport, $request->file('csv_file'));
            return redirect()->route('import.form')->with('success', 'CSV file imported successfully!');
        } catch (\Exception $e) {
            return redirect()->route('import.form')->with('error', 'An error occurred while importing the CSV file: ' . $e->getMessage());
        }

    }
}

Developing the Frontend

First, create a new view file named `import-form.blade.php` in the `resources/views` directory. Add the following HTML code for the file upload form:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Laravel 10 CSV Import Example - LaravelTuts.com</title>
    <link href="{{ asset('css/app.css') }}" rel="stylesheet">
</head>
<body>
    <div class="container mt-5">
        <h2 class="text-center">Laravel 10: Import a Large CSV File into a Database</h2>
        <form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
            @csrf
            <div class="form-group">
                <label for="csv_file">Choose CSV File</label>
                <input type="file" name="csv_file" id="csv_file" class="form-control">
            </div>
            <button type="submit" class="btn btn-primary">Import CSV</button>
        </form>
    </div>
    <script src="{{ asset('js/app.js') }}"></script>
</body>
</html>

Next, update the routes/web.php file to include the necessary routes:

use App\Http\Controllers\CsvImportController;

Route::get('/import', [CsvImportController::class, 'importForm'])->name('import.form');
Route::post('/import', [CsvImportController::class, 'import'])->name('import');

Testing the Import Process

Now, run your Laravel application using the following command:

php artisan serve

Visit http://localhost:8000/import in your web browser, and you should see the CSV file upload form. Choose a CSV file and click the “Import CSV” button. The application should import the CSV data into the users table.

Optimizing Performance

For very large CSV files, the import process may take a long time to complete. To optimize performance, consider implementing the following strategies:

Implementing queue-based background processing for file import tasks
Using Laravel’s chunk reading feature to read and process the CSV file in smaller parts
Optimizing database inserts using bulk insert operations or prepared statements

Conclusion

In this blog, we demonstrated how to import a large CSV file into a database using Laravel 10. By leveraging Laravel’s built-in functionality and the league/csv package, we can efficiently import data from CSV files into our applications.