Tuesday 11 June 2019

How to Import Export CSV File Data in Laravel 5.8



Do you know Import or Export CSV or Excel sheet data from Mysql Database is a primary needs of any admin level project. For this here we have make simple tutorial, in which we have step by step describe how to import CSV or Excel sheet data and Export CSV File or Excel sheet data in Laravel 5.8 application by using maatwebsite/excel version 3 package. There are many changes has been done in maatwebsite version 3 package. So, you have to need to know how to Export or Import of CSV or Excel file data in Laravel 5.8 with Mysql database. This updated package is now very simple and easy way to use with Laravel latest version like 5.7 and 5.8 for import excel CSV file data from Mysql database.

Suppose We have to some time export or import thousand of records import or export from our database. Then Maatwebsite has been offer functionality for importing and exporting of large records in CSV file or Excel sheet. For learn this feature here we will make import data in CSV file and import into mysql in laravel 5.8 application. For this task here we will use maatwebsite/excel package for importing and exporting data task with laravel 5.8 framework. Because maatwebsite/excel package has provide very easy method for import and export data using database model. Below you can find step by step process for how to use maatwebsite/excel version for import and export csv file data in Laravel 5.8 application.



How to Import Export CSV File Data in Laravel 5.8


Content


  • Install Laravel 5.8 framework

  • Add Fake Records in Mysql Database

  • Install Maatwebsite Package

  • Create Import Class

  • Create Export Class

  • Create Controller

  • Create Blade View File

  • Add route


Install Laravel 5.8 framework


First We need to download Laravel 5.8 version, and install into our computer. For this we have to go command prompt, in which we have to already run composer command and write following command. It will download Laravel 5.8 version in define folder.


composer create-project --prefer-dist laravel/laravel csv_file


After installing Laravel 5.8 framwork, after this we need to make database connection. For this we have to open .env file and under this file we have to define Mysql database configuration details which you can see below.


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


After makiing database configuration, now in next step we will see how to make table from Laravel 5.8 application.

Add Fake Records in Mysql Database


For add fake record into Mysql database. First we want to make table in Mysql database from this Laravel 5.8 application. Here we will use user default model for migrate data from this Laravel 5.8 application. If you have download fresh Laravel 5.8 framework, then you can find User default model in app/User.php. And for migrate data, you have to go command prompt and write following command.


php artisan migrate


This command will migrate default data from database/migrations folder and it will make user table in define Mysql database. Now for add fake records, we have to write following command in command prompt.


php artisan tinker

factory(App\User::class, 20)->create();


This command will add 20 fake records in User table, so we can perform import export data operation with CSV file.

Install Maatwebsite Package


Now for import and export data to csv file, we need to download and install Maatwebsite Version 3.0 package, for this we have go to command prompt and write following command.


composer require maatwebsite/excel


This command will download maatwebsite package in Laravel 5.8 application. Now we want to add into Laravel 5.8 application. For this we have open config/app.php and following service provider and aliase.

config/app.php

'providers' => [

 ....

 Maatwebsite\Excel\ExcelServiceProvider::class,

],

'aliases' => [

 ....

 'Excel' => Maatwebsite\Excel\Facades\Excel::class,

],


So, this way we can add maatwebsite/excel package in Laravel 5.8 application.




Create Import Class


Now we want to create import class for import data using maatwebsite 3.0 package in Laravel 5.8 frameworl. For this we have to write following command in command prompt.


php artisan make:import CsvImport --model=User


This command will make CsvImport.php import class under Import. We have to open this class and under this we to define table column which data we want to get from CSV file. Below you can find complete source code of Import Class for import data from CSV file by using Maatwebsite/excel 3.0 package in Laravel 5.8.

Imports/CsvImport.php

<?php

namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class CsvImport implements ToModel, WithHeadingRow
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new User([
            'name'      =>  $row["name"],
            'email'     =>  $row["email"],
            'password'  =>  \Hash::make($row['password']),
        ]);
    }
}


Create Export Class


After this for export mysql data into CSV file or excel sheet, we need to create Export class of maatwebsite using Laravel model. For this we have to write following command in command prompt.


php artisan make:export CsvExport --model=User


This command will make CsvExport.php class under Exports folder. You can find source code of this class below. We need to add this class into controller header for export data into CSV or Excel file.

