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.