MySQL Load Data Infile Support For Laravel

Overview

Laravel Load File 💽

Latest Stable Version License

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
Issues
Releases(1.2.0)
Owner
Ellis Green
Ellis Green
MySQL Spatial Data Extension integration with Laravel.

Laravel MySQL Spatial extension Laravel package to easily work with MySQL Spatial Data Types and MySQL Spatial Functions. Please check the documentati

Joseph Estefane 613 Jun 10, 2021
PHP version of mysqldump cli that comes with MySQL

MySQLDump - PHP Requirements | Installing | Getting started | API | Settings | PDO Settings | TODO | License | Credits This is a php version of mysqld

diego torres 1000 Jun 17, 2021
A MongoDB based Eloquent model and Query builder for Laravel (Moloquent)

Laravel MongoDB This package adds functionalities to the Eloquent model and Query builder for MongoDB, using the original Laravel API. This library ex

Jens Segers 5.6k Jun 13, 2021
Spot v2.x DataMapper built on top of Doctrine's Database Abstraction Layer

Spot DataMapper ORM v2.0 Spot v2.x is built on the Doctrine DBAL, and targets PHP 5.4+. The aim of Spot is to be a lightweight DataMapper alternative

Spot ORM 598 Jun 11, 2021
Driver to seamlessly integrate the Backup Manager into Laravel applications.

Laravel Driver for the Database Backup Manager This package pulls in the framework agnostic Backup Manager and provides seamless integration with Lara

Backup Manager 616 Jun 7, 2021
A package to backup your Laravel app

A modern backup solution for Laravel apps This Laravel package creates a backup of your application. The backup is a zip file that contains all files

Spatie 4.5k Jun 13, 2021
Oracle DB driver for Laravel 4|5|6|7|8 via OCI8

Oracle DB driver for Laravel 4|5|6|7|8 via OCI8 Laravel-OCI8 Laravel-OCI8 is an Oracle Database Driver package for Laravel. Laravel-OCI8 is an extensi

Arjay Angeles 651 Jun 13, 2021
Laravel Migrations Generator: Automatically generate your migrations from an existing database schema.

Laravel Migrations Generator Generate Laravel Migrations from an existing database, including indexes and foreign keys! Upgrading to Laravel 5.4 Pleas

Bernhard Breytenbach 3.2k Jun 13, 2021
Support for many missing PostgreSQL specific features

Laravel supports many different databases and therefore has to limit itself to the lowest common denominator of all databases. PostgreSQL, however, of

Tobias Petry 8 Apr 27, 2021
A Redis based, fully automated and scalable database cache layer for Laravel

Lada Cache A Redis based, fully automated and scalable database cache layer for Laravel Contributors wanted! Have a look at the open issues and send m

Matt 456 Jun 16, 2021
Laravel 5 - Repositories to abstract the database layer

Laravel 5 Repositories Laravel 5 Repositories is used to abstract the data layer, making our application more flexible to maintain. See versions: 1.0.

Anderson Andrade 3.8k Jun 17, 2021
Symfony 5.2 + api platform project with ELK stack + elastic FileBeats for the log management. All running in 7 docker containers: nginx, php 8, mysql, elastic search, logstash, kibana, fileBeats.

Symfony with ELK and Elastic FileBeats Stack Prerequisites: Make sure that docker and docker-compose are installed in your machine and available for y

null 5 Apr 19, 2021
ATK Data - Data Access Framework for high-latency databases (Cloud SQL/NoSQL).

ATK Data - Data Model Abstraction for Agile Toolkit Agile Toolkit is a Low Code framework written in PHP. Agile UI implement server side rendering eng

Agile Toolkit 245 Jun 14, 2021
PHP DataMapper, ORM

Cycle ORM Cycle is PHP DataMapper, ORM and Data Modelling engine designed to safely work in classic and daemonized PHP applications (like RoadRunner).

Cycle ORM 852 Jun 17, 2021