Incredibly powerful, efficient, unlimited number of rows, queues based Excel importer dependency for Laravel Enso.
This package works exclusively within the Enso ecosystem.
The front end assets that utilize this api are present in the ui package.
For live examples and demos, you may visit laravel-enso.com
click on the photo to view a short demo in compatible browsers
Comes pre-installed in Enso.
- allows the import of big files with the number of rows only limited by the xlsx file format, by splitting the data in chunks and handling them on multiple queues
- uses JSON templates to configure
xlsxfile imports into the application, with minimum custom logic
- import types are defined in the package configuration
- each import type can be validated against required columns, sheets, data types and more
- the Laravel validation is utilized for maximum reuse of existing mechanisms while custom validators can be added when necessary
- an example import type is included by default in the package
- uses Spout for reading the
- uses Laravel's queueing system and its auto-balancing features for efficient asynchronous, parallel processing
- blocking file structure validation
- non blocking file contents validation
- content import issues are made available in the rejected rows summary, a downloadable
xlsxfile with the same structure as the import file, with an extra column (on each sheet) that will describe all the validation errors for each row
- features real time import progress reporting in the UI
afterhooks which are available during the importing process
- comes with an utility ExcelSeeder class, that can be used to seed your tables using data from excel files
Publish the configuration, example classes and assets:
php artisan vendor:publish --tag=dataimport-config
php artisan vendor:publish --tag=dataimport-examples
In the library
vendor/laravel-enso/dataimport/samples, we have included a couple of sample import files for you to play with. It is strongly recommended that you check out the provided samples before attempting to create your own importers.
The configuration can be found/published in
config/enso/imports.php and contains:
string, flag that sets whether import template validations are also executed in production, valid values are
1000, the number of records in a chunk. It should be adjusted for optimum performance on your machine. Note that the size can also be given in the import template, thus overriding the global value
split, the name of the queue for the chunk splitting job. Can also be set individually, for each import, in its template
array, the configuration for all the queues used during the import process. Note that it's good practice to have more processes for the splitting queue as this is an intensive process, and it needs to keep the other queues 'busy'. Obviously, these queues must be set up in the Laravel
60 * 5, the Laravel job timeout for the splitting and the rejected summary report generation jobs
_errorsthe name of the error column used to report issues with the import rows (which appears in the rejected summary xlsx file)
['broadcast', 'database'], the list of channels used to notify the user
configs- configuration array, with what's needed to hook the JSON templates to the import package:
label- the label visible to the user in the interface
template- the relative path to the JSON import templates
JSON Template structure:
number, optional, local overriding configuration for the
string, optional, name of the queue used to run the import on
array, optional, array of parameter objects, that can be passed to an importer class
array, required, array of sheet configuration objects | required
Note that the importer expects to find just the sheets given in the template, meaning it will report an error if there are missing sheets but also if there are extra sheets.
Params Configuration object structure:
The configuration object attributes are similar in type and structure to the parameters used within the JSON Form Builder template to configure a form field.
string, the name of the parameter as it is passed to the importer class, within the
validations, string, the Laravel style validations to be applied to the respective parameter
string, the label used for the field generated for the parameter,
mixed, the default, starting value for the parameter,
string, type of the input generated for this parameter,
string, the route for the select type parameter
object, any parameters applied for the input generated for this parameter
Sheet Configuration object structure:
string, required, the name of the sheet. Should be lower snake cased if the sheet name contains spaces, so use
string, required, the fully qualified importer class name. The class contains the actual the import logic.
string, optional, the fully qualified custom validator class name, if you are using custom validators
number, optional, the size of the chunk used during splitting
array, required, contains the column configuration objects
Column Configuration object structure:
string, required, the name of the column. Similar to the sheet name, column names should be lower snake cased, so use
string, optional, the desired Laravel (Request) validation that you want applied for this column
Please note that the import does not continue if structure errors are encountered, such as missing sheets or columns. If there are no structure errors and content errors are found, the rows with errors are skipped and valid rows are imported.
The importer class
The importer class given in the JSON template is responsible for doing the actual importing, once the file has been validated.
Your importer class needs to implement the
and implement the
run method receives a row object, which you can use to implement your import logic.
If you need pre/post import logic for your import, you can then also implement the following interfaces:
LaravelEnso\DataImport\app\Contracts\AfterHook- requires that you implement the
LaravelEnso\DataImport\app\Contracts\BeforeHook- requires that you implement the
Inside the two methods you can add your extra logic.
If you must have the user that is requesting the import available during the import,
you may also add the
LaravelEnso\DataImport\app\Contracts\Authenticates marker interface on your
The custom validator class
There might be cases where it's not enough to use the Laravel Request Validation methods. For more complex scenarios you can create a custom validator class and declare it your template.
The validator class must extend the
abstract class and implement the
Here you'll have access to the parent class'
addError(string $error) method.
You may use it to add any required issues for the data that fails your custom validation logic.
The rejected rows summary file
You will notice that the summary file has an identical structure with the import file, with the exception of one last column, the errors column, that is used to present the issues for each row.
The idea behind creating this kind of report file is for the user to be able to get a list of import issues in a format that allows him to quickly correct any errors in the file, delete the errors column and then simply re-import the summary file.
When seeding your database, you may use regular seeders and fill your tables with random data (as configured) but when you have specific data, you may want to set the actual data from the beginning. While there's more than one way to achieve this, the Excel Seeder helper allows you to seed your table by using data provided in an excel file, through an import
- basically it's a seeder adapter for a regular data import.
There are multiple advantages to this:
- the data is present in an excel file, and can be added and updated by almost any person
- even after initially seeding the database, since the import is going to remain available, additional files can be later imported for updates
- create a data import, following your usual flow
- create your excel seeder class, that will be using the import you added at step 1
- save it on the
- note that your class must extend the
- inside, add the
$typeparameter and use for value the type of the import created at the first step
- save it on the
- add the actual excel file containing the data to be used when seeding
- the file should be placed on the
- the name of the file needs to be the type of the import, and and must have an
- since you're going to be committing the file to the repository,
don't forget to add it to the
- the file should be placed on the
- run the seed process with
php artisan db:seed
- the seeder will run the import process & the data will be available in the database
- you'll also be able to see the result of the import in the data imports index page
php artisan vendor:publish --tag=data-import-config- configuration files
php artisan vendor:publish --tag=data-import-examples- example import
php artisan vendor:publish --tag=data-import-factory- the factory for the DataImport model
php artisan vendor:publish --tag=enso-factories- a common alias for publishing all enso factories, once a newer version is released, can be used with the
php artisan vendor:publish --tag=data-import-mail- the email templates
php artisan vendor:publish --tag=enso-config- a common alias for when wanting to update configuration, once a newer version is released, can be used with the
php artisan vendor:publish --tag=enso-mail- a common alias for when wanting to update the email templates, once a newer version is released, can be used with the
are welcome. Pull requests are great, but issues are good too.
This package is released under the MIT license.