AppDividend
Latest Code Tutorials

How to Import and Export Data in CSV in Laravel 5

17,899

Get real time updates directly on you device, subscribe now.

Hii, Web Artists,  In today’s AppDividend Tutorial, I have shown the code of How to import and export data in CSV in Laravel 5This example is simple laravel excel tutorial.

If you want to up and running with basic laravel functionality, then go to my other article on this web blog called Laravel 5.4 Crud Example From Scratch.

Import & export data in CSV in Laravel 5.4

We will start this tutorial by installing the maatwebsite/excel package.

Step: 1 Download the maatwebsite/excel package through composer.

composer require maatwebsite/excel

maatwebsite/excel is a Laravel specific package, which provides us some methods to import and export our data from our database.

Step: 2 Add the service provider to the provider’s array in config/app.php file.

Maatwebsite\Excel\ExcelServiceProvider::class,

Step: 3 You can use the facade so, add this to your aliases in config/app.php file.

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

Step: 4 To publish the config settings in Laravel 5.4 use.

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

Step: 5 Create an items table via migration command.

 php artisan make:migration create_items_table

Step: 6 Define columns in our items table.

Go to project folder >> database >> migrations >> items migration file and edit the file.

// 2017_06_13_000837_create_items_table.php

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateItemsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
      Schema::create('items', function (Blueprint $table) {
          $table->increments('id');
          $table->string('item_name');
          $table->string('item_code');
          $table->string('item_price');
          $table->integer('item_qty');
          $table->integer('item_tax');
          $table->boolean('item_status');
          $table->timestamp('created_at');
      });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
          Schema::dropIfExists('items');
    }
}

Step: 7 Run the migration.

php artisan migrate

Now the table is created in the database. Next step is to build a view for the table to import the file.

Step: 8 Create the items.blade.php file.

// items.blade.php

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>Import-Export Data</title>
    <!-- Latest compiled and minified CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"
  </head>
  <body>
    <div class="container">
      <br />
      <div class="row">
        <div class="col-md-8"></div>
        <div class="col-md-2">
          <button class="btn btn-primary">Import</button>
        </div>
        <div class="col-md-2">
          <button class="btn btn-success">Export</button>
        </div>
      </div>
    </div>
  </body>
</html>

Step: 9 Create an ItemController file.

php artisan make:controller ItemController --resource

Step: 10 Register the route for that item view.

// web.php

Route::get('items', 'ItemController@index');

Step: 11 Write index function in ItemController file.

// ItemController.php

