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 5. This 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.

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.

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.