How to Import and Export Data in CSV in Laravel 5

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 and export data in CSV in Laravel 5.4

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

Read More
1 of 4

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.

<!-- 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

So finally our tutorial, How to import and export data into CSV and excel in Laravel 5.4 is over. 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.

You might also like More from author

3 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));

Leave A Reply

Your email address will not be published.