How to import Excel File in Laravel and Insert Data in the Database

Question:

How to import Excel File in Laravel and Insert Data in the Database?

Solution:

In Laravel, there is a Laravel Excel to manage Excel File in Laravel. The Laravel Excel documentation is in this link - https://github.com/Maatwebsite/Laravel-Excel

Let's start our code:

1) Insert Laravel Excel to our Project According to their installation rules.

So, commands are-
1) Run command to insert Laravel Excel to pur project

composer require "maatwebsite/excel:~2.1.0"

2) Then add the ServiceProvider to the providers array in config/app.php

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

3) Then add Aliases to the providers array in config/app.php , aliases section

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

Laravel Excel is set upped completely.

So, now in this example, we'll use a Student Database and Create an Excel File for students Data and insert them in the Database.

Setup Database in .env file:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=LaravelExcelDemo
DB_USERNAME=root
DB_PASSWORD=

Create a Model for Student Data.

php artisan make:model Student --migration

Now in Students Migration Table in database/migrations

public function up()
 {
    Schema::create('students', function (Blueprint $table) {
         $table->increments('id');
         $table->string('name', 50);
         $table->string('email', 50);
         $table->string('phone', 20);
    });
 }

In Laravel 5.4+ we/ve to add default String Length 191 in App/Providers>AppServiceProvider, use class in top first

use Illuminate\Support\Facades\Schema;

Then change the boot function empty to this according to Laravel Documentation

public function boot()
 {
    Schema::defaultStringLength(191);
 }

Run the Artisan migrate command,

php artisan migrate

Create a controller for our Demo Example,

php artisan make:controller StudentController

Set up our routes in routes.php or routes/web.php

Route::get('/', 'StudentController@index')->name('index');
Route::post('import', 'StudentController@import')->name('import');

The Main StudentController Class has the following line s of code

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use Session;
use Excel;
use File;

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

 public function import(Request $request){
     //validate the xls file
  $this->validate($request, array(
   'file'      => 'required'
  ));

  if($request->hasFile('file')){
   $extension = File::extension($request->file->getClientOriginalName());
   if ($extension == "xlsx" || $extension == "xls" || $extension == "csv") {

    $path = $request->file->getRealPath();
    $data = Excel::load($path, function($reader) {
    })->get();
    if(!empty($data) && $data->count()){

     foreach ($data as $key => $value) {
      $insert[] = [
      'name' => $value->name,
      'email' => $value->email,
      'phone' => $value->phone,
      ];
     }

     if(!empty($insert)){

      $insertData = DB::table('students')->insert($insert);
      if ($insertData) {
       Session::flash('success', 'Your Data has successfully imported');
      }else {                        
       Session::flash('error', 'Error inserting the data..');
       return back();
      }
     }
    }

    return back();

   }else {
    Session::flash('error', 'File is a '.$extension.' file.!! Please upload a valid xls/csv file..!!');
    return back();
   }
  }
 }

 

 
}

So, for our view, the view file is

<!doctype html>
<html lang="{{ app()->getLocale() }}">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="{{ URL::to('css/app.css') }}">

    <title>Laravel Excel Import csv and XLS file in Database</title>

    <!-- Fonts -->
    <link href="https://fonts.googleapis.com/css?family=Raleway:100,600" rel="stylesheet" type="text/css">

    <!-- Styles -->
    <style>
    html, body {
        background-color: #fff;
        color: #636b6f;
        font-family: 'Raleway', sans-serif;
        font-weight: 100;
        height: 100vh;
        margin: 0;
        padding: 5%
    }
</style>
</head>
<body>
    <div class="container">


        <h2 class="text-center">
            Laravel Excel/CSV Import
        </h2>

        @if ( Session::has('success') )
        <div class="alert alert-success alert-dismissible" role="alert">
          <button type="button" class="close" data-dismiss="alert" aria-label="Close">
            <span aria-hidden="true">&times;</span>
            <span class="sr-only">Close</span>
        </button>
        <strong>{{ Session::get('success') }}</strong>
    </div>
    @endif

    @if ( Session::has('error') )
    <div class="alert alert-danger alert-dismissible" role="alert">
        <button type="button" class="close" data-dismiss="alert" aria-label="Close">
            <span aria-hidden="true">&times;</span>
            <span class="sr-only">Close</span>
        </button>
        <strong>{{ Session::get('error') }}</strong>
    </div>
    @endif

    @if (count($errors) > 0)
    <div class="alert alert-danger">
      <a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
      <div>
        @foreach ($errors->all() as $error)
        <p>{{ $error }}</p>
        @endforeach
    </div>
