How to import Excel file to database in Laravel

Hi, Gurpreet this side again with some new stuff. Today we are going to learn about excel file import into database using a package called Laravel Excel and then I’ll be doing all this stuff practically in front of you. Sometimes, We do have requirement to insert some data through CSV or excel file then in any way you can use this package. Let’s move on best part which is how to use it.

As I have said I’ll be teaching you how to import data through excel file in database, the same package you can use to export data from database to excel. Let’s see how we can do this. Btw I am just going to show you how to import excel.

Create A File upload Form

First of all, I will explaining all of that straight forward and I am also assuming that you have prior knowledge of Laravel installation and setting up all the things. Because If I’ll be straight forward It will take less time to you to understand.

First of all We will create a form.

//You can keep this form anywhere in your blade file

<form action="{{ route('uploadusers') }}" enctype="multipart/form-data" method="POST">
                                    @csrf
                                    <div class="col-lg-12 py-3">
                                        <label for="users">Upload Users File</label>
                                        <input type="file" class="form-control" style="padding: 3px;" name="users" required />
                                        </div>
                                        <button type="submit" class="btn btn-success" name="upload">Upload</button>
                                </form>

File upload form

Make A Route To Visit Form page

Now, We have to make a route so that we can visit to the form page. Also if you want to can give some style to this form as I have already did in the image. Let’s create a web route in web.php file.

//for visiting the form page
Route::view('/upload-form','fileupload');

//for uploading to database
Route::post('/upload-form/fileupload',[UsersController::class,'upload'])->name('uploadusers');

//we have to create FileManagerController

Make a Controller

Now, we have to make a controller so that we can do all the action which we want to do, I mean so that we can import excel file data to database.

Remember, we haven’t installed the package yet and let’s do that also and before that we will create controller. So, that we would have clear vision what we have to do.

php artisan make:controller UsersController

Install The Package ( Laravel-Excel)

Now, we are finally going to install the package. So, there is a little conflict in my mind what is the name of the package. Because when we install this package then we use Maatwebsite in command and otherwise website name is Laravel-excel. So, whatever let’s see how we can install the package.

composer require maatwebsite/excel

If above command fails in Laravel 9 then they said that you can use the below command.

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

After installing the package, It’s auto registered itself in service provider. But If it not like that do it manually, It’s not a big task. Go to config in app.php and list it in providers array.

'providers' => [
    /*
     * Package Service Providers...
     */
    Maatwebsite\Excel\ExcelServiceProvider::class,
]

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

And after that just finish it by publishing the config.

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

Create Import class

After installing the package this is important to move on important part where we gonna do whole task. In your App directory you might have imports directory provided by the package. If not then you can simply run the below command to create an import class. Because when we do import data then we create import class and when we do export data then we create export command.

php artisan make:import UsersImport --model=User

Now we would have a file in our App/Imports directory called UsersImports.

I am assuming that we have three fields in our excel file. name, email and password.

<?php

namespace App\Imports;

use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;

class UsersImport implements ToModel
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new User([
            'name' => $row['name'],
'email'=>$row['email'],
'password'=>Hash::make($row['password']),
        ]);
    }
}

Now, I assumed that you have three fields in your excel file and it can be more than three, It just an example. And also It can be more complex than this if it is don’t forget to share your experience in comment below.

Let’s create Controller’s stuff to make sure we will be able to make this done.

Store via Controller

We are going to create a function in our already exists controller called “upload”.

  public function upload(Request $request)
    {
        request()->validate([
            'users' => 'required|mimes:xlx,xls|max:2048'
        ]);
        Excel::import(new UsersImport, $request->file('users'));

        return back()->with('massage', 'User Imported Successfully');
    }

You also can assign some other queries to imported user if you want and if you also want to read how to make your first crud in Laravel then you can subscibe to this website and if you are a Laraveler too.

Conclusion

This is how you can import users or any kind of data through excel. This is very helpful package I personally have used it in my freelance project. I would recommend if you are doing the same thing with your project like if you are importing such data files then you can easily use it.

Thanks for reading, I hope it will help you.

The post How to import Excel file to database in Laravel appeared first on Larachamp.