How to import CSV file into MySQL with Laravel Seeder

Dear readers, greetings. This tutorial will provide you step-by-step guidance about how to import CSV file records into MySQL by using laravel seeder. We will be using laravel seeder to grab data from csv file. But, before diving into the steps lets have a brief introdcution of CSV file format and its usage.

What is CSV (comma-separated values) file?

A CSV (comma-separated values) file is simply a text file in which values are stored separated by comma. CSV file helps us to save data in a tabular form. In CSV file, each line is considered as a data record. There are multiple fileds in each record, separated by comma.

What are the usage and advantages of CSV file?

• CSV file is very well established text file format and recognized by many applications.
• CSV file is frequently used for spreadsheets.
• CSV file represents the data typically found in a databse table.
• CSV file is a common format for data interchange because it is simple and compact.
• You can easily open a CSV file into Excel.
• Nearly all databases offer a tool to import data from CSV file.
• You can easily view and edit CSV file in any plain text editor.

How to Import Large Set of Records in MySQL using CSV and Laravel Seeder?

• Step 1: Create Fresh Laravel Application
• Step 2: Setup Database Configuration
• Step 3: Setup CSV File with Large Records
• Step 4: Setup Model and Migration Files
• Step 5: Setup Laravel Seeder to Import Records
• Step 6: Run Laravel Application

Now, we will go through all the above steps one by one to achieve our objective.

Step 1: Create Fresh Laravel Application

Open terminal and install fresh laravel application using below command:

composer create-project --prefer-dist laravel/laravel:^8.0 csvimortapp

Next, lets get inside the csvimortapp using below command:

cd csvimortapp

Step 2: Setup Database Configuration

Next, lets update database configuration in .env file as follows:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel8db
DB_USERNAME=dbusername
DB_PASSWORD=dbpassword

Step 3: Setup CSV File with Large Records

Next, lets create a csv file. Move inside database folder and create a folder called data and products.csv inside data folder. We will following information in products.csv file:
Name, MRP, SellingPrice, Quantity

You can copy and paste below provided dummy data into database/data/products.csv file:

name,mrp,price,quantity
Product01,1000.00,800.00,100
Product02,1000.00,800.00,100
Product03,1000.00,800.00,100
Product04,1000.00,800.00,100
Product05,1000.00,800.00,100
Product06,1000.00,800.00,100
Product07,1000.00,800.00,100
Product08,1000.00,800.00,100
Product09,1000.00,800.00,100
Product10,1000.00,800.00,100
Product11,1000.00,800.00,100
Product12,1000.00,800.00,100
Product13,1000.00,800.00,100
Product14,1000.00,800.00,100
Product15,1000.00,800.00,100
Product16,1000.00,800.00,100
Product17,1000.00,800.00,100
Product18,1000.00,800.00,100
Product19,1000.00,800.00,100
Product20,1000.00,800.00,100

Step 4: Setup Model and Migration Files

Next, lets create model (Product.php) and migration files using below command:

php artisan make:model Models/Product -m

Above command will create Models/Product.php model file and database/migrations/create_products_table.php migration file.

Open the Models/Product.php file and copy the below given code onto it:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    use HasFactory;
    protected $fillable = [
        'name',
        'mrp',
        'price',
        'quantity'
    ];
}

Open the database/migrations/create_products_table.php file and copy the below given code onto it:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateProductsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('mrp');
            $table->string('price');
            $table->string('quantity');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('products');
    }
}

Step 5: Setup Laravel Seeder to Import Records

Next, we will be using laravel seeder to seed products table with a large csv file. Use below command to create ProductSeeder.php

php artisan make:seeder ProductSeeder

Open the database/seeders/ProductSeeder.php file and copy the below given code onto it:

<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use App\Models\Product;

class ProductSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        Product::truncate();
        $heading = true;
        $input_file = fopen(base_path("database/data/products.csv"), "r");
        while (($record = fgetcsv($input_file, 1000, ",")) !== FALSE)
        {
            if (!$heading)
            {
                $product = array(
                    "name" => $record['0'],
                    "mrp" => $record['1'],
                    "price" => $record['2'],
                    "quantity" => $record['3']
                );
                Product::create($product);    
            }
            $heading = false;
        }
        fclose($input_file);
    }
}

Step 6: Run Laravel Application

Next, run the seeder by invoking below command:

php artisan db:seed --class=ProductSeeder

Great, now you can see that all the data from products.csv file has been imported to products table.

Happy Learning:)