Laravel & Eloquent: Seeding a CSV file into a MySQL database

Overview

In this tutorial, we will explore how to seed a CSV file into a MySQL database using Laravel, a popular PHP framework well-known for its elegant syntax and robust features. Laravel’s Eloquent ORM makes interacting with databases incredibly intuitive. By the end of this guide, you’ll have a comprehensive understanding of how to efficiently import a CSV file into your MySQL database using Laravel’s seeders.

Prerequisites

• Basic understanding of PHP and Laravel
• Laravel installation on your system
• A MySQL database
• A CSV file you want to import

Step-by-Step Instructions

Step 1: Setting Up the Environment

Ensure Laravel is installed and configured correctly on your machine. You also need to have a MySQL database ready. If needed, you can create a new database using phpMyAdmin or the MySQL command line:

CREATE DATABASE your_database_name;

Step 2: Creating the Model and Migration

Begin by creating a new model and migration for the table into which you’ll import the CSV data. You can do this easily with Artisan commands:

php artisan make:model MyModel -m

In the generated migration file located in database/migrations, add columns that correspond to the CSV file’s structure. Here’s an example:

public function up()
{
    Schema::create('my_models', function (Blueprint $table) {
        $table->id();
        $table->string('column1');
        $table->string('column2');
       // Add more columns as needed
    });
}

Run the migration to create the table in your database:

php artisan migrate

Step 3: Preparing Your CSV File for Import

Before importing, ensure your CSV file is formatted correctly. It should match the structure of the database table. Additionally, place the CSV file in the Laravel project, for example, in the database/csv directory.

Example:

column1,column2,column3
data1-1,data1-2,data1-3
data2-1,data2-2,data2-3
data3-1,data3-2,data3-3
data4-1,data4-2,data4-3
data5-1,data5-2,data5-3

Step 4: Creating a Seeder to Import the CSV Data

Laravel utilizes seeders to populate database tables with data. You can create a seeder specifically for importing your CSV data:

php artisan make:seeder CsvImportSeeder

Edit the newly created seeder file in database/seeders. Utilize Laravel’s built-in methods and PHP’s native fgetcsv function to read and import the CSV file. See the example below:

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use League\Csv\Reader;

class CsvImportSeeder extends Seeder
{
    public function run()
    {
        $csv = Reader::createFromPath(database_path() . '/csv/yourfile.csv', 'r');
        $csv->setHeaderOffset(0); //set the CSV header offset

        foreach ($csv as $record) {
            DB::table('my_models')->insert([
                'column1' => $record['column1'],
                'column2' => $record['column2'],
                // Map other columns accordingly
            ]);
        }
    }
}

Note: This example uses the league/csv package for CSV handling, demonstrating a more advanced approach. To use league/csv, you must install it via Composer:

composer require league/csv

Step 5: Running the Seeder

Finally, you can run the seeder to import the data into your database:

php artisan db:seed --class=CsvImportSeeder

Conclusion

By following the steps above, you’ve successfully imported a CSV file into a MySQL database using Laravel. This guide introduced you to creating and running migrations, models, and seeders within the Laravel framework. Whether you’re working on a large-scale project or a simple application, mastering these techniques is crucial for efficiently managing database operations.

Remember, Laravel offers a wealth of features and tools to streamline your development process. Exploring further into Laravel’s documentation can open up even more possibilities, enhancing your capability to create robust, scalable web applications.