Today I discovered Laravel Excel. My favorite way to import a CSV or Excel file into your Mysql database using Laravel & Laravel Excel (A composer package for the Laravel Framework).

This short guide assumes you are familiar with:

  • Laravel
  • Laravel Controllers & Eloquent Models
  • Composer package manager

This method is by far the fastest and my favorite method available.

If you are not familiar with Laravel, please check it out at laravel.com & laracast.com


Step 1 - Setup Laravel-Excel

(Read all the docs in Github)

Add this package to your composer.json file.

"maatwebsite/excel": "~2.1.0"

After updating composer, add the ServiceProvider to the providers array in config/app.php

Maatwebsite\Excel\ExcelServiceProvider::class,

You can use the facade for shorter code. Add this to your aliases:

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

The class is bound to the ioC as excel

$excel = App::make('excel'); To publish the config settings in Laravel 5 use:

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

This will add an excel.php config file to your config folder.

Now we are ready to import the CSV file into you MYSQL database using Eloquent.

Step 2 - Importing the CSV to your Mysql

Sample CSV file contacts.cvs saved inside database/import/users.csv

    'id','name','email'
    1,daniel Farina,[email protected]
    2,Jonh Doe,[email protected]
    3,Amigo Farina,[email protected]

Load your model and Excel in your controller

use Maatwebsite\Excel\Facades\Excel;
use App\Models\User;

Create a new function inside one of your controllers, load the csv file, loop trough it and insert each line to the database:

    public function importContacts()
    {

        //load CSV file
        Excel::filter('chunk')->load(database_path('import/users.csv'))->chunk(250, function($results) {
            foreach ($results as $row) {

        /** @var CREATE  Conctat row*/
        $contact = new Contact;
        $contact->name = $row->name;
        $contact->email = $row->email;
        $backer->save();

            }
        });

    }

As you can see, with just one line of code you are able to load the entire csv file into object. After that all you need todo is start your eloquent model and inser a new record for each line. Clean!!! isnt?

This is how your entire controller should look like:

<?php

namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\User;

/**
 * Class DashboardController
 * @package App\Http\Controllers\Backend
 */
class ImportUsersController extends Controller
{

    public function importContacts()
    {

        //load CSV file
        Excel::filter('chunk')->load(database_path('import/users.csv'))->chunk(250, function($results) {
            foreach ($results as $row) {

        /** @var CREATE  Conctat row*/
        $contact = new Contact;
        $contact->name = $row->name;
        $contact->email = $row->email;
        $backer->save();

            }
        });

    }
}

Posted in Laravel, Composer Packages on Apr 06, 2016