Exports/CsvExport.php

<?php

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class CsvExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
}


Create Controller


Now we have to create controller for handle http request for import and export data. For this we have to write following command in command prompt.


php artisan make:controller CsvFile


This command will create controller under app/Http/Controllers/CsvFile.php file. In this file first we need to add import class, export class, maatwebsite and User model class. In this controller we have make following method.

index() - This method has load all data of user data with pagination feature.
csv_export() - This method has been received data export to csv file request. This method will export all User table data into CSV file format using CsvExport class and CSV file download in computer.
csv_import() - This method has been used for import csv file data into Mysql database. This method has import data from csv file by using CsvImport class.

Create Blade View File


For display output in browser, we have to create blade file for display Laravel 5.8 application output on browser. You can find below two blade file has been used for display output on web page.

resources/views/csv_file.blade.php

<html>
 <head>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Laravel 5.8 - Import Export Data in CSV File</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
 </head>
 <body>
  <div class="container">    
     <br />
     <h3 align="center">Laravel 5.8 - Import Export Data in CSV File</h3>
     <br />
     <div class="panel panel-default">
          <div class="panel-heading">
           <h3 class="panel-title">Laravel 5.8 - Import Export Data in CSV File</h3>
          </div>
          <div class="panel-body">
           <form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
            @csrf
            <input type="file" name="file" accept=".csv">
                  <br>
                  <button class="btn btn-success">Import User Data</button>
                  <a class="btn btn-warning" href="{{ route('export') }}">Export User Data</a>
           </form>
              @yield('csv_data')
          </div>
      </div>
  </div>
 </body>
</html>


resources/views/csv_file_pagination.blade.php

@extends('csv_file')

@section('csv_data')

<table class="table table-bordered table-striped">
 <thead>
  <tr>
   <th>Name</th>
   <th>Email Address</th>
  </tr>
 </thead>
 <tbody>
 @foreach($data as $row)
  <tr>
   <td>{{ $row->name }}</td>
   <td>{{ $row->email }}</td>
  </tr>
 @endforeach
 </tbody>
</table>

{!! $data->links() !!}

@endsection


Add route


Lastly we need to add route for import and export operation. For this we have to open routes/web.php file. In this file you can define below route for import and export method.


Route::get('csv_file', 'CsvFile@index');

Route::get('csv_file/export', 'CsvFile@csv_export')->name('export');

Route::post('csv_file/import', 'CsvFile@csv_import')->name('import');


Now we are ready for import and export data into CSV file in Laravel 5.8 by using maatwebsite 3.0 version. For this first we want to start Laravel 5.8 server, for this we have to write following command in command prompt.


php artisan serve


This command will start Laravel server and give you base url of Laravel application. For test import and export data operation we have to write following url in browser.


http://localhost:8000/csv_file


Now you can check your Laravel 5.8 import export data into CSV file. We hope it will help you to learn Laravel 5.8 with maatwebsite package.

15 comments:

  1. You forgot to share the code on the Controller!

    ReplyDelete
  2. Saya mempunyai error seperti ini Undefined index: name
    return new User([
    'name' => $row['name'],
    'email' => $row['email'],
    'password' => \Hash::make($row['password']),
    ]);
    Cara mengatasinya bagaimana?

    ReplyDelete
  3. I have a little problem in import. It is showing an error which is (Undefined index: name). Will you help me.

    ReplyDelete
  4. I follow this tutorial and i get error like this when i import file csv "ErrorException (E_NOTICE)
    Undefined index: name"

    ReplyDelete
    Replies
    1. This comment has been removed by a blog administrator.

      Delete
    2. try like this
      return new Bulk([
      'name' => $row[0]
      ]);

      Delete
  5. ErrorException
    Undefined index: name

    I am using Laravel 6.14

    ReplyDelete
  6. hi,
    thank u for your lesson, but how i can export data with column names?

    ReplyDelete
  7. Only 600 records was imported ,how to import more than that ?

    ReplyDelete
  8. Can we give dynamic name to the CSV file that is downloading

    ReplyDelete
  9. where is the Controller class code ???

    ReplyDelete
  10. I mean - no matter how helpful he's trying to be with this post it shows the worst senario. Not enough information and actually takes you longer to work out than just going to the docs and reading the API docs.

    Sorry but this was just awful.

    ReplyDelete