Importing Data from CSV or Excel and Exporting Table Schema in Laravel 9

Importing and exporting data is a crucial aspect of web applications, as it facilitates the smooth processing of data in batches. With Laravel, the process of exporting data is simplified through the Laravel Excel package, which eliminates the need for extensive code.

The Laravel Excel package also supports data import from multiple formats such as XLSX, CSV, XLS, HTML, and more. In this tutorial, we will explore how to import data from Excel to a MySQL database and also demonstrate how to export a sample sheet that guides users on the correct Excel format to import step-by-step.

Importing Data from CSV or Excel and Exporting Table Schema in Laravel 9

For exporting data in Excel format we will follow these simple steps:

Step 1: Install Laravel  Application

Step 2: Configure Database in the .env file

Step 3: Create Model, controller, and seeder file using a single command

Step 4: Migrate the database and seed dummy data

Step 5: Install the Excel package  maatwebsite/excel

Step 6: Create Routes

Step 7: Create an Import class

Step 8: Create an ExportSchema class

Step 9: Create a Controller

Step 10: Create a View

Step 11: Run Laravel Application

Note: If you have already set up your laravel projects you can skip Step 1, Step  2, Step 3, and Step 4. else you can start from Step 1.

Step 1: Install Laravel  Application

If you're not familiar with the process, you can refer to the "Install Laravel 9 from Scratch" tutorial. Alternatively, you can run the following command to proceed with this step:

        
        composer create-project laravel/laravel laravel_export_app
        
    

The given code is an implementation of the ToModel, WithHeadingRow, and withStartRow interfaces from the Maatwebsite\Excel\Concerns namespace. The ToModel interface is used for mapping data to Eloquent models, the WithHeadingRow interface is used to indicate that the imported file has a header row, and the withStartRow interface is used to specify the starting row for data mapping.

The PostImport class contains two methods: startRow() and model(). The startRow() method returns the starting row for the data mapping process, which in this case is the second row. The model() method maps the data from the imported file to an instance of the Post model.

Step 8: Create an ExportSchema class

Run the below command to create an import class:

php artisan make:export PostExportSchema --model=Post

Then open it from App\Export\PostExport.php and paste the below code

        
        <?php

namespace App\Exports;

use App\Models\Post;
use Maatwebsite\Excel\Concerns\FromCollection;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;

class PostExportSchema implements FromQuery, WithHeadings
{
    protected $id;

    public function __construct($id)
    {
        $this->id = $id;
    }

    public function headings(): array
    {
        $columns = DB::select(DB::raw('SHOW columns FROM posts'));

        return array_map(function ($column) {
            return $column->Field;
        }, $columns);
    }

    public function query()
    {
        return DB::table('posts')->where('id', $this->id)->orderBy('id');
    }


}
        
    

Related Post

Step 9: Create a Controller

Now open the App/Http/Controllers/PostController.php file and paste the below code.

        
        <?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use Maatwebsite\Excel\Facades\Excel;
use App\Models\Post;
use App\Imports\PostImport;
use App\Exports\PostExportSchema;


class PostController extends Controller
{
    public function index()
    {
        $post = Post::get();
        return view('posts.index', compact('post'));
    }


    public function import(Request $request){
        Excel::import(new PostImport, $request->file('file')->store('files'));
        return redirect()->back();
    }

    public function post_export_schema(){
        // Excel::store(new PostExportSchema(1), 'post.csv');
        return Excel::download(new PostExportSchema(1), 'posts.csv');
    }
}
        
    

Step 11: Create a View

