Download the PHP package bfinlay/laravel-excel-seeder without Composer

On this page you can find all versions of the php package bfinlay/laravel-excel-seeder. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.

FAQ

After the download, you have to make one include require_once('vendor/autoload.php');. After that you have to import the classes with use statements.

Example:
If you use only one package a project is not needed. But if you use more then one package, without a project it is not possible to import the classes with use statements.

In general, it is recommended to use always a project to download your libraries. In an application normally there is more than one library needed.
Some PHP packages are not free to download and because of that hosted in private repositories. In this case some credentials are needed to access such packages. Please use the auth.json textarea to insert credentials, if a package is coming from a private repository. You can look here for more information.

  • Some hosting areas are not accessible by a terminal or SSH. Then it is not possible to use Composer.
  • To use Composer is sometimes complicated. Especially for beginners.
  • Composer needs much resources. Sometimes they are not available on a simple webspace.
  • If you are using private repositories you don't need to share your credentials. You can set up everything on our site and then you provide a simple download link to your team member.
  • Simplify your Composer build process. Use our own command line tool to download the vendor folder as binary. This makes your build process faster and you don't need to expose your credentials for private repositories.
Please rate this library. Is it a good library?
bfinlay/laravel-excel-seeder
Rate from 1 - 5
Rated 2.00 based on 1 reviews

Example code of bfinlay/laravel-excel-seeder

Informations about the package laravel-excel-seeder

Excel Seeder for Laravel

PHPUnit tests

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.

Features

Scale

This package has been used on CSV files with 5 million rows per file while maintaining flat memory usage (no memory leaks).

Testing

This package has PHPUnit tests run automatically by Github Actions. Tests are added as enhancements are made or as bugs are found and fixed.

PHPUnit tests
This package is tested against the following Laravel versions

Contents

Installation

Laravel 8.x, 9.x, 10.x, 11.x

Laravel 5.8, 6.x, 7.x

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:

Simplest Usage

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.

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:

Basic usage

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:

note: the older process of overloading run() still works for backwards compatibility.

Seeding Individual Sheets

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.

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

If you want to run the default SpreadsheetSeeder class, you can specify --class=#. (The # resembles a spreadsheet.)

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.

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.

Excel Text Output for Branch Diffs

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.

Configuration

Add Columns

$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: []

Column Aliases

$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: []

Batch Insert Size

$batchInsertSize (integer)

Number of rows to insert in a batch.

Default: 5000

Date Formats

$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:

Default: []

Defaults

$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

$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

Data Source File Default Extension

$extension (string 'xlsx')

The default extension used when a directory is specified in $this->file

Default: "xlsx"

Data Source File

$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"

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:

Hashable

$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

$header (boolean TRUE)

If the data source has headers in the first row, setting this to true will skip the first row.

Default: TRUE

Input Encodings

$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

$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:

Default: null

Column "Mapping"

$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

$offset (integer)

Number of rows to skip at the start of the data source, excluding the header row.

Default: 0

Output Encodings

$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

$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:

Default: []

Read Chunk Size

$readChunkSize (integer)

Number of rows to read per chunk.

Default: 5000

Skipper

$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.

Default: "%";

Skip Columns

$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: []

Skip Sheets

$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: []

Destination Table Name

$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

Text Output

$textOutput (boolean) or (string) or (array [])

Default: TRUE

Text Output Path

$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

$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 Destination Table

$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

Truncate Destination Table Ignoring Foreign Key Constraints

$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

Unix Timestamps

$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

$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

$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:

Default: []

Worksheet Table Mapping

$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: []

Details

Null values

Date/Time values

When the destination table column is a date/time type, the cell value is converted to a Date/Time format.

Examples

Table with specified timestamps and specified table name

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;

Worksheet to Table Mapping

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

Mapping

Map the worksheet or CSV headers to table columns, with the following CSV;

XLSX
1 Foo Bar
2 John Doe
CSV
1,Foo,Bar
2,John,Doe

Example:

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:

  1. add header columns to your multi-sheet workbooks
  2. use CSVs or single-sheet workbooks and create a separate seeder for each that need different column mappings

Aliases with defaults

Seed a table with aliases and default values, like this;

Skipper

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

To skip a worksheet in a workbook, prefix the worksheet name with '%' or the specified skipper prefix.

Validate

Validate each row of an XLSX or CSV like this;

Hash

Hash values when seeding an XLSX or CSV like this;

Input and Output Encodings

The mb_convert_encodings function is used to convert encodings.

Postgres Sequence Counters

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.

License

Excel Seeder for Laravel is open-sourced software licensed under the MIT license.

Changes

3.4.1

All versions of laravel-excel-seeder with dependencies

PHP Build Version
Package Version
Requires php Version >=7.1.3
laravel/framework Version >=5.8
phpoffice/phpspreadsheet Version ~1.7
doctrine/dbal Version ^2.6|^3.0
composer/semver Version *
Composer command for our command line client (download client) This client runs in each environment. You don't need a specific PHP version etc. The first 20 API calls are free. Standard composer command

The package bfinlay/laravel-excel-seeder contains the following files

Loading the files please wait ....