</div>
@endif



<form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
    {{ csrf_field() }}
    Choose your xls/csv File : <input type="file" name="file" class="form-control">

    <input type="submit" class="btn btn-primary btn-lg" style="margin-top: 3%">
</form>

</div>
</body>
</html>

Everything is complete for Importing data to Database from Excel File.

Implementation:
Take an Excel File like this -

Now choose a file and Click Import the file, then your data's will be inserted to database.

So, now look at your database students table,

Great !! That's Work. We've successfully imported the data to database from an Excel file.
You can check also that for Bad file or others, then you'll get the error, that's also set upped.

So Code Explanation Now :

So, Look at our import function in StudentsController, that's the main part-

 public function import(Request $request){
     //validate the xls file
  $this->validate($request, array(
   'file'      => 'required'
  ));

  if($request->hasFile('file')){
   $extension = File::extension($request->file->getClientOriginalName());
   if ($extension == "xlsx" || $extension == "xls" || $extension == "csv") {

    $path = $request->file->getRealPath();
    $data = Excel::load($path, function($reader) {
    })->get();
    if(!empty($data) && $data->count()){

     foreach ($data as $key => $value) {
      $insert[] = [
      'name' => $value->name,
      'email' => $value->email,
      'phone' => $value->phone,
      ];
     }

     if(!empty($insert)){

      $insertData = DB::table('students')->insert($insert);
      if ($insertData) {
       Session::flash('success', 'Your Data has successfully imported');
      }else {                        
       Session::flash('error', 'Error inserting the data..');
       return back();
      }
     }
    }

    return back();

   }else {
    Session::flash('error', 'File is a '.$extension.' file.!! Please upload a valid xls/csv file..!!');
    return back();
   }
  }
 }

Here,
1) We'ev check the validation for the file first is required or not.

2) Then if someone entered file, check th file Extension and if file extension is xls, xlsx or csv then do the rest of the job.

  if($request->hasFile('file')){
   $extension = File::extension($request->file->getClientOriginalName());
   if ($extension == "xlsx" || $extension == "xls" || $extension == "csv") {

3) Then get file path and load the file path in Laravel excel. Check the data row by row and add them in insert[] array.

    $path = $request->file->getRealPath();
    $data = Excel::load($path, function($reader) {
    })->get();
    if(!empty($data) && $data->count()){

     foreach ($data as $key => $value) {
      $insert[] = [
      'name' => $value->name,
      'email' => $value->email,
      'phone' => $value->phone,
      ];
     }

4) If insert [] array is not empty then, insert the data's in the database.

if(!empty($insert)){

      $insertData = DB::table('students')->insert($insert);
      if ($insertData) {
       Session::flash('success', 'Your Data has successfully imported');
      }else {                        
       Session::flash('error', 'Error inserting the data..');
       return back();
      }
     }

And in View File :

Just remember that enctype="multipart/form-data" and method="post" and route is import which is created in our route. Then take the file.

<form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
    {{ csrf_field() }}
    Choose your xls/csv File : <input type="file" name="file" class="form-control">

    <input type="submit" class="btn btn-primary btn-lg" style="margin-top: 3%">
</form>

So, that's the Laravel Excel File import. If you've any problem and qustion, just let us know.

Download Full Source Codes with Database Files.

Note:
If you face any problem, just comment below. I'm here to help you.

Tags:
How to import Excel File in Laravel and Insert Data in the Database, Laravel Excel, Excel File insert to database, Laravel Excel Example, Laravel Excel insert in the Database.

5 comments:

AnonymousOctober 21, 2018 at 11:53 PM
Are you able to add some validation to your code to check for duplicate records in the csv/xls and return a notification?
SupaMonkeyJanuary 2, 2019 at 7:30 AM
What about when you have multiple models in a single sheet of data? eg: row 1, columns A-E = model1 data (eg: student model); F-H = model2 data (eg: student contact data), i-k = model3 data (eg: student courses data), etc etc.?
UnknownNovember 1, 2019 at 1:42 AM
Symfony\Component\Debug\Exception\FatalThrowableError
Call to undefined method Maatwebsite\Excel\Excel::load() generate error
yaseminAugust 22, 2023 at 9:39 AM
karşıyaka transfer
akbük transfer
balıkesir transfer
ayvalık transfer
aliağa transfer

1BF4F