Laravel Package to Help with Loading Files into MySQL Tables

 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.