How to Import Records in SQL with CSV and Seeder in Laravel 9

The database system has the integrity to hold records, whether they are few or many. This tutorial may be a goldmine of knowledge for you if you are willing to import extensive data records through CSV. The comprehensive tutorial will help you discover how you can import records in SQL with CSV and seeder.

CSV stands for comma-separated values, and is a delimited text file. It typically holds large numbers of data, and the information is laid in rows form. A single row may have few or more than values. Each value is separated by commas.

Let’s use the Laravel seeder to grab data from a CSV file. A tool Laravel suggests to add dummy data to the database without putting extra effort. You may also use it to insert the testing data into the database table using the database seeder.

Import Large SQL Records with CSV and Seeder

Step 1: Create a New Laravel Project

Create a new Laravel project by passing in the composer command below at the desired location where you want your project to be at.

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

Step 2: Database Credentials

You must now add database credentials in your .env file, as shown below.

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

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=db_name
DB_USERNAME=db_username
DB_PASSWORD=db_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.

In case you are working on macOS, and using the local MAMP server, you will also need to add the UNIX_SOCKET and DB_SOCKET database credentials in .env file.

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

Step 3: Set Up CSV

We will now create a new CSV. You must redirect to the database folder, and create a new folder as ‘csv’ and a transaction.csv file here.

The csv file will hold a dummy transaction reportr with the Transaction_date, Name, City, US Zip, Payment_Type, and Price.

You wi8ll have to add the data below in the database/csv/transaction_report.csv file.

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

Step 4: Add related Model and Migrations

Now, we will set up the model and migration. It allows to create table definition for the database structure.

Firstly, execute the command below and make sure it generates the model and migration files simultaneously.

php artisan make:model Transaction -m

Now open the database/migrations/create_transactions_table.php file and copy the code below 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');
    }
}

Similarly, do the same process for 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 may execute the migration command.

php artisan migrate

This adds in a table in the database as per your specifications.

Step 5: Add Large Records using CSV

The setup has been done, now comes the main step, i.e. to seed your database with a large CSV file. Here we use the Laravel seeder. Pass the seeder artisan command as below to invoke the seed class.

php artisan make:seeder TransactionSeeder

Generically, seed file stays in the database/seeders folder, so navigate there, open the database/seeders/TransactionSeeder.php file, and edit it as given below.

<?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);
    }
}

Step 6: Start Laravel App

It’s time to seed the records into the database table. We will pass the command below for this task.

php artisan db:seed --class=TransactionSeeder

You can now seed the records in your database table.

It inserts each data item defined in our comma-separated file. To check the imported data in your database, you may go to your database > transactions table and make sure you have the data in correct format.

Here is the database table output.