Now create an index.blade.php file inside the resources/views/posts folder.

        
        <!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="csrf-token" content="{{ csrf_token() }}" />
    <title>Try it your self</title>
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet"
        integrity="sha384-rbsA2VBKQhggwzxH7pPCaAqO46MgnOM80zW1RWuH61DGLwZJEdK2Kadq2F9CUG65" crossorigin="anonymous">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css"
        integrity="sha512-iecdLmaskl7CVkqkXNQ/ZH/XLlvWZOJyj7Yy7tcenmpD1ypASozpmT/E0iPtmFIB46ZmdtAc9eNBvH0H/ZpiBw=="
        crossorigin="anonymous" referrerpolicy="no-referrer" />
    <script src="https://code.jquery.com/jquery-3.6.4.min.js"
        integrity="sha256-oP6HI9z1XaZNBrJURtCoUT5SUnxFr8s3BzRl+cbzUq8=" crossorigin="anonymous"></script>
    <style>
        body {
            background: #ededed;
        }

        .table_outer {
            padding: 20px 0;
        }

        table td,
        table th {
            text-overflow: ellipsis;
            white-space: nowrap;
            overflow: hidden;
        }

        .card {
            border-radius: .5rem;
        }

        .custom_table tbody .persons {
            padding: 0;
            margin: 0;
        }

        .custom_table tbody .persons li {
            padding: 0;
            margin: 0 0 0 -15px;
            list-style: none;
            display: inline-block;
        }

        .custom_table tbody .persons li a {
            display: inline-block;
            width: 36px;
        }

        .custom_table tbody .persons li a img {
            border-radius: 50%;
            max-width: 100%;
        }


        .custom_table tbody .persons.single li a {
            margin-left: 7px;
        }

        table button.btn {
            border-radius: 50%;
            width: 30px;
            height: 30px;
            text-align: center;
            line-height: 30px;
            padding: 0px !important;
        }

        table .remove_tr {
            box-shadow: 0 0 20px 0 rgba(255, 0, 0, .5);
            border: 2px solid rgba(255, 0, 0, 1);
        }
    </style>
</head>

<body>
    <section class="table_outer">
        <div class="container">
            <div class="row justify-content-center">
                <div class="col-12">



                    <div class="card border-0 shadow">
                        <div
                            class="card-header with-border d-flex justify-content-start align-items-center media_card_header">

                            <div class="container-fluid">
                                <div class="row">
                                    <div class="col-md-2">
                                        <a href="{{ url('post_export_schema') }}" class="btn btn-primary">Export
                                            Demo
                                            file</a>
                                    </div>
                                    <div class="col-md-3">
                                        <form action="{{ route('import') }}" method="POST"
                                            enctype="multipart/form-data">
                                            @csrf
                                            <div class="input-group">
                                                <input type="file" name="file" class="form-control" required
                                                    aria-describedby="basic-addon2">
                                                <button type="submit" class="input-group-text btn btn-primary"
                                                    id="basic-addon2">Import</button>
                                            </div>
                                        </form>
                                    </div>
                                </div>
                            </div>

                        </div>
                        <div class="card-body">

                            <div class="table-responsive">
                                <table class="table table-hover table-striped table-borderless custom_table">
                                    <thead class="table-success">
                                        <tr>
                                            <th scope="col">
                                                <div class="form-check">
                                                    <input class="form-check-input" type="checkbox" value=""
                                                        id="select_all" />
                                                </div>
                                            </th>
                                            <th scope="col">Title
                                            </th>
                                            <th scope="col">Description</th>
                                            <th scope="col">Status</th>
                                            <th scope="col">feature Image</th>
                                            <th scope="col">ACTION</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        @foreach ($post as $item)
                                            <tr class="">
                                                <th scope="row">
                                                    <div class="form-check">
                                                        <input class="form-check-input checkbox" type="checkbox"
                                                            value="" id="flexCheckDefault1"
                                                            data-id="{{ $item->id }}" value="{{ $item->id }}" />
                                                    </div>
                                                </th>
                                                <td>{{ $item->title }}</td>
                                                <td>
                                                    {{ $item->description }}
                                                </td>

                                                <td> {{ $item->status }}</td>
                                                <td>
                                                    <ul class="persons single">
                                                        <li>
                                                            <a href="#">
                                                                <img src="https://picsum.photos/id/64/100/100"
                                                                    alt="Person" class="img-fluid">
                                                            </a>
                                                        </li>
                                                    </ul>
                                                </td>
                                                <td>
                                                    <button type="button" class="btn btn-success btn-sm px-2">
                                                        <i class="fa-solid fa-pen-to-square"></i>
                                                    </button>
                                                    <button type="button" class="btn btn-primary btn-sm px-2">
                                                        <i class="fa-solid fa-eye"></i>
                                                    </button>
                                                    <button type="button" class="btn btn-danger btn-sm px-2">
                                                        <i class="fa-solid fa-trash"></i>
                                                    </button>
                                                </td>
                                            </tr>
                                        @endforeach


                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </section>
    <script>
        $(document).ready(function() {
            // Select/deselect all checkboxes
            $('#select_all').click(function() {
                if ($(this).is(':checked')) {
                    $('.checkbox').prop('checked', true);
                } else {
                    $('.checkbox').prop('checked', false);
                }
            });

            // If all checkboxes are selected, select the top checkbox
            $('.checkbox').click(function() {
                if ($('.checkbox:checked').length === $('.checkbox').length) {
                    $('#select_all').prop('checked', true);
                } else {
                    $('#select_all').prop('checked', false);
                }
            });
        });
    </script>
