Import Export CSV di Laravel - Hi Coderrs, in this post I will share a tutorial on how to import data from a CSV file to a database and export data to csv format with the laravel-excel package in laravel 8.
How to Import-Export CSV in Laravel 8?
In laravel 8, to make a feature to import data from files in CSV format to a database or export data in CSV format, we can use the laravel-excel package. The ways of implementation will be discussed below.
Import CSV
Ok, the first feature we created is a feature to import data from a CSV format file to the users table in the database. The steps to make the CSV import feature in laravel 8 can be seen below.
- Step 1: Install Laravel
//via Laravel Installer
composer global require laravel/installer
laravel new laravel-csv
//via Composer
composer create-project laravel/laravel laravel-csv
In this first step, we need to install the latest version of laravel (currently version 8) which we will try to implement the import and export CSV feature in laravel 8. To install laravel you can use the laravel installer or use composer like the example above.
Please choose one method you want to use for laravel installation. From the two examples of laravel installation commands above, they will both generate or generate a laravel project with the name laravel-csv.
Wait until the installation process is complete and when it's finished, don't forget to go to the project directory using the command cd laravel-csv.
- Step 2: Setup Database
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_csv
DB_USERNAME=root
DB_PASSWORD=
Next, create a new database to store sample data that we will use in this experiment. If you are using xampp as local development, please create a new database at localhost/phpmyadmin. Here I give an example, I created a new database with the name laravel_csv. Then don't forget to adjust the DB_DATABASE in the .env file as well as in the example above. And then run php artisan migrate command, to migrate all migration files to database.
- Step 3: Install Laravel Excel Package
composer require maatwebsite/excel
Install the Laravel excel package by running the composer command as above.
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
To publish the config file, run the vendor publish command as above. Running this command will create a new config file named and located in the config/excel.php directory.
- Step 4: Create Import Class
php artisan make:import UsersImport --model=User
We will create a feature to import csv data into the users table first. For that, let's prepare the import file. Please run the artisan command as above to generate the UsersImport.php file. If the artisan command has been executed, the UsersImport.php file will be located in the App/Imports directory.
<?php
namespace App\Imports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;
class UsersImport implements ToModel, WithStartRow, WithCustomCsvSettings
{
public function startRow(): int
{
return 2;
}
public function getCsvSettings(): array
{
return [
'delimiter' => ';'
];
}
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new User([
'name' => $row[0],
'email' => $row[1],
'password' => \Hash::make($row[2]),
]);
}
}
Then open the file that was just generated or in App/Http/Imports/UsersImport.php and change the existing code to be like the code above. With the code above, there is a startRow function that serves to set the data that is entered into the users table only data starting from the second row. So the first line which usually contains the heading will be skipped.
Then in the function model, we set the data from row 0 will be entered into the name field, the data from row 1 will be entered into the email field and the value in row 2 will be entered into the password field with hash format.
- Step 5: Create Route & Logic
<?php
use Illuminate\Support\Facades\Route;
use Maatwebsite\Excel\Facades\Excel;
use App\Imports\UsersImport;
....
....
...
....
Route::get('/', function () {
return view('welcome',[
'users' => App\Models\User::all()
]);
});
Route::post('import', function () {
Excel::import(new UsersImport, request()->file('file'));
return redirect()->back()->with('success','Data Imported Successfully');
});
Next, change the route provided by laravel default and add a post route to import files or users data in csv format. So for now, the code in routes/web.php will be like the code above.
- Step 6: Setup View
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
<title>Import & Export CSV in Laravel 8</title>
</head>
<body>
<div class="container my-5">
<h1 class="fs-5 fw-bold text-center">Import & Export CSV in Laravel 8</h1>
<div class="row">
<div class="d-flex my-2">
<a href="" class="btn btn-primary me-1">Export Data</a>
<button type="button" class="btn btn-success" data-bs-toggle="modal" data-bs-target="#exampleModal">
Import Data
</button>
</div>
@if (session('success'))
<div class="alert alert-success alert-dismissible fade show" role="alert">
{{ session('success') }}
<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
</div>
@endif
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">Name</th>
<th scope="col">Email</th>
</tr>
</thead>
<tbody>
@foreach ($users as $key => $item)
<tr>
<th scope="row">{{ ++$key }}</th>
<td>{{ $item->name }}</td>
<td>{{ $item->email }}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
<!-- Modal -->
<div class="modal fade" id="exampleModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">Import CSV</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<form action="import" method="POST" enctype="multipart/form-data">
@csrf
<div class="input-group mb-3">
<input type="file" name="file" class="form-control">
<button class="btn btn-primary" type="submit">Submit</button>
</div>
</form>
</div>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
</body>
</html>
Then, open the welcome.blade.php file and change the existing code to be like the code above. In this welcome.blade.php file, we will use bootstrap 5 and add a component table and two buttons namely export and import.
- Step 7: Import CSV Testing
Okay, after going through the processes starting from installing laravel, installing the laravel excel package, generating the UsersImport.php file, and setting up the view, it's time for us to test the CSV import feature that we have created. Please download the sample CSV file below for use in testing the CSV import in laravel 8.
Then, run the laravel project by running the command php artisan serve. Open laravel project in browser with URL like 127.0.0.1:8000 or laravel-csv.test. Click the Import Data button, then select the CSV file and click submit. Then the data from the CSV file will go to the database or users table and the data will be displayed in the view as shown above.
What if you want to save the CSV file in storage?
Route::post('import', function () {
$fileName = time().'_'.request()->file->getClientOriginalName();
request()->file('file')->storeAs('reports', $fileName, 'public');
Excel::import(new UsersImport, request()->file('file'));
return redirect()->back()->with('success','Data Imported Successfully');
});
To save a CSV file (in storage) when importing data from a CSV format file to the database, we can add the code as above. With this code, the imported CSV file will be stored in the storage/app/public/reports directory.
Export CSV
Then, the second feature that we will make is a feature to export data from the users table into CSV format. How to ? Please see the brief explanation below.
- Step 1: Create Export Class php artisan make:export UsersExport --model=User First, we generate the export file first using the artisan command as above. With this command, it will create a file with the name UsersExport.php which is located in the app/Exports directory.
<?php
namespace App\Exports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;
use Maatwebsite\Excel\Concerns\WithHeadings;
class UsersExport implements FromCollection, WithCustomCsvSettings, WithHeadings
{
public function getCsvSettings(): array
{
return [
'delimiter' => ';'
];
}
public function headings(): array
{
return ["Name", "Email"];
}
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return User::select('name','email')->get();
}
}
Then open the UsersExport.php file that has been successfully generated and change or adjust the code to be like the code above. With the code as above, we use a delimiter or data delimiter in the CSV file with a semicolon character (;). Then we also add a heading in the CSV file (output or export) with two texts namely Name and Email. And we select the data that is exported to a CSV file, which is the data in the name and email fields.
'enclosure' => '"',
Next open the config file located in the config/excel.php directory. Then look for the exclousure code as above, then change it to something like the one below.
'enclosure' => '',
- Step 2: Add Route
Route::get('export-csv', function () {
return Excel::download(new UsersExport, 'users.csv');
});
Next, we add an export csv route to download the csv file from the UsersExport file with the output file name users.csv. We will call this route later in the view or more precisely in the Export Data button.
- Step 3: Setup View
<a href="" class="btn btn-primary me-1">Export Data</a>
Well, now open the welcome.blade.php file. Look for the Export Data button code like the code above, then change it to like the code below.
<a href="export-csv" target="_blank" class="btn btn-primary me-1">Export Data</a>
Here we just change or add a link to the export-csv route.
- Step 4: Export CSV Testing
Okay, now it's time for the second test or testing the CSV export feature that we have made in laravel 8. To test the CSV export feature, make sure the users table is filled with data, or have imported csv data in the previous step. If the users table is filled with data, we can immediately try clicking the Export Data button.
Finished.
Conclusion
In this article, we have both learned how to create an export or import data feature in CSV format in laravel 8. By using the laravel-excel package, we can easily create a csv export or import feature in laravel. Not only CSV, with the laravel-excel package we can also use other file formats such as xlsx, tsv, pdf and others. We can explore the package on the official website with the link below.
📓 Full Documentation: Laravel Excel
visit Codelapan and find other articles about web development.
Top comments (0)