Laravel 8 Import Large SQL with CSV and Seeder Tutorial

Web developers who often work with SQL know how valuable this SQL database is. There are many ways to add data; today, in this article, we will learn how to import large CSV data into SQL database in Laravel 8 application.

To add the CSV data in Laravel SQL, we will use the laravel database seeder. CSV file holds the comma-separated values. Ideally, commas are used to separate the values in the CSV file. We will show you how to create the CSV file and add the data into the laravel.

Laravel framework offers some of the agile features. This Laravel CSV database seeder example will show you the top-notch mechanism of laravel. We will show you how to add records in the laravel database using the CSV file.

How to Import SQL Database with CSV and Seeder in Laravel

  • Step 1: Create Laravel Project
  • Step 2: Connect to Database
  • Step 3: Create CSV
  • Step 4: Prepare Migrations and Model
  • Step 5: Import Data with Seeder

Create Laravel Project

Go to the terminal, type command, and invoke the command to install the new laravel app.

composer create-project --prefer-dist laravel/laravel test

Use command to enter into the project directory.

cd test

Connect to Database

In this step, you will have to open the .env file and add the database credentials into this file.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database_name
DB_USERNAME=database_username
DB_PASSWORD=database_password

Create CSV

Next, get inside the database folder, create the data folder then create the financial-report.csv file in the data/ folder.

We have created a CSV financial report, to complete the file add the code into the database/data/financial-report.csv file.

Period,Data_value,Suppressed,STATUS,UNITS
2016.06,1116.386,,F,Dollars
2016.09,1070.874,,F,Dollars
2016.12,1054.408,,F,Dollars
2017.03,1010.665,,F,Dollars
2017.06,1233.7,,F,Dollars
2017.09,1282.436,,F,Dollars
2017.12,1290.82,,F,Dollars
2018.03,1412.007,,F,Dollars
2018.06,1488.055,,F,Dollars
2018.09,1497.678,,F,Dollars
2018.12,1570.507,,F,Dollars
2019.03,1393.749,,F,Dollars
2019.06,1517.143,,F,Dollars
2019.09,1381.514,,F,Dollars
2019.12,1370.985,,F,Dollars
2020.03,1073.017,,F,Dollars
2020.06,1131.445,,F,Dollars
2020.09,1440.101,,F,Dollars
2020.12,1489.979,,F,Dollars
2021.03,1390.782,,F,Dollars
2021.06,1826.73,,F,Dollars
2016.06,1061.354,,R,Dollars
2016.09,1059.264,,R,Dollars
2016.12,1055.683,,R,Dollars
2017.03,1075.857,,R,Dollars
2017.06,1174.183,,R,Dollars
2017.09,1270.569,,R,Dollars
2017.12,1288.087,,R,Dollars
2018.03,1505.77,,R,Dollars
2018.06,1416.372,,R,Dollars
2018.09,1483.703,,R,Dollars
2018.12,1566.251,,R,Dollars
2019.03,1488.614,,R,Dollars
2019.06,1441.339,,R,Dollars
2019.09,1370.491,,R,Dollars
2019.12,1366.993,,R,Dollars
2020.03,1145.482,,R,Dollars
2020.06,1074.819,,R,Dollars
2020.09,1430.05,,R,Dollars
2020.12,1486.139,,R,Dollars
2021.03,1480.051,,R,Dollars
2021.06,1738.499,,F,Dollars
2016.06,1025.436,,R,Dollars
2016.09,1070.246,,R,Dollars
2016.12,1053.542,,R,Dollars
2017.03,1085.732,,R,Dollars
2017.06,1173.246,,R,Dollars
2017.09,1252.99,,R,Dollars
2017.12,1328.224,,R,Dollars
2018.03,1393.548,,R,Dollars
2018.06,1437.152,,R,Dollars
2018.09,1491.932,,R,Dollars
2018.12,1539.381,,R,Dollars
2019.03,1506.566,,R,Dollars
2019.06,1430.703,,R,Dollars
2019.09,1379.168,,R,Dollars
2019.12,1369.957,,R,Dollars
2020.03,,Y,C,Dollars
2020.06,1413.073,,R,Dollars
2020.09,,Y,C,Dollars
2020.12,1471.482,,R,Dollars
2021.03,1489.814,,R,Dollars
2021.06,,Y,C,Dollars
2016.06,1189.735,,F,Dollars
2016.09,1144.938,,F,Dollars
2016.12,1390.589,,F,Dollars
2017.03,1310.912,,F,Dollars
2017.06,1241.466,,F,Dollars
2017.09,1288.648,,F,Dollars
2017.12,1772.086,,F,Dollars
2018.03,1554.221,,F,Dollars
2018.06,1441.386,,F,Dollars
2018.09,1364.769,,F,Dollars
2018.12,1771.028,,F,Dollars
2019.03,1665.84,,F,Dollars
2019.06,1554.397,,F,Dollars
2019.09,1484.434,,F,Dollars
2019.12,1817.118,,F,Dollars
2020.03,1582.915,,F,Dollars
2020.06,1421.593,,F,Dollars
2020.09,1614.752,,R,Dollars
2020.12,2089.453,,R,Dollars
2021.03,1817.439,,R,Dollars
2021.06,1814.973,,F,Dollars
2016.06,1266.46,,R,Dollars
2016.09,1290.305,,R,Dollars
2016.12,1221.059,,R,Dollars
2017.03,1266.43,,R,Dollars
2017.06,1329.659,,R,Dollars
2017.09,1441.852,,R,Dollars
2017.12,1550.553,,R,Dollars
2018.03,1513.2,,R,Dollars
2018.06,1544.738,,R,Dollars
2018.09,1520.486,,R,Dollars
2018.12,1542.182,,R,Dollars
2019.03,1637.375,,R,Dollars
2019.06,1663.32,,R,Dollars
2019.09,1647.29,,R,Dollars
2019.12,1579.243,,R,Dollars