/**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        return view('items');
    }

Step: 12 Create import form in the view file.

<!--items.blade.php -->

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>Import-Export Data</title>
    <!-- Latest compiled and minified CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"
  </head>
  <body>
    <div class="container">
      <br />
      <div class="row">
        <div class="col-md-4"></div>
        <div class="col-md-6">
          <div class="row">
            <form action="" method="post" enctype="multipart/form-data">
              <div class="col-md-6">
                  <input type="file" name="imported-file"/>
              </div>
              <div class="col-md-6">
                  <button class="btn btn-primary" type="submit">Import</button>
              </div>
            </form>
          </div>
        </div>
        <div class="col-md-2">
          <button class="btn btn-success">Export</button>
        </div>
      </div>
    </div>
  </body>
</html>

Step: 13 Create a model for items table

php artisan make:model Item

so your model will look like this.

// Item.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Item extends Model
{
    protected $fillable = [
      'item_name',
      'item_code',
      'item_price',
      'item_qty',
      'item_tax',
      'item_status',
      'created_at'
    ];
}

Here I have added protected $fillable field to prevent mass assignment exception.

Step: 14 Update the routes and also update actions according to routes.

// web.php

<?php

Route::get('/', function () {
    return view('welcome');
});
Route::get('users', 'UserController@index');

Route::get('items', 'ItemController@index');
Route::post('items/import', 'ItemController@import');

Step: 15 Update items.blade.php according to action provided by routes file web.php

<!-- items.blade.php -->

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>Import-Export Data</title>
    <!-- Latest compiled and minified CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"
  </head>
  <body>
    <div class="container">
      <br />
      <div class="row">
        <div class="col-md-4"></div>
        <div class="col-md-6">
          <div class="row">
            <form action="{{url('items/import')}}" method="post" enctype="multipart/form-data">
              <div class="col-md-6">
                {{csrf_field()}}
                <input type="file" name="imported-file"/>
              </div>
              <div class="col-md-6">
                  <button class="btn btn-primary" type="submit">Import</button>
              </div>
            </form>
          </div>
        </div>
        <div class="col-md-2">
          <button class="btn btn-success">Export</button>
        </div>
      </div>
    </div>
  </body>
</html>

I have also added {{csrf_field()}} to prevent token mismatch exception because this form uses POST request. For more details, please visit https://laravel.com/docs/5.4/csrf

Step: 16 Write import function in ItemController.

// ItemController.php

  /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function import(Request $request)
    {
      if($request->file('imported-file'))
      {
			    $path = $request->file('imported-file')->getRealPath();
			    $data = Excel::load($path, function($reader) {
			})->get();

			if(!empty($data) && $data->count())
      {
        $data = $data->toArray();
        for($i=0;$i<count($data);$i++)
        {
          $dataImported[] = $data[$i];
        }
			}
      Item::insert($dataImported);
		}
		return back();
  }

I am uploading that excel file in here.  Items Excel File

Now you can upload any excel file and import it, and it can easily be imported into your database.

The new table with inserted values will look like this.

How to import and export data into csv and excel in Laravel 5.4

Now, if the database column names and excel sheet headers are different then we can use following code.

<!-- ItemController.php -->

/**
     * import a file in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function import(Request $request)
    {
      if($request->file('imported-file'))
      {
			    $path = $request->file('imported-file')->getRealPath();
			    $data = Excel::load($path, function($reader)
          {
			    })->get();

          if(!empty($data) && $data->count())
          {
            foreach ($data->toArray() as $row)
            {
              if(!empty($row))
              {
                $dataArray[] =
                [
                  'item_name' => $row['name'],
                  'item_code' => $row['code'],
                  'item_price' => $row['price'],
                  'item_qty' => $row['quantity'],
                  'item_tax' => $row['tax'],
                  'item_status' => $row['status'],
                  'created_at' => $row['created_at']
                ];
              }
          }
          if(!empty($dataArray))
          {
             Item::insert($dataArray);
             return back();
           }
         }
       }
    }

Here is the excel file which has header name and database column name different. Different Header file name

For CSV it works the same so, you can use this code for CSV implementation.

Next step is to Export the files in Excel or CSV format. So first step is to view that table into the items.blade.php

Step: 17 Create table in the items.blade.php file.

Related Posts
1 of 41
<!-- items.blade.php -->

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>Import-Export Data</title>
    <!-- Latest compiled and minified CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"
  </head>
  <body>
    <div class="container">
      <br />
      <div class="row">
        <div class="col-md-4"></div>
        <div class="col-md-6">
          <div class="row">
            <form action="{{url('items/import')}}" method="post" enctype="multipart/form-data">
              <div class="col-md-6">
                {{csrf_field()}}
                <input type="file" name="imported-file"/>
              </div>
              <div class="col-md-6">
                  <button class="btn btn-primary" type="submit">Import</button>
              </div>
            </form>
          </div>
        </div>
        <div class="col-md-2">
          <form action="{{url('items/export')}}" enctype="multipart/form-data">
            <button class="btn btn-success" type="submit">Export</button>
          </form>
        </div>
      </div>
      <div class="row">
        @if(count($items))
        <table class="table table-striped">
          <thead>
            <tr>
              <td>item_name</td>
              <td>item_code</td>
              <td>item_price</td>
              <td>item_qty</td>
              <td>item_tax</td>
              <td>item_status</td>
            </tr>
          </thead>
          @foreach($items as $item)
            <tr>
              <td>{{$item->item_name}}</td>
              <td>{{$item->item_code}}</td>
              <td>{{$item->item_price}}</td>
              <td>{{$item->item_qty}}</td>
              <td>{{$item->item_tax}}</td>
              <td>{{$item->item_status}}</td>
            </tr>
          @endforeach
        </table>
        @endif
      </div>
    </div>
  </body>
</html>

Also, I have defined the action in the export button.

Now create an action in the web.php file.

// web.php

<?php

Route::get('/', function () {
    return view('welcome');
});
Route::get('users', 'UserController@index');

Route::get('items', 'ItemController@index');
Route::post('items/import', 'ItemController@import');
Route::get('items/export', 'ItemController@export');

Step: 18 Create an export function in ItemControlller.php file.

// ItemController.php

/**
     * export a file in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function export(){
      $items = Item::all();
      Excel::create('items', function($excel) use($items) {
          $excel->sheet('ExportFile', function($sheet) use($items) {
              $sheet->fromArray($items);
          });
      })->export('xls');

    }

Now when you press the export button, one .xls file will be downloaded in your browser.

How to import and export data into csv and excel in Laravel 5.4

 This example demonstrates Laravel import CSV or excel to the database.

If you have any doubt in this How to Import and Export Data Laravel 5 tutorial then ask in a comment below, I am happy to help you out.

17 Comments
  1. gayathri says

    this is very useful tutorial but now i have table values in excel sheet like biometric access values in excel how to import in database in laravel5

  2. Amadou Barry says

    I keep getting a blank row on the top of my file. Do you have any idea how to fix this?

    1. sa says

      array_filter(array_map(‘array_filter’, $dataArray));

  3. Jeff says

    This is a great tutorial! My CSV file doesn’t contain the “created_at” or “updated_at” fields. When the records are inserted into the database those fields are null. How can I keep that from occurring?

  4. Patrick says

    what if i have the same item_code then if i import it will update only not to insert again. is it possible in this import function?

    Thanks in advance for your answer.

  5. Jon Aguilar says

    Hi i cannot download the csv file. Please so I can test this project.

  6. Deen says

    Great tutorial but missing add ‘use Maatwebsite\Excel\Facades\Excel;’ at top of controller (step 11).
    Thanks anyway.

  7. Hafiizh says

    Undefined variable: items (View: C:\xampp\htdocs\kantor\laravel\maatwebsite\resources\views\items.blade.php) how ??

  8. abhijit says

    Hi,
    I have done the following config in route.php…

    Route::get(‘/items’, ‘ItemController@index’);

    Route::post(‘/items/import’,[ ‘uses’ => ‘ItemController@import’,
    ‘as’ => ‘items.import’]
    );

    And in Form Action page is like…

    <form action="{{route('items.import')}}"

    The error am getting is NotFoundHttpException in RouteCollection.php
    Any help on this would be highly appreciable…

    1. Yannig says

      Hi, i want to export items with price but price is in other talbe, how can i do this stuff ?

  9. dimer says

    Call to undefined method MaatwebsiteExcelExcel::save()

    1. James says

      @Dimer Downgrade your Maatwebsite/excel package to version 2.1. The current version(3.0) doesn’t support save(), Import() and export() functions yet.

  10. jaspreet singh says

    Hello there,

    i got error ErrorException (E_NOTICE) Undefined index: name please let me know where i m wrong.

  11. sobhan says

    Hi sir It is very useful to me but when i am converting into .csv file after every line completion it is giving two (,,) as outpuy instead of one comma(,).

  12. Dilee says

    Hi sir i m getting the Call to undefined method Maatwebsite\Excel\Excel::load() when import the csv file.How to fix this.I am using laravel 5.6 version.Please help me.

    1. Krunal says

      It is removed in the package’s latest version. Please check the doc for that.

  13. Adarsh Bhatt says

    In latest library Create function is not work. So I have to use either download or store method. but when I use this method in my code instead of create() like
    Excel::download(‘customerdata’, function($excel) use($customerdataset) {
    $excel->sheet(‘ExportFile’, function($sheet) use($customerdataset) {
    $sheet->fromArray($customerdataset);
    });
    })->export(‘xls’);

    I got error like “Argument 2 passed to Maatwebsite\Excel\Excel::download() must be of the type string, object given,”. Can you please help me to solve this.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.