noblemfd's avatar

Maatwebsite\Excel\Validators\ValidationException: The given data was invalid in Laravel

In my Laravel-5.8 project, I am using Maatwebsites-3.1 to import excel

Imports

<?php
namespace App\Imports;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class LeavesImport implements WithMultipleSheets
{
    public function sheets(): array
    {
        return [
            new FirstLeaveSheetImport()
        ];
    }
}

Which calles this:

class FirstLeaveSheetImport implements ToModel, WithHeadingRow, WithBatchInserts, WithValidation
{
    protected $staffid, $leavetype, $commencementdate, $resumptiondate, $reliefofficer;
    private $errors = []; // array to accumulate errors
    use Importable;

    // public function onRow(Row $row)
    public function model(array $row)
    {   
        $this->staffid = $row['staff_id'];
        $this->leavetype = $row['leave_type'];
        $this->reliefofficer = $row['relief_officer'];
        $this->commencementdate = $row['commencement_date'];

        return new HrLeaveRequest([
            'employee_id'                       => $this->getStaffId(),
            'leave_type_id'                     => $this->getLeaveType(),
            'commencement_date'                 => $this->transformDate($row['commencement_date']),
            'resumption_date'                   => $this->transformDate($row['resumption_date']),
            'no_of_days'                        => $row['leave_days'],
            'is_adjusted'                       => 1,
            'relief_officer_id'                 => $this->getReliefOfficer(),
            'reason'                            => $row['reason'] ?? '',
            'alternative_email_address'         => $row['alternative_email'] ?? '',
            'contact_phone_number'              => $row['contact_phone'] ?? '',
            'contact_address'                   => $row['contact_address'] ?? '',
            'company_id'                        => Auth::user()->company_id,
            'leave_status'                      => 4,
            'is_resumption_activated'           => 1,
            'resumption_activation_date'        => $this->transformDate($row['resumption_date']),
            'employee_code'                     => $row['staff_id'],
        ]);        

    }

    public function getStaffId(){
        if(!empty($this->staffid)){

            return HrEmployee::where('employee_code',$this->staffid)->where('company_id',Auth::user()->company_id)->pluck('id')->first();
        } else {
            return 0;
        }
    }

    public function getLeaveType(){
        if(!empty($this->leavetype) || !$this->leavetype){

            return HrLeaveType::where('leave_type_name',$this->leavetype)->where('company_id',Auth::user()->company_id)->pluck('id')->first();
        } else {
            return 0;
        }
    }    

    public function getReliefOfficer(){
        return HrEmployee::where('employee_code',$this->reliefofficer)->where('company_id',Auth::user()->company_id)->pluck('employee_code')->first();
    }

    public function getErrors()
    {
        return $this->errors;
    }

    public function rules(): array
    {
        return [
            'staff_id' => 'required|max:15',                   
            'leave_type' => 'required|max:255',
            'commencement_date' => 'required',
            'leave_days' => 'required|numeric',
            'resumption_date' => 'required',
            'relief_officer' => 'nullable|max:255',
            'reason' => 'nullable|max:255',
            'alternative_email' => 'nullable|email|max:255',
            'contact_phone' => 'nullable|phone:NG,mobile',
            'contact_address' => 'nullable|max:255',
        ];
    }  

    public function customValidationAttributes()
    {
        return [
            'staff_id'                  => 'Staff ID',
            'leave_type'                => 'Leave Type',
            'commencement_date'         => 'Commencement Date',
            'leave_days'                => 'Leave Days',
            'resumption_date'           => 'Resumption Date',
            'relief_officer'            => 'Duty Relief Officer',
            'reason'                    => 'Reason',
            'alternative_email'         => 'Alternative Email Address',
            'contact_phone'             => 'Contact Phone No.',  
            'contact_address'           => 'Contact Address',                                              
        ];
    }        

    public function validationMessages()
    {
        return [
            'staffid.*required' => "Staff ID is required",
        ];
    }

    public function transformDate($value, $format = 'Y-m-d')
    {
        try {
            return \Carbon\Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value));
        } catch (\ErrorException $e) {
            return \Carbon\Carbon::createFromFormat($format, $value);
        }
    } 

    public function batchSize(): int
    {
        return 200;
    }    

    public function headingRow(): int
    {
        return 1;
    }          
}

