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.
Contents
- Install Package
- Update app.php
- Publish package
- Database Configuration
- Create Table
- Model
- Create Import class
- Route
- Controller
- View
- Output
- 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 theemployees
table otherwise return null.
- model() – Read values from
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 theemployees
table otherwise return error.
- collection() – Validate
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 | 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()
.
- index() – Load
Pass 2 parameters –
-
EmployeesImport
Class instance.- Pass selected file ‘temp’ location.
-
- validateAndImportdata() – Again call
Excel::import()
.
- validateAndImportdata() – Again call
Pass 2 parameters –
-
Employees2Import
Class instance.- 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
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.