Preface
I’m sure many of you when working on projects that have been asked to import excel or csv data into a database. Then we will often think of the processing functions that php provides such as fopen to read files, fgetcsv to read csv files … However today I will introduce you to a package that supports us reading, Export excel or csv file very easily and quickly. And that is maatwebsite / excel. But today I’m not going to do normal file import, I’m focusing mostly on validating data in import file. Let’s get started!
Initialize project
First, create a new project laravel
1 2 | composer create-project --prefer-dist laravel/laravel test_import_csv_file "5.8.*" |
Create route
After web.php
the project, go to the web.php
file and add the following:
1 2 3 4 5 6 | use IlluminateHttpRequest; ... Route::post('/import', function (Request $request) { return view('welcome'); })->name('import.csv'); |
For quick and convenient, I always use the closure function on the route, no need to take time to create controller =)).
Create blade view
Sure, we need an interface to perform the import function. I use the welcome.blade.php file to add the import form:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | <!DOCTYPE html> <html lang="{{ str_replace('_', '-', app()->getLocale()) }}"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>Laravel</title> <!-- Fonts --> <link href="https://fonts.googleapis.com/css?family=Nunito:200,600" rel="stylesheet"> <!-- Styles --> <style> html, body { background-color: #fff; color: #636b6f; font-family: 'Nunito', sans-serif; font-weight: 200; height: 100vh; margin: 0; } .full-height { height: 100vh; } .flex-center { align-items: center; display: flex; justify-content: center; } .position-ref { position: relative; } .top-right { position: absolute; right: 10px; top: 18px; } .content { text-align: center; } .title { font-size: 84px; } .links > a { color: #636b6f; padding: 0 25px; font-size: 13px; font-weight: 600; letter-spacing: .1rem; text-decoration: none; text-transform: uppercase; } .m-b-md { margin-bottom: 30px; } </style> </head> <body> <div class="flex-center position-ref full-height"> <form action="{{route('import.csv')}}" method="post" enctype="multipart/form-data"> @csrf <input type="file" name="csv_file"></p> <button type="submit">Submit</button> </form> </div> </body> </html> |
Now, all you need to do is add a database and we can start validating data from the csv file.
Create the form request
Of course, to validate data records inside the csv file, we first need to validate the file uploaded to the server first, right? Please create a form request as follows:
1 2 | php artisan make:request ImportCsvFileRequest |
Laravel will create the ImportCsvFileRequest.php
file for us with the following content:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | <?php namespace AppHttpRequests; use IlluminateFoundationHttpFormRequest; class ImportCsvFileRequest extends FormRequest { /** * Determine if the user is authorized to make this request. * * @return bool */ public function authorize() { return true; } /** * Get the validation rules that apply to the request. * * @return array */ public function rules() { return [ 'csv_file' => [ 'required', 'file', 'mimes:csv,txt', ], ]; } } |
Install Laravel excel
Ok, now we go to the main part of today’s article. Please go to the laravel excel homepage to find out more details. Of course, to use it, we have to install it into the project.
1 2 | composer require maatwebsite/excel |
Since we perform the import file function, let’s create the import file:
1 2 | php artisan make:import UsersImport --model=User |
1 2 | php artisan make:import ValidateCsvFile |
What do we create these 2 import files? Why create 2 import files? In fact, the name of the file speaks for its purpose. But the details of how you guys wait and see later. Content of UsersImport.php
file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | <?php namespace AppImports; use MaatwebsiteExcelConcernsWithChunkReading; use IlluminateContractsQueueShouldQueue; use MaatwebsiteExcelConcernsWithStartRow; use IlluminateSupportFacadesDB; use IlluminateSupportFacadesLog; use MaatwebsiteExcelConcernsToModel; use IlluminateSupportFacadesValidator; use MaatwebsiteExcelConcernsImportable; use AppUser; class UsersImport implements ToModel, ShouldQueue, WithChunkReading, WithStartRow { use Importable; /** * @var errors */ private $errors; /** * @var row */ private $row = 1; /** * UsersImport constructor. * @param StoreEntity $store */ public function __construct($errors = []) { $this->errors = $errors; } public function model(array $row) { if (array_key_exists(++$this->row, $this->errors)) { return null; } $validator = Validator::make($row, [ '0' => [ 'required', 'string', 'max:255', ], '1' => [ 'required', 'string', 'email', 'max:255', ], '2' => [ 'required', 'string', 'min:6', ], ]); if ($validator->fails()) { return null; } DB::beginTransaction(); try { User::create([ 'name' => $row[0], 'email' => $row[1], 'password' => $row[2], ]); DB::commit(); } catch (Exceptions $e) { DB::rollBack(); Log::debug($e); } } public function chunkSize(): int { return 500; } public function startRow(): int { return 2; } } |
file ValidateCsvFile.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | <?php namespace AppImports; use MaatwebsiteExcelConcernsWithStartRow; use IlluminateSupportCollection; use MaatwebsiteExcelConcernsToCollection; use IlluminateSupportFacadesDB; use IlluminateSupportFacadesValidator; class ValidateCsvFile implements ToCollection, WithStartRow { /** * @var errors */ public $errors = []; /** * @var isValidFile */ public $isValidFile = false; /** * ValidateCsvFile constructor. * @param StoreEntity $store */ public function __construct() { // } public function collection(Collection $rows) { $errors = []; if (count($rows) > 1) { $rows = $rows->slice(1); foreach ($rows as $key => $row) { $validator = Validator::make($row->toArray(), [ '0' => [ 'required', 'string', 'max:255', ], '1' => [ 'required', 'string', 'email', 'max:255', // 'unique:users', ], '2' => [ 'required', 'string', 'min:6', ], ]); if ($validator->fails()) { $errors[$key] = $validator; } } $this->errors = $errors; $this->isValidFile = true; } } public function startRow(): int { return 1; } } |
And finally the route closure () function inside our final web.php file will look like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | <?php use IlluminateHttpRequest; use IlluminateSupportFacadesValidator; use AppHttpRequestsImportCsvFileRequest; use AppImportsUsersImport; use AppImportsValidateCsvFile; /* |-------------------------------------------------------------------------- | Web Routes |-------------------------------------------------------------------------- | | Here is where you can register web routes for your application. These | routes are loaded by the RouteServiceProvider within a group which | contains the "web" middleware group. Now create something great! | */ Route::get('/', function () { return view('welcome'); }); Route::post('/import', function (ImportCsvFileRequest $request) { $validator = new ValidateCsvFile(); Excel::import($validator, $request->file('csv_file')); if (count($validator->errors)) { $errors = []; foreach ($validator->errors as $key => $error) { $errors[$key] = $key; } (new UsersImport($errors))->queue($request->file('csv_file')); return redirect()->back()->with('error', 'row number ' . implode(',', $errors) . ' contain incorrect data'); } elseif (!$validator->isValidFile) { return redirect()->back(); } (new UsersImport())->queue($request->file('csv_file')); return redirect()->back(); })->name('import.csv'); |
The purpose of the ValidateCsvFile.php file is that we will validate the entire row included in the file, and with the $ errors variable we can show and determine which row is being validated and from which we can fix. file a convenient way.
And with the UsersImport file, when importing each row one by one, we validate again to ensure it cannot pass rows with an invalid value.
Result
And here is the final result we achieved: So we have successfully imported a csv file into the database with extremely easy validate.
Conclude
I have just finished a fairly simple demo of data validation when we import it from the csv file. Hope to help you solve some problems when doing tasks related to importing files. If you have any questions, please comment below. Would like to thank!