Laravel Load File 💽
A package to help with loading files into MySQL tables.
This uses MySQL's LOAD DATA statement to load text files quickly into your database.
This is usually 20 times faster than using INSERT statements according to: https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html
Options
This library currently can handle any of the options in a normal LOAD DATA statement except for the partitioned table support. This will be included in a future release of laravel-loadfile.
Further information on the following options that can be passed to the load file builder can be found here:
https://dev.mysql.com/doc/refman/8.0/en/load-data.html
Installation
Requires > PHP 8.0 and > Laravel 8
composer require ellgreen/laravel-loadfileLoading files
To use local files you will need to have local_infile enabled for
the client and server. To do this on the Laravel side you will need
to add the following to the options part of your database config:
'config' => [
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
],Simple file import
use EllGreen\LaravelLoadFile\Laravel\Facades\LoadFile;
LoadFile::file('/path/to/employees.csv', $local = true)
->into('employees')
->columns(['forename', 'surname', 'employee_id'])
->load();Ignoring header row
LoadFile::file('/path/to/employees.csv', $local = true)
->into('employees')
->columns(['forename', 'surname', 'employee_id'])
->ignoreLines(1)
->load();Specifying field options
LoadFile::file('/path/to/employees.csv', $local = true)
->into('employees')
->columns(['forename', 'surname', 'employee_id'])
// like this
->fieldsTerminatedBy(',')
->fieldsEscapedBy('\\\\')
->fieldsEnclosedBy('"')
// or
->fields(',', '\\\\', '"')
->load();Specifying line options
LoadFile::file('/path/to/employees.csv', $local = true)
->into('employees')
->columns(['forename', 'surname', 'employee_id'])
// like this
->linesStartingBy('')
->linesTerminatedBy('\\n')
// or
->lines('', '\\n')
->load();Input preprocessing (set)
LoadFile::file('/path/to/employees.csv', $local = true)
->into('employees')
->columns([
DB::raw('@forename'),
DB::raw('@surname'),
'employee_id',
])
->set([
'name' => DB::raw("concat(@forename, ' ', @surname)"),
])
->load();Using a different connection
LoadFile::connection('mysql')
->file('/path/to/employees.csv', $local = true)
->into('employees')
->columns(['forename', 'surname', 'employee_id'])
->load();Duplicate-key and error handling
LoadFile::connection('mysql')
->file('/path/to/employees.csv', $local = true)
->replace()
// or
->ignore()
->into('employees')
->load();Loading data into Eloquent Models
Simply add the LoadsFiles trait to your model like so:
use EllGreen\LaravelLoadFile\Laravel\Traits\LoadsFiles;
class User extends Model
{
use LoadsFiles;
}Then you can use the following method to load a file into that table:
User::loadFile('/path/to/users.csv', $local = true);Need to specify options to load the file with?
Add the following method to your Model
class User extends Model
{
use LoadsFiles;
public function loadFileOptions(Builder $builder): void
{
$builder
->fieldsTerminatedBy(',')
->ignoreLines(1);
}
}Or you can get an instance of the query builder on the fly
User::loadFileBuilder($file, $local)
->replace()
->ignoreLines(1)
->load();Development
Unit tests
composer test-unitAll tests
You will need to have docker installed for these.
composer check