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 Laravel 6 or above
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();
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-unit
All tests
You will need to have docker installed for these.
composer check
1.1k Jan 8, 2023
13 May 20, 2022
97 Dec 29, 2022
547 Dec 27, 2022
6 Jan 17, 2021
35 Nov 11, 2022
2 Sep 28, 2021
4 Feb 22, 2022
4 Jun 14, 2022
741 Jan 9, 2023
3 Aug 5, 2022
257 Dec 29, 2022
20 Jun 23, 2021
311 Dec 28, 2022
9 Jul 15, 2022
10 Nov 1, 2022
424 Dec 12, 2022
6.4k Jan 5, 2023