Laravel 9 Import Records in SQL with CSV and Seeder

Last updated on: March 1, 2023 by Digamber

The database has the integrity to hold the records, be it a few or many more. If you are a Laravel developer and looking to import extensive data records through CSV. Then, this tutorial is going to be a goldmine of knowledge.

This comprehensive tutorial will help you discover how to import large data records in SQL using the CSV and Laravel seeder.

CSV stands for comma-separated values; generally, it is a delimited text file. This file holds a large amount of data; typically, information is laid in rows form. A single row may contain few or more than few values. Every value is separated by commas.

We will use the laravel seeder to grab the data from the CSV; laravel proposes a tool to add dummy data to the database without putting extra effort. You can use it to insert the testing data into the database table using the database seeder.

How to Import Large SQL Records in Laravel using CSV and Database Seeder

Step 1: Download New Laravel Project
Step 2: Make Database Ready
Step 3: Set Up CSV
Step 4: Incorporate Model and Migrations
Step 5: Add Large Records using CSV
Step 6: Start Laravel App

Download New Laravel Project

You must have created the laravel app; however, if not, get to the command-line tool.

Type the command and initiate the app installation process.

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

Make Database Ready

Next, you will have to make your database ready to create the consensus between your laravel app and database.

Open .env and define the database name, username and password respectively.

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

If you are using MAMP local server in macOs; make sure to append UNIX_SOCKET and DB_SOCKET below database credentials in .env file.

UNIX_SOCKET=/Applications/MAMP/tmp/mysql/mysql.sock
DB_SOCKET=/Applications/MAMP/tmp/mysql/mysql.sock

Set Up CSV File

Let us prepare the csv file; for that, you have to ensure you are in the database folder, here you have to create `csv` folder and a transaction_report.csv file.

Theoretically, this csv file holds the dummy transaction report with Transaction_date, Price, Payment_Type, Name, City, US Zip

Here is the data that you have to add into the database/csv/transaction_report.csv.

