How to Import data into MySQL database in Laravel 8

Last updated on by

Bulk importing data is a useful feature to add records stored in a file to the database. Data may be stored in – CSV, Excel, XML, ODS, etc format.

In this tutorial, I show how you can import CSV & Excel data to MySQL database using Laravel Excel package in Laravel 8 project.

How to Import data into MySQL database in Laravel 8

Contents

  1. Install Package
  2. Update app.php
  3. Publish package
  4. Database Configuration
  5. Create Table
  6. Model
  7. Create Import class
  8. Route
  9. Controller
  10. View
  11. Output
  12. Conclusion

1. Install Package

Requirement –

  • PHP: ^7.2\|^8.0
  • Laravel: ^5.8
  • PhpSpreadsheet: ^1.21
  • psr/simple-cache: ^1.0
  • PHP extension php_zip enabled
  • PHP extension php_xml enabled
  • PHP extension php_gd2 enabled
  • PHP extension php_iconv enabled
  • PHP extension php_simplexml enabled
  • PHP extension php_xmlreader enabled
  • PHP extension php_zlib enabled

Install the package using composer –

composer require maatwebsite/excel

If you are getting an error while executing the above command then execute the below command –

composer require psr/simple-cache:^1.0 maatwebsite/excel

After that again execute –

composer require maatwebsite/excel

2. Update app.php

  • Open config/app.php file.
  • Add the following Maatwebsite\Excel\ExcelServiceProvider::class in 'providers' –
'providers' => [
      ....
      ....
      ....  
      Maatwebsite\Excel\ExcelServiceProvider::class
];
  • Add the following 'Excel' => Maatwebsite\Excel\Facades\Excel::class in 'aliases' –
'aliases' => [
     .... 
     .... 
     .... 
     'Excel' => Maatwebsite\Excel\Facades\Excel::class
];

3. Publish package

Run the command –

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

This will create a new excel.php file in config/.

4. Database Configuration

Open .env file.

Specify the host, database name, username, and password.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

5. Create Table

  • Create a new table Employees using migration and add some records.
php artisan make:migration create_employees_table
  • Now, navigate to database/migrations/ folder from the project root.
  • Find a PHP file that ends with create_employees_table and open it.
  • Define the table structure in the up() method.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->smallInteger('age');
        $table->timestamps();
    });
}
  • Run the migration –
php artisan migrate
  • The table has been created.

6. Model

  • Create Employees Model.
php artisan make:model Employees
  • Open app/Models/Employees.php file.
  • Specify mass assignable Model attributes – username, name, email, and age using the $fillable property.

Completed Code

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email','age'
    ];
}

7. Create Import class

I am creating 2 import classes just for example purpose –

1. EmployeesImport Class –

php artisan make:import EmployeesImport --model=Employees
  • Open app/Imports/EmployeesImport.php file.
  • Class has 1 method –
    • model() – Read values from $row Array and insert a record if email id does not exists in the employees table otherwise return null.

NOTE – This import class start reading records from 1st row. I explained how to skip 1st row that contains heading in the next Import class.

CSV file with no heading row –

user1 User1 u1 user1@makitweb.com 28
user2 User2 u2 user2@makitweb.com 24

Completed Code

<?php
namespace App\Imports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\ToModel;

class EmployeesImport implements ToModel {
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row) {
 
       // Check email already exists
       $count = Employees::where('email',$row[2])->count();
       if($count > 0){
          return null;
       }
       return new Employees([
          'username' => $row[0],
          'name' => $row[1], 
          'email' => $row[2],
          'age' => $row[3],
       ]);
    }

}

2. Employees2Import Class –

php artisan make:import Employees2Import --model=Employees
  • Open app/Imports/Employees2Import.php file.
  • Class has 2 method –
    • collection() – Validate $rows Array data. If successfully validated then insert record if email id does not exists in the employees table otherwise return error.

NOTE – If  WithHeadingRow is implemented then $rows Arrays contains row heading as key names instead of index.

    • headingRow() – Return heading index position.

CSV file with heading row –

Username Name Email Age
yssyogesh Yogesh singh yogesh@makitweb.com 28
bsonarika Sonarika Bhadoria bsonarika@makitweb.com 28
vishal Vishal Sahu vishal@makitweb.com 31

Completed Code

<?php

namespace App\Imports;

use App\Models\Employees;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;

class Employees2Import implements ToCollection ,WithHeadingRow
{

