Laravel Load File is 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 the laravel-loadfile.
Installation
Requires > PHP 8.0 and > Laravel 8
composer require ellgreen/laravel-loadfile
Loading 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();
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();
For more details, visit Github.