Controller

public function import(Request $request){
    $request->validate([
        'file' => 'required|max:10000|mimes:xlsx,xls',
    ]);

  $path1 = $request->file('file')->store('temp'); 
  $path=storage_path('app').'/'.$path1;  

    try{

        Excel::import(new LeavesImport, $path);      
        
    } catch (\Maatwebsite\Excel\Validators\ValidationException $e) {
        $failures = $e->failures();
        Log::error($e);
        $errormessage = "";            

     // dd($failures);
     foreach ($failures as $failure) {
         $errormess = "";
         foreach($failure->errors() as $error)
         {
             $errormess = $errormess.$error;
         }
         $errormessage = $errormessage." ,\n At Row ".$failure->row().", ".$errormess."<br>";
     }
         // Session::flash('error', 'Excel file is not imported!');
         Session::flash('error', $errormessage);
         // return redirect()->route('leave.leave_adjustments.index');  
         return back();            
    }catch (\Illuminate\Database\QueryException $e)
    {
        $errorCode = $e->errorInfo[1];
        if($errorCode == 1062){
        Log::error($e);
        DB::rollback();
        Session::flash('error', 'You have a duplicate entry problem!');
        }
        return back();

    }
    
    Session::flash('success', 'Leave Records Imported Successfully');
    return redirect()->route('leave.leave_reviews.index_hr');         
}

When I tried t upload the excel file it shows that it was successful. But I found that no data is store in the database. So when I checked error Log, I saw these:

[2020-11-16 07:38:53] production.ERROR: Maatwebsite\Excel\Validators\ValidationException: The given data was invalid. in C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Validators\RowValidator.php:62
Stack trace:
#0 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Imports\ModelManager.php(166): Maatwebsite\Excel\Validators\RowValidator->validate(Array, Object(App\Imports\FirstLeaveSheetImport))
#1 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Imports\ModelManager.php(53): Maatwebsite\Excel\Imports\ModelManager->validateRows(Object(App\Imports\FirstLeaveSheetImport))
#2 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Imports\ModelImporter.php(70): Maatwebsite\Excel\Imports\ModelManager->flush(Object(App\Imports\FirstLeaveSheetImport), true)
#3 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Sheet.php(248): Maatwebsite\Excel\Imports\ModelImporter->import(Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), Object(App\Imports\FirstLeaveSheetImport), 2)
#4 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Reader.php(111): Maatwebsite\Excel\Sheet->import(Object(App\Imports\FirstLeaveSheetImport), 2)
#5 C:\xampp\htdocs\myapp\vendor\laravel\framework\src\Illuminate\Database\Concerns\ManagesTransactions.php(29): Maatwebsite\Excel\Reader->Maatwebsite\Excel\{closure}(Object(Illuminate\Database\MySqlConnection))
#6 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Transactions\DbTransactionHandler.php(30): Illuminate\Database\Connection->transaction(Object(Closure))
#7 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Reader.php(115): Maatwebsite\Excel\Transactions\DbTransactionHandler->__invoke(Object(Closure))
#8 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Excel.php(146): Maatwebsite\Excel\Reader->read(Object(App\Imports\LeavesImport), 'C:\xampp\htdocs...', 'Xlsx', NULL)
#9 C:\xampp\htdocs\myapp\vendor\laravel\framework\src\Illuminate\Support\Facades\Facade.php(239): Maatwebsite\Excel\Excel->import(Object(App\Imports\LeavesImport), 'C:\xampp\htdocs...')
#10 C:\xampp\htdocs\myapp\app\Http\Controllers\Leave\LeaveAdjustmentsController.php(360): Illuminate\Support\Facades\Facade::__callStatic('import', Array)
#11 [internal function]: App\Http\Controllers\Leave\LeaveAdjustmentsController->import(Object(Illuminate\Http\Request))

How do I detect and rectify the error?

Thanks

0 likes
1 reply
MarianoMoreyra's avatar

Hi @noblemfd

What is this validation rule?

'contact_phone' => 'nullable|phone:NG,mobile'

Did you add a custom rule to validate phones? In any case, try removing the phone part (leave the billable if you want) to check if that might be the problem.

Just leave for now:

'contact_phone' => 'nullable'

Please or to participate in this conversation.