Transaction_date,Price,Payment_Type,Name,City,US Zip
01/11/2009 17:28,1200,Visa,Sophie,Newtown,6470
01/03/2009 21:11,1200,Mastercard,asuncion,Centennial,80112
01/23/2009 11:33,1200,Mastercard,Sandrine,Walnut Creek,94596
01/07/2009 19:06,3600,Amex,Brittany,Orlando,32801
01/05/2009 19:29,1200,Visa,Carmen,Arlington,22201
01/25/2009 8:54,1200,Amex,Corinne,Anthem,85086
01/12/2009 8:05,1200,Amex,Francoise,Danville,17821
01/15/2009 8:12,1200,Visa,Katherine,Marietta,30060
01/19/2009 14:53,1200,Mastercard,Laura,Fairfield,52556
01/11/2009 6:01,1200,Mastercard,Anna,New York,10007
01/18/2009 11:17,1200,Amex,Rachel,Atascadero,93422
01/17/2009 3:22,1200,Visa,Rachel,Fresno,93702
01/12/2009 20:43,1200,Visa,Alicja,Kenmore,98028
01/05/2009 10:25,1200,Amex,linda,Arlington,22201
01/06/2009 16:08,3600,Visa,cristina,Seattle,98104
01/13/2009 5:58,1200,Mastercard,Kit,Suwanee,30024
01/01/2009 9:35,1200,Mastercard,Barbara,La Crescenta,91214
01/16/2009 11:49,1200,Mastercard,Yoko,Andover,1810
01/13/2009 11:54,1200,Visa,alice and rudolf,Baltimore,21224
01/15/2009 9:52,1200,Amex,Mark,Holmdel,7733
01/27/2009 8:11,1200,Visa,Helena,Fremont,94538
01/21/2009 9:07,1200,Visa,Elaine,Vienna,22180
01/25/2009 19:59,1200,Visa,Kim,Seattle,98104
01/10/2009 21:36,1200,Visa,Craig,Seattle,98104
01/28/2009 20:59,1200,Visa,Michele,Austin,78701
01/05/2009 12:41,1200,Mastercard,Kathryn,Pacific Beach,92109
01/30/2009 19:12,1200,Diners,Heather,Ooltewah,37363
01/19/2009 8:52,1200,Visa,Pam,Richmond,77469
01/28/2009 20:44,1200,Visa,Whitney,Falmouth,22405
01/06/2009 16:07,1200,Visa,Margaret,Terrell Hills,78205
01/23/2009 21:41,1200,Visa,Joanne,Oak Park,48237
01/09/2009 19:41,1200,Mastercard,Tarah,San Jose,95113
01/07/2009 13:49,1200,Diners,megan,West Hills,91303
01/12/2009 14:50,1200,Visa,Holly,Arlington,2476
01/07/2009 20:15,1200,Amex,Nicole,Houston,77002
01/07/2009 15:12,3600,Visa,Anabela,Flossmoor,60422
01/07/2009 7:44,1200,Mastercard,Marie,Ball Ground,30107
01/06/2009 22:19,3600,Amex,Ritz,Pittsfield,5762
01/06/2009 23:00,3600,Amex,Sylvia,Pittsfield,5762
01/09/2009 6:39,1200,Mastercard,Anneli,Houston,77002
01/08/2009 16:24,1200,Visa,jennifer,Phoenix,85004
01/06/2009 7:46,1200,Amex,Kelly,Reston,20190
01/03/2009 9:03,1200,Diners,Sheila,Brooklyn,11226
01/08/2009 3:16,1200,Mastercard,Linda,Miami,33130
01/08/2009 3:56,1200,Mastercard,Katherine,New York,10007
01/08/2009 0:42,1200,Visa,Family,Los Gatos,95032
01/01/2009 20:21,1200,Visa,Maxine,Morton,61550
01/07/2009 8:08,1200,Diners,Bryan Kerrene,New York,10007
01/01/2009 2:24,1200,Visa,Lisa,Sugar Land,77478
01/06/2009 7:18,1200,Visa,asuman,Chula Vista,91910
01/02/2009 7:35,1200,Diners,Hani,Salt Lake City,84111
01/02/2009 14:18,1200,Visa,Richard,Riverside,8075
01/05/2009 10:08,1200,Visa,Georgia,Eagle,83616
01/02/2009 9:16,1200,Mastercard,Sean,Shavano Park,78230
01/05/2009 2:42,1200,Diners,Stacy,New York,10002
01/02/2009 20:09,1200,Mastercard,adam,Martin,38237
01/04/2009 20:11,1200,Mastercard,Fleur,Peoria,61601
01/04/2009 13:19,1200,Visa,LAURENCE,Mickleton,8056
01/04/2009 12:56,3600,Visa,Gerd W,Cahaba Heights,35243
01/03/2009 9:58,1200,Visa,Anja,Gainesville,20155
01/06/2009 15:12,1200,Amex,lydia,Sandy Plains,30075
01/24/2009 11:05,1200,Visa,alan,Norcross,30071
01/24/2009 7:14,1200,Mastercard,nihan,Roanoke,24018
01/18/2009 6:09,1200,Mastercard,Ignacio,Engleside,22314
01/04/2009 13:20,1200,Mastercard,Nuria,Superior,80027
01/02/2009 9:57,1200,Amex,leigh,Potomac Falls,20165
01/31/2009 11:14,3600,Mastercard,Ulrika,Scottsdale,85251
01/11/2009 14:07,1200,Visa,Sarah,San Francisco,94103
01/31/2009 8:56,1200,Mastercard,Laura,Coconut Grove,33133
01/31/2009 7:22,1200,Visa,Monica,Weston,6883
01/30/2009 20:36,1200,Visa,Renee,Edinburg,78539
01/08/2009 13:19,1200,Mastercard,valerie et nicolas,Glenn Dale,20706
01/26/2009 11:38,1200,Mastercard,Gabriella,Austin,78701
01/07/2009 18:53,1200,Visa,Marie-Christine,Durham,27701
01/19/2009 10:32,1200,Mastercard,Maie,Queen Creek,85142
01/26/2009 12:05,1250,Mastercard,verena,Queen Creek,85142
01/02/2009 9:31,1200,Visa,camilla,Chicago,60608
01/11/2009 12:22,1200,Visa,Marina,Minneapolis,55401
01/02/2009 13:08,1200,Mastercard,Federica e Andrea,Astoria,97103
01/22/2009 22:16,3600,Mastercard,leanne,Clyde Hill,98004
01/29/2009 15:08,1200,Visa,KERRY,Sunnyvale,94086
01/29/2009 10:09,3600,Mastercard,Jacqui,Scottsdale,85251
01/29/2009 12:28,1200,Mastercard,Alva and Martin,Great Falls,22066
01/23/2009 15:33,1200,Mastercard,T,Brooklyn,11226
01/09/2009 15:24,1200,Mastercard,alex,Farmingtn Hls,48336
01/13/2009 18:08,1200,Amex,Amanda,Houston,77002
01/29/2009 5:30,1200,Diners,Vanessa,Ithaca,14850
01/29/2009 5:43,1200,Visa,Mrs,Sunnyvale,94086
01/28/2009 13:07,1200,Amex,BALA,Accokeek,20607
01/18/2009 4:01,1200,Visa,Eleanor,Fairbanks,99701
01/12/2009 18:24,1200,Diners,Anthony,Victor,14564
01/05/2009 10:49,3600,Amex,Courtney,Mililani,96782
01/05/2009 13:46,1200,Visa,Courtney,Atchison,66002
01/04/2009 17:54,1200,Amex,ruth,Encinitas,92024
01/18/2009 8:56,1200,Mastercard,Stanford,Howell,48843
01/07/2009 12:55,1200,Amex,Fiona,Stamford,6901
01/14/2009 12:07,1200,Mastercard,Lainey,Lakeville,55044
01/28/2009 12:31,1200,Diners,lilia,Brooklyn,11226
01/02/2009 12:16,1200,Mastercard,Monique,Waldorf,20603