</body>

</html>
        
    

Step 10: Run Laravel Application

Now run the command below to run the app:

        
        php artisan serve
        
    

That's it, now you have all done open Chrome or any browser and hit URL Now open http://127.0.0.1:8000/posts or http://localhost:8000/posts.

If you are using this code after downloading from here make sure to run the bellow command then start your application.

        
        composer install
        
    

Step 2: Configure Database in the .env file

Once you have installed your Laravel application, it is crucial to create a database and configure the .env file accordingly. Here is an example that illustrates how to configure your .env file:

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

Step 3: Create Model, controller, and seeder file using a single command

        
        php artisan make:model Post -msc
        
    

Step 4: Migrate the database and seed dummy data

        
        <?php
namespace Database\Seeders;
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;
use App\Models\Post;

class PostSeeder extends Seeder
{
    public function run()
    {
        for($i =1; $i<=20; $i++){
            Post::create([
            'title' =>  'Test post title '.$i,
            'status' =>  rand(0, 1),
            'description' =>  'Lorem ipsum dolor sit amet, consectetur adipisicing elit. Odio ratione eius qui',
            ]);
        }
    }
}
        
    

Now Call it inside DatabaseSeeder.php

        
        public function run()
{

    $this->call([
        PostSeeder::class,
    ]);

}
        
    

Then execute the following command to create all the tables and seed the dummy data defined in the PostSeeder class:

        
        php artisan migrate --seed
        
    

Step 5: Install the Excel package  maatwebsite/excel

composer require maatwebsite/excel

If you are getting any issues with the above command you can run the below command to install the laravel excel package. 

        
        composer require maatwebsite/excel --ignore-platform-reqs

//or
composer require maatwebsite/excel:3.1 --ignore-platform-reqs
        
    

Step 6: Create Routes


        
        <?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\PostController;

Route::get('/', function () {
    return view('welcome');
});

Route::get('posts', [PostController::class, 'index'])->name('items.filter');
Route::post('/import',[PostController::class, 'import'])->name('import');

Route::get('/post_export_schema', [PostController::class, 'post_export_schema'])->name('post_export_schema');
        
    

Step 7: Create an Import class

Run the below command to create an import class:

php artisan make:import PostImport --model=Post

Then open it from App\Import\PostImport.php and paste the below code

        
        <?php

namespace App\Imports;

use App\Models\Post;
use Illuminate\Support\Carbon;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\withStartRow;

class PostImport implements ToModel , WithHeadingRow, withStartRow
{
    public function startRow(): int
    {
        return 2;
    }

    public function model(array $row)
    {

        //dd($row);
        // trim the spaces from each value in the row
        $row = array_map('trim', $row);

        return new Post([
            'title' => $row['title'],
            'description' => $row['description'],
            'status' => $row['status'],
            'created_at' => Carbon::createFromFormat('Y-m-d H:i:s', $row['created_at']),
            'updated_at' => Carbon::createFromFormat('Y-m-d H:i:s', $row['updated_at'])
        ]);
    }
}
        
    

Leave a comment