Laravel 9 Import Excel data into Database using maatwebsite package

Whatsapp

https://www.fundaofwebit.com/post/laravel-9-import-excel-data-into-database

How to Import Excel / CSV file data into database using maatwebsite package in Laravel 9


In this post, you are going to learn how to import or insert excel file data into database in laravel where we will be seeing the example of student records. Also we will be doing excel validation before import data into database. So lets get started.


Step 1: Create a Laravel 9 Application with the following command:

composer create-project laravel/laravel laravelproject

Step 2: Setup your database in .env file

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database
DB_USERNAME=your_username
DB_PASSWORD=your_password

Step 3: Install maatwebsite/excel Package with following command:

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

If you are using less than laravel 9 version, then install with below command:

composer require maatwebsite/excel


Step 4: Open config/app.php and add service provider and alias:

This Maatwebsite ExcelServiceProvider and Excel is aut-discovered and registered by default.

If you are using less than laravel 9 version, lets add it manually in the following path: config/app.php

'providers' => [
    ....
   Maatwebsite\Excel\ExcelServiceProvider::class,
],
'aliases' => [
    ....
   'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],

after adding the provider and aliases, run the below command to pushlish.

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


Step 5: Lets create students table with the following command:

php artisan make:migration create_students_table
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateStudentsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('students', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email');
            $table->string('phone');
            $table->string('course');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('students');
    }
}


Step 6: Lets create the model for student with the following command:

php artisan make:model Student

after creating Student model, add the attributes in the following path: app/Models/Student.php

<?php

namespace App\Models;

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

class Student extends Model
{
    use HasFactory;

    protected $table = 'students';

    protected $fillable = [
        'name',
        'email',
        'phone',
        'course',
    ];
}


Step 7: Now, migrate the table into database with the following command:

php artisan migrate

Step 8: Create Import Class with the following command:

php artisan make:import StudentsImport

After Import created successfully, paste the below code:

<?php

namespace App\Imports;

use App\Models\Student;
use Illuminate\Support\Collection;

use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class StudentsImport implements ToCollection, WithValidation, WithHeadingRow
{
    /**
    * @param Collection $collection
    */
    public function collection(Collection $rows)
    {
        foreach ($rows as $row)
        {
            Student::create([
                'name' => $row['name'],
                'email' => $row['email'],
                'phone' => $row['phone'],
                'course' => $row['course']
            ]);
        }
    }

    public function rules(): array
    {
        return [

            'name' => [

                'required',
                'string',
                'max:191'
            ],
            'email' => [

                'required',
                'email',
                'max:191'
            ],
            'phone' => [

                'required',
                'digits:10',
            ],
            'course' => [

                'required',
                'string',
                'max:191'
            ],
        ];
    }

}

Step 9: Create a student controller with the following command:

php artisan make:controller StudentController

After controller created successfully paste the below code:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use Maatwebsite\Excel\Facades\Excel;
use App\Imports\StudentsImport;
use Exception;

class StudentController extends Controller
{
    public function index()
    {
        return view('student.import');
    }

    public function importExcel(Request $request)
    {
        try {

            Excel::import(new StudentsImport, $request->file('student_import_file'));
            return redirect()->back()->with('message','Imported Successfully');

        } catch (Exception $ex) {

            return redirect()->back()->with('message','Something went wrong.! Please Check Whether Excel Is Empty Or Compare Excel Sample.');
        }
    }
}


Step 10: Define the routes in following path: routes/web.php

Route::get('students', [App\Http\Controllers\StudentController::class, 'index']);
Route::post('import-student', [App\Http\Controllers\StudentController::class, 'importExcel']);


Step 11: Create a file import.blade.php in student folder as follows: resources/views/student/import.blade.php and paste the below code.

@extends('layouts.app')

@section('content')

    <div class="container mt-4">
        <div class="card">
            <div class="card-header">
                <h4>Import Excel / CSV File data in Laravel</h4>
            </div>
            <div class="card-body">
                @if (session('message'))
                    <h6 class="alert alert-warning">{{ session('message') }}</h6>
                @endif

                <form action="{{ url('import-student') }}" method="POST" enctype="multipart/form-data">
                    @csrf

                    <div class="mb-3">
                        <input type="file" name="student_import_file" required class="form-control" />
                    </div>
                    <button type="submit" class="btn btn-success">Import Data</button>
                </form>
            </div>
        </div>
    </div>

@endsection


Step 12: That's it, now lets serve the application and import the data.

php artisan serve


Excel Format to upload it:



Thank you.

© Copyright - 2018 - 2022 | All rights reserved at Funda of Web IT