Seed your database using CSV files, XLSX files, and more with Laravel
With this package you can save time seeding your database. Instead of typing out seeder files, you can use CSV, XLSX, or any supported spreadsheet file format to load your project's database. There are configuration options available to control the insertion of data from your spreadsheet files.
This project was forked from laravel-csv-seeder and rewritten to support processing multiple input files and to use the PhpSpreadsheet library to support XLSX and other file formats.
,
, tab \t
, pipe |
, and semi-colon ;
This package has been used on CSV files with 5 million rows per file while maintaining flat memory usage (no memory leaks).
This package has PHPUnit tests run automatically by Github Actions. Tests are added as enhancements are made or as bugs are found and fixed.
This package is tested against the following Laravel versions
composer require --dev -W bfinlay/laravel-excel-seeder
Or add this package in your composer.json and run composer update
"require-dev": {
...
"bfinlay/laravel-excel-seeder": "^3.0",
...
}
Laravel 5.8, 6.x, and 7.x require DBAL 2.x. Because DBAL is a require-dev
dependency of laravel, its version
constraint will not be resolved by composer when installing a child package. However, this is easy to solve by specifying DBAL 2.x as
an additional dependency.
Note that Laravel 5.8, 6.x, 7.x, 8.x, and 9.x are EOL. See https://laravelversions.com/en. These versions will continue to be supported by this package for as long as reasonably possible, thanks to github actions performing the testing.
To install for Laravel 5.8, 6.x, and 7.x:
composer require --dev -W bfinlay/laravel-excel-seeder
composer require --dev -W doctrine/dbal:^2.6
Or add these packages in your composer.json and run composer update
"require-dev": {
...
"bfinlay/laravel-excel-seeder": "^3.0",
"doctrine/dbal": "^2.6"
...
}
In the simplest form, you can use the bfinlay\SpreadsheetSeeder\SpreadsheetSeeder
as is and it will process all XLSX files in /database/seeds/*.xlsx
and /database/seeders/*.xlsx
(relative to Laravel project base path).
Just add the SpreadsheetSeeder to be called in your /database/seeds/DatabaseSeeder.php
(Laravel 5.8, 6.x, 7.x) or /database/seeder/DatabaseSeeder.php
(Laravel 8.X and newer) class.
use Illuminate\Database\Seeder;
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class DatabaseSeeder extends Seeder
{
/**
* Seed the application's database.
*
* @return void
*/
public function run()
{
$this->call([
SpreadsheetSeeder::class,
]);
}
}
Place your spreadsheets into the path /database/seeds/
(Laravel 5.8, 6.x, 7.x) or /database/seeders/
(Laravel 8.x and newer) of your Laravel project.
With the default settings, the seeder makes certain assumptions about the XLSX files:
An Excel example:
first_name | last_name | birthday |
---|---|---|
Foo | Bar | 1970-01-01 |
John | Doe | 1980-01-01 |
A CSV example:
first_name,last_name,birthday
Foo,Bar,1970-01-01
John,Doe,1980-01-01
In most cases you will need to configure settings.
Create a seed class that extends bfinlay\SpreadsheetSeeder\SpreadsheetSeeder
and configure settings on your class. A seed class will look like this:
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
// By default, the seeder will process all XLSX files in /database/seeds/*.xlsx (relative to Laravel project base path)
// Example setting
$set->worksheetTableMapping = ['Sheet1' => 'first_table', 'Sheet2' => 'second_table'];
}
}
note: the older process of overloading run()
still works for backwards compatibility.
By default, executing the db:seed
Artisan command will seed all worksheets within a workbook.
If you want to specify individual sheets to seed, you may use the xl:seed
Artisan command
with the --sheet
option. You may specify multiple --sheet
options.
php artisan xl:seed --sheet=users --sheet=posts
The above will run the Database\Seeders\DatabaseSeeder
class, and for any SpreadsheetSeeders that are invoked
will only seed sheets named users
and posts
. You may use the --class
option to specify a specific seeder
class to run individually
php artisan xl:seed --class=MySpreadsheetSeederClass --sheet=users --sheet=posts
If you want to run the default SpreadsheetSeeder
class, you can specify --class=#
. (The #
resembles a spreadsheet.)
php artisan xl:seed --class=# --sheet=users --sheet=posts
For an easier syntax, you can also pass these as arguments and omit the --class and --seed. When using arguments,
the first argument must be the class, and subsequent arguments will be sheets.
php artisan xl:seed # users posts
Important note: as with seeding traditional seeder classes individually, when seeding individual sheets if the truncate option is true, relations with cascade delete will also be deleted.
After running the database seeder, a subdirectory will be created using the same name as the input file. A text output file will be created for each worksheet using the worksheet name. This text file contains a text-based representation of each worksheet (tab) in the workbook and can be used to determine changes in the XLSX when merging branches from other contributors.
Two formats are currently supported. The older format is 'markdown' and is the defualt for backward compatibility. The newer format is 'yaml' which is meant to work better with typical line-oriented diff software.
Check this file into the repository so that it can serve as a basis for comparison.
You will have to merge the XLSX spreadsheet manually.
TextOutput can be disabled by setting textOutput
to FALSE
See Text Output for more information.
$addColumns
(array [])
This is an array of column names that will be column names in addition to those found in the worksheet.
These additional columns will be processed the same ways as columns found in a worksheet. Cell values will be considered the same way as "empty" cells in the worksheet. These columns could be populated by parsers, defaults, or uuids.
Example: ['uuid, 'column1', 'column2']
Default: []
$aliases
(array [])
This is an associative array to map the column names of the data source to alternative column names (aliases).
Example: ['CSV Header 1' => 'Table Column 1', 'CSV Header 2' => 'Table Column 2']
Default: []
$batchInsertSize
(integer)
Number of rows to insert in a batch.
Default: 5000
$dateFormats
(array [])
This is an associative array mapping column names in the data source to date format strings that should be used by Carbon to parse the date. Information to construct date format strings is here: https://www.php.net/manual/en/datetime.createfromformat.php
When the destination column in the database table is a date time format, and the source data is a string, the seeder will use Carbon to parse the date format. In many cases Carbon can parse the date automatically without specifying the date format.
When Carbon cannot parse the date automatically, map the column name in this array to the date format string. When a source column is mapped, Carbon will use the date format string instead of parsing automatically.
If column mapping is used (see mapping) the column name should match the value in the $mapping array instead of the value in the file, if any.
Example:
[
'order_date' => 'Y-m-d H:i:s.u+', // parses "2020-10-04 05:31:02.440000000"
]
Default: []
$defaults
(array [])
This is an associative array mapping column names in the data source to default values that will override any values in the datasource.
Example: ['created_by' => 'seed', 'updated_by' => 'seed]
Default: []
$delimiter
(string NULL)
The delimiter used in CSV, tab-separate-files, and other text delimited files. When this is not set, the phpspreadsheet library will automatically detect the text delimiter
Default: null
$extension
(string 'xlsx')
The default extension used when a directory is specified in $this->file
Default: "xlsx"
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
// specify relative to Laravel project base path
// feature directories specified
$set->file = [
'/database/seeds/feature1',
'/database/seeds/feature2',
'/database/seeds/feature3',
];
// process all xlsx and csv files in paths specified above
$set->extension = ['xlsx', 'csv'];
}
}
$file
(string) or (array []) or (Symfony\Component\Finder\Finder)
This value is the path of the Excel or CSV file used as the data source. This is a string or array[] and is list of files or directories to process, which can include wildcards. It can also be set to an instance of Symfony Finder, which is a component that is already included with Laravel.
By default, the seeder will process all XLSX files in /database/seeds (for Laravel 5.8 - 7.x) and /database/seeders (for Laravel 8.x and newer).
The path is specified relative to the root of the project
Default: "/database/seeds/*.xlsx"
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
// specify relative to Laravel project base path
$set->file = [
'/database/seeds/file1.xlsx',
'/database/seeds/file2.xlsx',
'/database/seeds/seed*.xlsx',
'/database/seeds/*.csv'];
}
}
This setting can also be configured to an instance of Symfony Finder, which is a component that is already included with Laravel.
When using Finder, the path is not relative to base_path()
by default.
To make the path relative to base_path()
prepend it to the finder path.
You could also use one of the other Laravel path helpers .
Example:
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
// specify relative to Laravel project base path
$set->file =
(new Finder)
->in(base_path() . '/database/seeds/')
->name('*.xlsx')
->notName('*customers*')
->sortByName();
}
}
$hashable
(array ['password'])
This is an array of column names in the data source that should be hashed
using Laravel's Hash
facade.
The hashing algorithm is configured in config/hashing.php
per
https://laravel.com/docs/master/hashing
Example: ['password']
Default: []
$header
(boolean TRUE)
If the data source has headers in the first row, setting this to true will skip the first row.
Default: TRUE
$inputEncodings
(array [])
Array of possible input encodings from input data source See https://www.php.net/manual/en/mbstring.supported-encodings.php
This value is used as the "from_encoding" parameter to mb_convert_encoding. If this is not specified, the internal encoding is used.
Default: []
$limit
(int)
Limit the maximum number of rows that will be loaded from a worksheet. This is useful in development to keep loading time fast.
This can be used in conjunction with settings in the environment file or App::environment() (APP_ENV) to limit data rows in the development environment.
Example:
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class SalesTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
$set->file = '/database/seeds/sales.xlsx';
if (App::environment('local'))
$set->limit = 10000;
}
}
Default: null
$mapping
(array [])
Backward compatibility to laravel-csv-seeder
This is an array of column names that will be used as headers.
If $this->header is true then the first row of data will be skipped. This allows existing headers in a CSV file to be overridden.
This is called "Mapping" because its intended use is to map the fields of a CSV file without a header line to the columns of a database table.
Example: ['Header Column 1', 'Header Column 2']
Default: []
$offset
(integer)
Number of rows to skip at the start of the data source, excluding the header row.
Default: 0
$outputEncoding
(string)
Output encoding to database See https://www.php.net/manual/en/mbstring.supported-encodings.php
This value is used as the "to_encoding" parameter to mb_convert_encoding.
Default: UTF-8
$parsers
(array ['column' => function($value) {}])
This is an associative array of column names in the data source that should be parsed with the specified parser.
Example:
['email' => function ($value) {
return strtolower($value);
}];
Default: []
$readChunkSize
(integer)
Number of rows to read per chunk.
Default: 5000
$skipper
(string %)
This is a string used as a prefix to indicate that a column in the data source should be skipped. For Excel workbooks, a worksheet prefixed with this string will also be skipped. The skipper prefix can be a multi-character string.
%id_copy
will be skipped with skipper set as %
#id_copy
will be skipped with skipper set as #
[skip]id_copy
will be skipped with skipper set as [skip]
%worksheet1
will be skipped with skipper set as %
Default: "%"
;
$skipColumns
(array [])
This is an array of column names that will be skipped in the worksheet.
This can be used to skip columns in the same way as the skipper character, but without modifying the worksheet.
Example: ['column1', 'column2']
Default: []
$skipSheets
(array [])
This is an array of worksheet names that will be skipped in the workbook.
This can be used to skip worksheets in the same way as the skipper character, but without modifying the workbook.
Example: ['Sheet1', 'Sheet2']
Default: []
$tablename
(string)
Backward compatibility to laravel-csv-seeder
Table name to insert into in the database. If this is not set then the tablename is automatically resolved by the following rules:
Use worksheetTableMapping instead to map worksheet names to alternative table names
Default: null
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
// specify relative to Laravel project base path
// specify filename that is automatically dumped from an external process
$set->file = '/database/seeds/autodump01234456789.xlsx'; // note: could alternatively be a csv
// specify the table this is loaded into
$set->tablename = 'sales';
// in this example, table truncation also needs to be disabled so previous sales records are not deleted
$set->truncate = false;
}
}
$textOutput
(boolean) or (string) or (array [])
true
defaults to 'markdown'
output for backward compatibility.'markdown'
for markdown output'yaml'
for yaml output['markdown', 'yaml']
for both markdown and yaml outputDefault: TRUE
$textOutputPath
(string)
Note: In development, subject to change
Path for text output
After processing a workbook, the seeder outputs a text format of the sheet to assist with diff and merge of the workbook. The default path is in the same path as the input workbook. Setting this path places the output files in a different location.
Default: "";
$timestamps
(string/boolean TRUE)
When true
, set the Laravel timestamp columns 'created_at' and 'updated_at'
with the current date/time.
When false
, the fields will be set to NULL
Default: true
$truncate
(boolean TRUE)
Truncate the table before seeding.
Default: TRUE
Note: does not currently support array of table names to exclude
See example for tablename above
$truncateIgnoreForeign
(boolean TRUE)
Ignore foreign key constraints when truncating the table before seeding.
When false
, table will not be truncated if it violates foreign key integrity.
Default: TRUE
Note: does not currently support array of table names to exclude
$unixTimestamps
(array [])
This is an array of column names that contain values that should be interpreted unix timestamps rather than excel timestamps. See Conversions: Date/Time values
If column mapping is used (see mapping) the column name should match the value in the $mapping array instead of the value in the file, if any.
Note: this setting is currently global and applies to all files or worksheets that are processed. All columns with the specified name in all files or worksheets will be interpreted as unix timestamps. To apply differently to different files, process files with separate Seeder instances.
Example: ['start_date', 'finish_date']
;
Default: []
$uuid
(array [])
This is an array of column names in the data source that the seeder will generate a UUID for.
The UUID generated is a type 4 "Random" UUID using laravel Str::uuid() helper https://laravel.com/docs/10.x/helpers#method-str-uuid
If the spreadsheet has the column and has a UUID value in the column, the seeder will use the UUID value from the spreadsheet.
If the spreadsheet has any other value in the column or is empty, the seder will generate a new UUID value.
If the spreadsheet does not have the column, use $addColumns to add the column, and also use $uuid (this setting) to generate a UUID for the added column.
Example: ['uuid']
Default: []
$validate
(array [])
This is an associative array mapping column names in the data source that should be validated to a Laravel Validator validation rule. The available validation rules are described here: https://laravel.com/docs/master/validation#available-validation-rules
Example:
[
'email' => 'unique:users,email_address',
'start_date' => 'required|date|after:tomorrow',
'finish_date' => 'required|date|after:start_date'
]
Default: []
$worksheetTableMapping
(array [])
This is an associative array to map names of worksheets in an Excel file to table names.
Excel worksheets have a 31 character limit.
This is useful when the table name should be longer than the worksheet character limit.
Example: ['Sheet1' => 'first_table', 'Sheet2' => 'second_table']
Default: []
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
// specify the table this is loaded into
$set->worksheetTableMapping = [
'first_table_name_abbreviated' => 'really_rather_very_super_long_first_table_name',
'second_table_name_abbreviated' => 'really_rather_very_super_long_second_table_name'
];
}
}
NULL
, 'true' is converted to TRUE
, 'false' is converted to FALSE
NULL
are treated as explicit nulls. They are not subject to implicit conversions to default values. When the destination table column is a date/time type, the cell value is converted to a Date/Time format.
Use a specific timestamp for 'created_at' and 'updated_at' and also give the seeder a specific table name instead of using the CSV filename;
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
$set->file = '/database/seeds/csvs/users.csv';
$set->tablename = 'email_users';
$set->timestamps = '1970-01-01 00:00:00';
}
}
Map the worksheet tab names to table names.
Excel worksheet tabs have a 31 character limit. This is useful when the table name should be longer than the worksheet tab character limit.
See example above
Map the worksheet or CSV headers to table columns, with the following CSV;
1 | Foo | Bar |
2 | John | Doe |
1,Foo,Bar
2,John,Doe
Example:
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
$set->file = '/database/seeds/users.xlsx';
$set->mapping = ['id', 'firstname', 'lastname'];
$set->header = FALSE;
}
}
Note: this mapping is a legacy laravel-csv-seeder option. The mapping currently applies to all worksheets within a workbook, and is currently designed for single sheet workbooks and CSV files.
There are two workarounds for mapping different column headers for different input files or worksheets:
Seed a table with aliases and default values, like this;
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
$set->file = '/database/seeds/csvs/users.csv';
$set->aliases = ['csvColumnName' => 'table_column_name', 'foo' => 'bar'];
$set->defaults = ['created_by' => 'seeder', 'updated_by' => 'seeder'];
}
}
Skip a worksheet in a workbook, or a column in an XLSX or CSV with a prefix. For example you use id
in your worksheet which is only usable in your workbook. The worksheet file might look like the following:
%id | first_name | last_name | %id_copy | birthday |
---|---|---|---|---|
1 | Foo | Bar | 1 | 1970-01-01 |
2 | John | Doe | 2 | 1980-01-01 |
The first and fourth value of each row will be skipped with seeding. The default prefix is '%' and changeable. In this example the skip prefix is changed to 'skip:'
skip:id | first_name | last_name | skip:id_copy | birthday |
---|---|---|---|---|
1 | Foo | Bar | 1 | 1970-01-01 |
2 | John | Doe | 2 | 1980-01-01 |
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
$set->file = '/database/seeds/users.xlsx';
$set->skipper = 'skip:';
}
}
To skip a worksheet in a workbook, prefix the worksheet name with '%' or the specified skipper prefix.
Validate each row of an XLSX or CSV like this;
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
$set->file = '/database/seeds/users.xlsx';
$set->validate = [ 'name' => 'required',
'email' => 'email',
'email_verified_at' => 'date_format:Y-m-d H:i:s',
'password' => ['required', Rule::notIn([' '])]];
}
}
Hash values when seeding an XLSX or CSV like this;
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function settings(SpreadsheetSeederSettings $set)
{
$set->file = '/database/seeds/users.xlsx';
$set->hashable = ['password'];
}
}
The mb_convert_encodings function is used to convert encodings.
[]
which defaults to internal encoding. See [https://www.php.net/manual/en/mbstring.supported-encodings.php]
use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;
class UsersTableSeeder extends SpreadsheetSeeder
{
/**
When using Postgres, Excel Seeder for Laravel will automatically update Postgres sequence counters for auto-incrementing id columns.
MySQL automatically handles the sequence counter for its auto-incrementing columns.
Excel Seeder for Laravel is open-sourced software licensed under the MIT license.
doctrine\dbal
3.xxl:seed
command to specify individual sheets as suggested in issue #8
limit
featurelimit
test
SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535
=index(X:X,match(Y,Z:Z,0))
in a skipped column to verify foreign keys.