In this tuto, I'll show you how to seed your database with CSV files in Laravel with laravel-csv-seeder.
Seed your database with CSV files - Installation
Require this package in your composer.json and run composer update (or run composer require flynsarmy/csv-seeder:2.* directly):
For PHP 7.4+
"flynsarmy/csv-seeder": "2.0.*"
For older PHP versions
"flynsarmy/csv-seeder": "1.*"
Usage
Your CSV's header row should match the DB columns you wish to import. IE to import id and name columns, your CSV should look like:
id,name
1,Foo
2,Bar
Seed classes must extend Flynsarmy\CsvSeeder\CsvSeeder, they must define the destination database table and CSV file path, and finally they must call parent::run() like so:
use Flynsarmy\CsvSeeder\CsvSeeder;
class StopsTableSeeder extends CsvSeeder {
public function __construct()
{
$this->table = 'your_table';
$this->filename = base_path().'/database/seeds/csvs/your_csv.csv';
}
public function run()
{
// Recommended when importing larger CSVs
DB::disableQueryLog();
// Uncomment the below to wipe the table clean before populating
DB::table($this->table)->truncate();
parent::run();
}
}
Drop your CSV into /database/seeds/csvs/your_csv.csv or whatever path you specify in your constructor above.
Examples
CSV with pipe delimited values:
public function __construct()
{
$this->table = 'users';
$this->csv_delimiter = '|';
$this->filename = base_path().'/database/seeds/csvs/your_csv.csv';
}
Specifying which CSV columns to import:
public function __construct()
{
$this->table = 'users';
$this->csv_delimiter = '|';
$this->filename = base_path().'/database/seeds/csvs/your_csv.csv';
$this->mapping = [
0 => 'first_name',
1 => 'last_name',
5 => 'age',
];
}
Trimming the whitespace from the imported data:
public function __construct()
{
$this->table = 'users';
$this->csv_delimiter = '|';
$this->filename = base_path().'/database/seeds/csvs/your_csv.csv';
$this->mapping = [
0 => 'first_name',
1 => 'last_name',
5 => 'age',
];
$this->should_trim = true;
}
Skipping the CSV header row (Note: A mapping is required if this is done):
public function __construct()
{
$this->table = 'users';
$this->csv_delimiter = '|';
$this->filename = base_path().'/database/seeds/csvs/your_csv.csv';
$this->offset_rows = 1;
$this->mapping = [
0 => 'first_name',
1 => 'last_name',
2 => 'password',
];
$this->should_trim = true;
}
Specifying the DB connection to use:
public function __construct()
{
$this->table = 'users';
$this->connection = 'my_connection';
$this->filename = base_path().'/database/seeds/csvs/your_csv.csv';
}
Example How to use this package:
pays.csv file:
"be","Belgium"
"br","Brazil"
Writing Seeders
Create Seeder Class for generate Country Data for example
php artisan make:seeder PaySeeder
this command will create one file PaySeeder.php on database/seeds directory.
PaySeeder.php file:
use Flynsarmy\CsvSeeder\CsvSeeder;
class CountrySeeder extends CsvSeeder
{
public function __construct()
{
$this->table = 'countries';
$this->filename = base_path() . '/database/seeds/csvs/pays.csv';
$this->mapping = ['0' => 'code', 1 => 'name'];
}
/**
* Run the database seeds.
* @return void
*/
public function run()
{
// Recommended when importing larger CSVs
DB::disableQueryLog();
DB::statement('SET FOREIGN_KEY_CHECKS = 0');
// Uncomment the below to wipe the table clean before populating
DB::table($this->table)->truncate();
DB::statement('SET FOREIGN_KEY_CHECKS = 1');
parent::run();
}
}
Once you have written your seeder, you may need to regenerate Composer's autoloader using the dump-autoload command:
composer dump-autoload
Now you may use the db:seed Artisan command to seed your database. By default, the db:seed command runs the DatabaseSeeder class, which may be used to call other seed classes. However, you may use the --class option to specify a specific seeder class to run individually
php artisan db:seed
php artisan db:seed --class=PaySeeder
Example How to override laravel csv seeder package
I will override readRow() method to save group_id not group_name.
<?php
namespace Database\Seeders;
use Flynsarmy\CsvSeeder\CsvSeeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
class UserSeeder extends CsvSeeder
{
/**
* set settings for Csv Seeder
*/
public function __construct()
{
$this->table = 'users';
$this->filename = base_path() . '/database/seeders/csvs/users.csv';
$this->mapping = [0 => 'name', 1 => 'email', 2 => 'status', 3 => 'group_name'];
$this->insert_chunk_size = 1;
$this->offset_rows = 1;
$this->timestamps = false;
}
/**
* Run DB seed
*/
public function run()
{
// Uncomment the below to wipe the table clean before populating
DB::statement('SET FOREIGN_KEY_CHECKS=0;');
DB::table($this->table)->truncate();
parent::run();
DB::statement('SET FOREIGN_KEY_CHECKS=1;');
}
/**
* Override readRow method
*/
public function readRow(array $row, array $mapping): array
{
$row_values = [];
foreach ($mapping as $csvCol => $dbCol) {
if($dbCol === 'group_name'){
if (!isset($row[$csvCol]) || $row[$csvCol] === '') {
$row_values["group_id"] = null;
} else {
$group = DB::table("groups")->where('name', $row[$csvCol])->first();
if($activity){
$row_values["group_id"] = $group->id ? $group->id : null;
}
}
}else{
if (!isset($row[$csvCol]) || $row[$csvCol] === '') {
$row_values[$dbCol] = null;
} else {
$row_values[$dbCol] = $this->should_trim ? trim($row[$csvCol]) : $row[$csvCol];
}
}
}
if (!empty($this->hashable)) {
foreach ($this->hashable as $columnToHash) {
if (isset($row_values[$columnToHash])) {
$row_values[$columnToHash] = Hash::make($row_values[$columnToHash]);
}
}
}
if ($this->timestamps) {
$row_values['created_at'] = $this->created_at;
$row_values['updated_at'] = $this->updated_at;
}
return $row_values;
}
}
I hope you found this article useful. let me know if you have any questions and I’ll be happy to answer them.