    public function collection(Collection $rows){

       // Validate
       Validator::make($rows->toArray(), [
          '*.username' => 'required|string',
          '*.name' => 'required|string',
          '*.email' => 'required|email',
          '*.age' => 'required|integer',
       ],[
          '*.username.required'=> "The username field is required.",
          '*.username.string'=> "The username must be string.",
          '*.name.required'=> "The name field is required.",
          '*.name.string'=> "The name must be string.",
          '*.email.required'=> "The email field is required.",
          '*.email.email'=> "The email must be a valid email address.",
          '*.age.integer'=> "The age must be an integer."
       ])->validate();

       foreach ($rows as $row) {

          // Check email already exists
          $count = Employees::where('email',$row['email'])->count();
          if($count > 0){
             continue;
          }
          Employees::create([
             'username' => $row['username'],
             'name' => $row['name'], 
             'email' => $row['email'],
             'age' => $row['age'],
         ]);
       }
    }

    // Specify header row index position to skip
    public function headingRow(): int {
       return 1;
    }
}

8. Route

  • Open routes/web.php file.
  • Define 3 routes –
    • / – Load index view.
    • employees/importdata – Post route to Import data.
    • employees/validateandimportdata – Post route to validate and import data.

Completed Code

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index'])->name('home'); 
Route::post('employees/importdata/', [EmployeesController::class, 'importData'])->name('employees.importdata');
Route::post('employees/validateandimportdata/', [EmployeesController::class, 'validateAndImportdata'])->name('employees.validateandimportdata');

9. Controller

  • Create EmployeesController Controller.
php artisan make:controller EmployeesController
  • Open app/Http/Controllers/EmployeesController.php file.
  • Import EmployeesImport,  Employees2Import and Excel.
  • Create 3 methods –
    • index() – Load index view.
    • importdata() – To import call Excel::import().

Pass 2 parameters –

    1. EmployeesImport Class instance.
    2. Pass selected file ‘temp’ location.
    • validateAndImportdata() – Again call Excel::import().

Pass 2 parameters –

    1. Employees2Import Class instance.
    2. Pass stored file location (I have stored the file in public/employees.xlsx folder).

Completed Code

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Imports\EmployeesImport;
use App\Imports\Employees2Import;
use Excel;

class EmployeesController extends Controller
{
   public function index(){
      return view('index');
   }

   // Import data 
   public function importdata(Request $request){
      Excel::import(new EmployeesImport, $request->file('file')->store('temp'));
      return back()->with('success', 'Import successfully!');
   }

   // Validate and Import data 
   public function validateAndImportdata(Request $request){

      Excel::import(new Employees2Import, "employees.xlsx");
      return back()->with('success', 'Import successfully!');
   }

}

10. View

Create index.blade.php file in resources/views/ folder.

Create 2 <form> –

  • In the 1st <form > set action to {{ route('employees.importdata') }}. Create a file element and a submit button.
  • In the 2nd <form> set action to {{ route('employees.validateandimportdata') }}. Create a submit button.

Loop on $errors->all() to display errors.

Completed Code

<!DOCTYPE html>
<html>
<head>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <title>How to Import data into MySQL database in Laravel 8</title>

   <!-- CSS -->
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>

   <div class="container mt-5">

      <!-- Success message -->
      @if(Session::has('success'))
         <div class="alert alert-success">
            {{ Session::get('success') }}
         </div>
      @endif

      <form method='post' action="{{ route('employees.importdata') }}" enctype="multipart/form-data">
         @csrf
         <div class="mb-3">
            <label for="file" class="form-label">File</label>
            <input type="file" class="form-control" id="file" name="file" value="">
         </div>

         <button type="submit" class="btn btn-success">Import</button>
      </form>

      <!-- Import data with validation -->
      <h2 class='mt-5'>Validate and import data</h2>
      {{-- Display errors --}}
      @if (count($errors) > 0)
         <div class="row">
            <div class="col-md-12 ">
                <div class="alert alert-danger">
                   <ul>
                      @foreach($errors->all() as $error)
                         <li>{{ $error }} </li>
                      @endforeach 
                   </ul> 
                </div>
            </div>
         </div>
      @endif

      <form method='post' action="{{ route('employees.validateandimportdata') }}" >
         @csrf
         <button type="submit" class="btn btn-success">Import</button>
      </form>
   </div>

</body>
</html>

11. Output

View Output

12. Conclusion

Make sure to validate the record before inserting or updating records in the Import class. Specify WithHeadingRow in Import class only if the import file has a header row.

You can learn more about this package from here.

View this tutorial to know how to export data using the Laravel Excel package.

If you found this tutorial helpful then don't forget to share.

Leave a Comment

© 2016-2024 Makitweb, All rights reserved