Incorporate Model and Migrations

In this step, we will set up the model and migration. It allows you to create the table definition inside the database.

We will demystify the table creation process more eloquently; here are the instructions that are required to be followed.

First, execute the command; make sure to use the below command to generate the model and migrations file simultaneously.

php artisan make:model Transaction -m

Open the database/migrations/create_transactions_table.php and copy the given code and paste into the file.

<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateTransactionsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('transactions', function (Blueprint $table) {
            $table->id();
            $table->string('transaction_date');
            $table->string('price');
            $table->string('payment_type');
            $table->string('name');
            $table->string('city');
            $table->string('us_zip');
            $table->timestamps();
        });
    }
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('transactions');
    }
}

Likewise, repeat the similar process in app/Models/Transaction.php file.

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Transaction extends Model
{
    use HasFactory;
    protected $fillable = [
        'transaction_date', 
        'price',
        'payment_type',
        'name',
        'city',
        'us_zip',
    ];
}

Eventually, we will execute the migration command.

php artisan migrate

Add Large Records using CSV

Almost more than half of the work has been done, now to seed your database with a large CSV, we will use the seeder. The seeder artisan command needs to be evoked to manifest the seed class.

php artisan make:seeder TransactionSeeder

Generically, seed file stays into the database/seeders folder, now you have to define the given code into the database/seeders/TransactionSeeder.php file.

<?php
namespace Database\Seeders;
use Illuminate\Database\Seeder;
use App\Models\Transaction;
class TransactionSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        Transaction::truncate();
        $csvData = fopen(base_path('database/csv/transaction_report.csv'), 'r');
        $transRow = true;
        while (($data = fgetcsv($csvData, 555, ',')) !== false) {
            if (!$transRow) {
                Transaction::create([
                    'transaction_date' => $data['0'],
                    'price' => $data['1'],
                    'payment_type' => $data['2'],
                    'name' => $data['3'],
                    'city' => $data['4'],
                    'us_zip' => $data['5'],
                ]);
            }
            $transRow = false;
        }
        fclose($csvData);
    }
}

Start Laravel App

It’s time to seed the records into the database table; let us hand over this task to the recommended command.

It will insert every data defined in our comma-separated file, and we promise it won’t leave even an iota of data.

php artisan db:seed --class=TransactionSeeder

To check the imported data in your database, make sure to go to database > transactions table.

Here is the output you will view on your db screen.

Laravel Import Records in SQL with CSV and Seeder Tutorial

Summary

Up until now, we have demystified every process to import large records in the SQL database in the Laravel application. To insert the data in the database table, we used the CSV file and laravel seeder.

We hope we have been more articulative in making you understand.

Digamber

I am Digamber, a full-stack developer and fitness aficionado. I created this site to bestow my coding experience with newbie programmers. I love to write on JavaScript, ECMAScript, React, Angular, Vue, Laravel.