Welcome to the Laravel Import Export CSV Database Example tutorial.
We will use Maatwebsiteexcel Package to export and import CSV files in laravel.
Sometimes we need to share our database record with clients so we can export our data in CSV file in laravel from MySQL database.
Similarly, we can import data from the CSV file into the MySQL database in laravel.
Some Output Views
Below are some images as the final example’s working functionalities.
When the user clicks the “Export Employee Data” button, the system will allow him to download the CSV file.
This CSV file will contain all the data records from the “employee” table.
The above image is the CSV file that a user has exported from the laravel project and the MySQL database.
On the click event of the Browse button, the computer will ask a user to select a CSV file to import on a database.
We will import the above CSV file in the database. It has two headers: name and email.
This file contains two rows. Look at the below image. These two data row records are imported into the MySQL database.
Now let us create our example step by step.
Step 1. Project and Database Creation
First of all, run the below command to make a new laravel project.
laravel new laraimex
Now go to phpMyAdmin or sequel pro database management tool and make a new database with the name as ” laraimex ”
Go to the code editor tool and open your laravel project in it.
Then open your .env file and see the below lines in it
DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=laraimex DB_USERNAME=root DB_PASSWORD=
These lines are the configuration details of the database.
You should modify the above details as per your computer’s settings.
Once you do it, you have successfully linked your database and laravel project with each other.
Step 2. Download maatwebsite/excel
In this example, we will use one external package to simplify our process of coding.
This package is: maatwebsite/excel package. To integrate it in our laravel project, run the below command
composer require maatwebsite/excel
Once the command line downloads it completely, go to the config->app.php file and find “provides” array.
Add the following line in “provides” array.
Maatwebsite\Excel\ExcelServiceProvider::class,
There should be another array “aliases“, write the below in it
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
Now again go to the command line and fire the following
php artisan vendor:publish
This command will help us to publish the changes we have made in the app.php file.
You will see the following lines when firing the publish command.
Step 3. Making Migration and Table
First of all, write the below command
php artisan make:migration create_employees_table
A migration file will be added by the above command.
You can find that file at app->database->migrations timestamp_create_employees_table.php.
Write down the below source liens inside this migration file.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateEmployeesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('email')->unique();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('employees');
}
}
The system uses this migration file to find out the column names and types to be added to the table.
We have defined column names and type inside the up() function.
To create the “employees” table in the MySQL database, run the below command
php artisan migrate
After the execution of the above command, you should see the “employees” table in your database.
Now to create the model “Employee.php“, run the following
php artisan make:model Employee
Now navigate to the app->Employee.php class. Add the below code block inside it
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Employee extends Model
{
//
public $table = "employees";
protected $fillable = [
'name', 'email',
];
}
Step 4. Testing Data
We require some fake data in our employees table to test this example.
For this, we will use the factory to generate dummy data. So go to the app->database->factories->UserFactory.php file and write the below in it
$factory->define(Employee::class, function (Faker $faker) {
return [
'name' => $faker->name,
'email' => $faker->unique()->safeEmail,
];
});
Also, add the below line at the top of the file,
use App\Employee;
Final source code for UserFactory.php file is as the below
<?php
/** @var \Illuminate\Database\Eloquent\Factory $factory */
use App\User;
use App\Employee;
use Faker\Generator as Faker;
use Illuminate\Support\Str;
/*
|--------------------------------------------------------------------------
| Model Factories
|--------------------------------------------------------------------------
|
| This directory should contain each of the model factory definitions for
| your application. Factories provide a convenient way to generate new
| model instances for testing / seeding your application's database.
|
*/
$factory->define(User::class, function (Faker $faker) {
return [
'name' => $faker->name,
'email' => $faker->unique()->safeEmail,
'email_verified_at' => now(),
'password' => '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', // password
'remember_token' => Str::random(10),
];
});
$factory->define(Employee::class, function (Faker $faker) {
return [
'name' => $faker->name,
'email' => $faker->unique()->safeEmail,
];
});
Now run the below command,
php artisan tinker
Again run the below command quickly
factory(App\Employee::class, 50)->create();
This command will create 50 dummy data rows into the “employees” table.
Step 5. Important Files For CSV
Start by using the below command
php artisan make:import EmployeesImport --model=Employee
This command will create a file EmployeesImport.php in the app->imports folder.
Write the following source snippet in the EmployeesImport.php file.
<?php
namespace App\Imports;
use App\Employee;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class EmployeesImport implements ToModel, WithHeadingRow
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Employee([
//
// print_r($row)
'name' => $row['name'],
'email' => $row['email'],
]);
}
}
This class will help us to import the data from the CSV file which has two headers : name and email.
Time to run the below command
php artisan make:export EmployeesExport --model=Employee
It will let you access the “app/Exports/EmployeesExport.php” file.
You need to write these lines into the EmployeesExport.php” file.
<?php
namespace App\Exports;
use App\Employee;
use Maatwebsite\Excel\Concerns\FromCollection;
class EmployeesExport implements FromCollection
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return Employee::all();
}
public function headings(): array
{
return [
'name',
'email',
];
}
}
This file will fetch all the data records from the “employees” table and then it will create the CSV file with those records.
Users can download a CSV file as an export action.
Step 6. Ending Files
First of all, go to the app->resources->views->web.php file.
Add the below lines of code into this web.php file.
Route::get('showscreen', 'ExImController@showSceen');
Route::get('export', 'ExImController@exportData')->name('export');
Route::post('import', 'ExImController@importData')->name('import');
To create the controller file, hit the following command
php artisan make:controller ExImController
Now navigate to the app/Http/Controllers/ExImController.php file.
Below are the coding lines for this controller file.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Imports\EmployeesImport;
use Maatwebsite\Excel\Facades\Excel;
class ExImController extends Controller
{
//
public function showSceen()
{
return view('showscreen');
}
public function exportData()
{
return Excel::download(new EmployeesExport, 'employees.xlsx');
}
public function importData()
{
$path1 = request()->file('file')->store('file');
$path=storage_path('app').'/'.$path1;
Excel::import(new EmployeesImport,$path);
}
}
Open app->resources->views directory and make a new file named showscreen.blade.php.
Just add the below lines in this showscreen.blade.php file.
<!DOCTYPE html>
<html>
<head>
<title>Laravel Export Import Excel CSV</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
</head>
<body>
<div class="container">
<div class="card bg-light mt-3">
<div class="card-header">
Laravel Export Import Excel CSV
</div>
<div class="card-body">
<form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
@csrf
<input type="file" name="file" class="form-control">
<br>
<button class="btn btn-success">Import Employee Data</button>
<a class="btn btn-warning" href="{{ route('export') }}">Export Employee Data</a>
</form>
</div>
</div>
</div>
</body>
</html>
Okay, we have written all the required files and codes. Just run the “php artisan serve” command to test the example.