How to Import Excel file data into database in Laravel 10

By Ved Prakash N | Jan 18, 2024 | Laravel
Share : Whatsapp

https://www.fundaofwebit.com/post/how-to-import-excel-file-data-into-database-in-laravel

How to Import Excel file data into database in Laravel 10

In this tutorial, I’ll guide you through the process of importing Excel files in Laravel using the popular Laravel Excel package also known as maatwebsite/excel package.

I have taken an example of Customer details to import data from excel sheet in database. Also how to update the existing data into database while excel import.


Step 1: Install the Laravel Application and setup the database. 

Step 2: Install Laravel Excel Package via composer as follows: 

composer require maatwebsite/excel


Step 3: Configure the Package

The Maatwebsite\Excel\ExcelServiceProvider is auto-discovered and registered by default.

If you want to register it yourself, add the ServiceProvider in "config/app.php": ( providers & aliases )

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

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


Step 4: To publish the config, run the vendor publish command

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


Step 5: Create the Migration file using the below artisan command:

php artisan make:migration create_customers_table

After successfully creating the Customer migration file, paste the below code in it - path : database/ migrations/ your_customers_table.php

<?php

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

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

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

now its migrate into the database using the following command: 

php artisan migrate


Step 6: Create a Excel Import Class named CustomerImport with the following command:

php artisan make:import CustomerImport

After successfully creating the Import Class, paste the below code in the following path : app/ Imports/ CustomerImport.php 

<?php

namespace App\Imports;

use App\Models\Customer;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class CustomerImport implements ToCollection, WithHeadingRow
{
    /**
    * @param Collection $collection
    */
    public function collection(Collection $rows)
    {
        foreach ($rows as $row)
        {
            $customer = Customer::where('email', $row['email'])->first();
            if($customer){

                $customer->update([
                    'name' => $row['name'],
                    'phone' => $row['phone'],
                ]);

            }else{

                Customer::create([
                    'name' => $row['name'],
                    'email' => $row['email'],
                    'phone' => $row['phone'],
                ]);
            }

        }
    }
}


Step 7: Create a Model named Customer using the below artisan command:

php artisan make:model Customer

After successfully creating the Customer model, paste the below code in it - path : app/ Models/ Customer.php 

<?php

namespace App\Models;

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

class Customer extends Model
{
    use HasFactory;

    protected $table = 'customers';

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


Step 8: Create a Controller named CustomerController using the below artisan command:

php artisan make:controller CustomerController

After successfully creating the CustomerController, paste the below code in it.

In this controller, write the logic of import excel as follows 

<?php

namespace App\Http\Controllers;

use App\Models\Customer;
use Illuminate\Http\Request;
use App\Imports\CustomerImport;
use Maatwebsite\Excel\Facades\Excel;

class CustomerController extends Controller
{
    public function index()
    {
        $customers = Customer::all();
        return view('customer.index', compact('customers'));
    }

    public function importExcelData(Request $request)
    {
        $request->validate([
            'import_file' => [
                'required',
                'file'
            ],
        ]);

        Excel::import(new CustomerImport, $request->file('import_file'));

        return redirect()->back()->with('status', 'Imported Successfully');
    }
}


Step 9: Create a Blade View

Create the blade file in the following path : resources/ views/ customer/ index.blade.php 

This Blade view includes a form with a file input field for uploading Excel files. It also displays a success message if the import is successful and also listed all the inserted records.

<x-app-layout>

    <div class="container">
        <div class="row justify-content-center">
            <div class="col-md-8 mt-5">

                @if (session('status'))
                    <div class="alert alert-success">{{ session('status') }}</div>
                @endif

                <div class="card">
                    <div class="card-header">
                        <h4>Import Excel Data into Database in Laravel 10</h4>
                    </div>
                    <div class="card-body">

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

                            <div class="input-group">
                                <input type="file" name="import_file" class="form-control" />
                                <button type="submit" class="btn btn-primary">Import</button>
                            </div>

                        </form>

                        <hr>

                        <table class="table table-bordered">
                            <thead>
                                <tr>
                                    <th>ID</th>
                                    <th>Name</th>
                                    <th>Email</th>
                                    <th>Phone</th>
                                </tr>
                            </thead>
                            <tbody>
                                @foreach ($customers as $item)
                                <tr>
                                    <td>{{$item->id}}</td>
                                    <td>{{$item->name}}</td>
                                    <td>{{$item->email}}</td>
                                    <td>{{$item->phone}}</td>
                                </tr>
                                @endforeach
                            </tbody>
                        </table>

                    </div>
                </div>
            </div>
        </div>
    </div>

</x-app-layout>


Step 10: Create a Route

Route::get('customer/import', [App\Http\Controllers\CustomerController::class, 'index']);
Route::post('customer/import', [App\Http\Controllers\CustomerController::class, 'importExcelData']);


Let's serve the application 

php artisan serve


Now, we are ready to import the excel file at : http://localhost:8000/customer/import 


I hope this helps you. Thanks for reading.