Prepare Migrations and Model

Subsequently, we have to prepare the model and migration files. These files help create the architecture of the table for the database.

Here is the command that you need to evoke from the database.

php artisan make:model Finance -m

Add the csv file values to set up the migration file; here is how you can do it.

Then, add the given code into the database/migrations/create_finances_table.php.

<?php

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

class CreateFinancesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('finances', function (Blueprint $table) {
            $table->id();
            $table->string('period');
            $table->string('data_value');
            $table->string('suppressed');
            $table->string('status');
            $table->string('units');
            $table->timestamps();
        });
    }

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

Next, you have to create the model for the Finance table. Go to the app/Models/Finance.php file, and define the fillable array with the table values.

<?php

namespace App\Models;

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

class Finance extends Model
{
    use HasFactory;
    
    protected $fillable = [
        'period', 
        'data_value',
        'suppressed',
        'status',
        'units',
    ];
}

Now, run the following command to create the migration in laravel.

php artisan migrate

Import Data with Seeder

Again, open the command prompt this time; we will use the PHP artisan command to create the seeder class.

php artisan make:seeder FinanceSeeder

Open the database/seeders/FinanceSeeder.php file and append the suggested code within the file.

<?php

namespace Database\Seeders;

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


class FinanceSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        Finance::truncate();
  
            $report = fopen(base_path("database/data/financial-report.csv"), "r");
    
            $dataRow = true;
            while (($data = fgetcsv($report, 4000, ",")) !== FALSE) {
                if (!$dataRow) {
                    Finance::create([
                        "period" => $data['0'],
                        "data_value" => $data['1'],
                        "suppressed" => $data['2'],
                        "status" => $data['3'],
                        "units" => $data['3']
                    ]);    
                }
                $dataRow = false;
            }
   
        fclose($report);
    }
}

To seed the database, you have to use the given command along with your seeder class name.

php artisan db:seed --class=FinanceSeeder

Laravel 8 Import Large SQL with CSV and Seeder Tutorial

Summary

Seldom, you may need to import the data into the SQL database using the CSV file. Now, using this step by step guide, you will be able to import the records into the SQL database via the laravel application.

CSV file has some advantages: easy to make, human-readable, comprehensive support of text editors, etc.

We hope this guide will be beneficial for you.