Importing data from Excel to MySql

Usually, in order to complete our basic work, we need an outsider package. Preferably, we are discussing the Laravel-Excel module from Maatwebsite, Provides powerful tools for managing Excel imports in Laravel.

Run the following command to install Maatwebsite/Excel.

composer require maatwebsite/excel

Register Services in Providers and Aliases -

Place the following code in the config/app.php file.

Solution:

  
    'providers' => [
      .......
      .......
      .......
      Maatwebsite\Excel\ExcelServiceProvider::class,
      |
      | Here is where you can register web routes for your application. These
      | routes are loaded by the RouteServiceProvider within a group which
      | contains the "web" middleware group. Now create something great!
      |
      */

      Route::get('file-import-export', [UserController::class, 'fileImportExport']);
      Route::post('file-import', [UserController::class, 'fileImport'])->name('file-import');

Make Import Class:-

Now we need to create an import class. We can create a import class using following steps - Execute the following command:-

php artisan make:import UsersImport - model=User

Place the following code in the app/Imports/UsersImport.php file.

    
      <?php
      namespace App\Imports;
      use App\Models\User;
      use Illuminate\Support\Facades\Hash;
      use Maatwebsite\Excel\Concerns\ToModel;
      use Maatwebsite\Excel\Concerns\WithHeadingRow;

      class UsersImport implements ToModel
      {
          public function model(array $row)
          {
              return new User([
                  'name'     => $row[0],
                  'email'    => $row[1],
                  'password' => Hash::make($row[2])
              ]);
          }
      }

Create and Prepare Controller:-

Now we have reached to this step where we are going to create and prepare controller to import a CSV file. We will learn some steps to create controllers.

Execute the following command :- php artisan make:controller UserController

Place the following code in the app/Http/Controllers/UserController.php file.

    
      <?php
      namespace App\Http\Controllers;
      use Illuminate\Http\Request;
      use Maatwebsite\Excel\Facades\Excel;
      use App\Imports\UsersImport;
      class UserController extends Controller
      {
          public function fileImportExport()
          {
      return view('file-import');
          }
        
          public function fileImport(Request $request) 
          {
              Excel::import(new UsersImport, $request->file('file')->store('temp'));
              return back();
          } 
      }

Write Down Blade View:-

Now, we have arrived at the last step. Here we need to form the view for dealing with bringing in and sending out through the frontend.

Create a file import.blade.php to import csv file:

      
        <!DOCTYPE html>
        <html >
        <head>
            <meta charset="utf-8">
            <meta name="viewport" content="width=device-width, initial-scale=1">
            <title>Import Excel & CSV to Database in Laravel </title>
            <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
        </head>
        <body>
            <div class="container mt-5 text-center">
                <h2 class="mb-4">
                    Laravel  Import CSV & Excel to Database Example
                </h2>
                 <form action="{{ route('file-import') }}" method="POST" enctype="multipart/form-data">
                    @csrf
                    <div class="form-group mb-4" style="max-width: 500px; margin: 0 auto;">
                        <div class="custom-file text-left">
                            <input type="file" name="file" class="custom-file-input" id="customFile">
                            <label class="custom-file-label" for="customFile">Choose file</label>
                        </div>
                    </div>
                    <button class="btn btn-primary">Import data</button>
                </form>
            </div>
        </body>
        </html>

Now, run the following command to start your artisan server.

php artisan serve

Here is the endpoint that you can test:

http://localhost:8000/file-import-export Import CSV to Databse

Summary :-

So this was it, we have followed each and every step to import a CSV file into database using laravel. In this tutorial we threw light on importing the Excel & CSV file into the database with the maatwebsite/excel composer package.

Importing CSV/Excel files into database using Laravel