Laravel
1130
w3alert.com
14-01-2020
In this article, you will learn how to import Excel or CSV data into MySQL database in the laravel web application using the maatwebsite package with an example.
When you work with any Laravel web application. So you may need to upload data from MySQL database in laravel web application with many different formats like CSV and Excel.
In this tutorial, we will show in an easy and simple way to import data into mysql database using Excel and CSV files in Laravel web applications.
First Step, you need to download or install laravel web application new setup on your local machine. you can use the following command to download or install laravel application new setup:
composer create-project laravel/laravel laravel-import –-prefer-dist
If you want to install a specific version of laravel, you can follow the following tutorial
https://w3alert.com/laravel-tutorial/laravel-install-on-windows-ubuntu-with-composer
After that, you will set up the database credentials in .env file. So open your .env file and update database credentials following:
DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=database_name DB_USERNAME=root DB_PASSWORD=root
Before you will run php artisan migrate command. Go to app/providers/AppServiceProvider.php and update the below code :
..
use Illuminate\Support\Facades\Schema;
....
function boot()
{
Schema::defaultStringLength(191);
}
...
Next, go to command prompt and run the following command:
php artisan migrate OR php artisan migrate:fresh
This php artisan migrate command creates a table in your database.
Next, you need to Install Maatwebsite Package in your laravel web applications. So go to your command prompt and run the following command to install the latest laravel Install Maatwebsite Package:
composer require maatwebsite/excel
When the package of Maatwebsite Package is installed. After that this package will have to be registered in the Laravel web application of the app. For this, you can register this package of instruction follow below.
App_root_directory/config/app.php
'providers' => [ Maatwebsite\Excel\ExcelServiceProvider::class, ], 'aliases' => [ 'Excel' => Maatwebsite\Excel\Facades\Excel::class, ]
When you will register the package of Maatwesite. After that, the package will have to be published with the command following command.
php artisan vendor:publish
Next, we need to generate fake data into the MySQL database table by using php artisan command. So go to your command prompt (cli) and then go to your project root directory. After that run the following command:
php artisan tinker
After run the php artisan tinker. Use the following command. This command will add 200 fake data in your MySQL database table users:
>>> factory(App\User::class, 200)->create();
Next step, go to routes/web.php file and update the following routes into your file:
Route::get('import', '[email protected]');
Route::post('import', '[email protected]');
Here, you can create an import class by using the following PHP artisan command. So go to your command prompt type the following command:
php artisan make:import ImportUsers --model=User
After that, Go to the app/Imports/ImportUsers.php and your created import class looks like:
<?php
namespace App\Imports;
use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
class ImportUsers implements ToModel
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new User([
'name' => $row[0],
'email' => $row[1],
]);
}
}
Now, you need to generate or create a controller file using php artisan command, so use the php artisan make:controller command and generate/create a new controller file:
php artisan make:controller ImportController
In this controller file, we will create some new methods. This method will also show import form and import data into mysql database..
After that, go to App/Http/Controllers/ directory and find the created ImportController file and update the following code into your file:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Imports\ImportUsers;
use Maatwebsite\Excel\Facades\Excel;
class ImportController extends Controller
{
/**
* @return \Illuminate\Support\Collection
*/
public function index()
{
return view('import');
}
/**
* @return \Illuminate\Support\Collection
*/
public function import(Request $request)
{
Excel::import(new ImportUsers, request()->file('file'));
return back();
}
}
Next, you need to create a blade views file, so go resource/views directory and create the following blade views file.
Go to resource/views/ then create a new file name import.blade.php and update the following code:
<!DOCTYPE html>
<html>
<head>
<title>Laravel Import Data From Excel File Into database - w3alert.com</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 mt-4">
<div class="card-header">
Laravel Import Data From Excel File Into database
</div>
<div class="card-body">
<form action="{{ url('import') }}" method="POST" name="importform"
enctype="multipart/form-data">
{{ csrf_token() }}
<input type="file" name="file" class="form-control">
<br>
<button class="btn btn-success">Import File</button>
</form>
</div>
</div>
</div>
</body>
</html>
You can use the following command artisan command to start the development server:
php artisan serve
The final step, go to your browser and type the below-given URL into your browser:
http://localhost:8000/import OR http://localhost/laravel-import/public/import
You can also download laravel import excel or CSV web application here Download
This tutorial idea has taken from this https://www.tutsmake.com/laravel-7-6-import-export-excel-csv-to-database-example/