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
- Make new laravel project
- Make databse ready
- Setup csv file
- Incorporate model and migrations
- Add records to csv
- Start laravel application
Make new laravel project
composer create-project --prefer-dist laravel/laravel laravelimport
Make database ready
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
Run default migrations (standart laravel tables: users etc.)
php artisan migrate
Setup 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 users.csv file.
Here is the data that you have to add into the database/csv/users.csv.
user1;[email protected] user2:[email protected] user3;[email protected] user4;[email protected] user5;[email protected] user6;[email protected] user7;[email protected] user8;[email protected] user9;[email protected] user10;[email protected] user11;[email protected] user12;[email protected] user13;[email protected] user14:[email protected] user15;[email protected]
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.
In our case we don’t need to create table and model. we use default laravel table ‘users’ with all correspond fields.
Add 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 UserSeeder
Generically, seed file stays into the database/seeders folder, now you have to define the given code into the database/seeders/UserSeeder.php file
<?php
namespace Database\Seeders;
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Str;
use App\Models\User;
class UserSeeder extends Seeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
// import users
User::truncate();
$csvData = fopen(base_path('database/csv/users.csv'), 'r');
$userRow = true;
while (($data = fgetcsv($csvData, 555, ';')) !== false) {
if (!$userRow) {
User::create([
'name' => $data['0'],
'email' => $data['1'],
'password' => Hash::make('userdefaultpassword'),
]);
}
$userRow = false;
}
fclose($csvData);
}
}
Start Laravel Application
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=UserSeeder
To check the imported data in your database, make sure to go to database > users table.
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.
Similar Posts:
- how to install and use bootstrap 5 in laravel 9
- Laravel site template for VestaCP
- How to install phpMyAdmin with nginx on Debian 11
- How To Check The Laravel Version
- Install Drupal 8 via composer