Row Validation

Sometimes you might want to validate each row before it's inserted into the database. By implementing the WithValidation concern, you can indicate the rules that each row need to adhere to.

The rules() method, expects an array with Laravel Validation rules to be returned.

<?php

namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithValidation;

class UsersImport implements ToModel, WithValidation
{
    use Importable;

    public function model(array $row)
    {
        return new User([
            'name'     => $row[0],
            'email'    => $row[1],
            'password' => 'secret',
        ]);
    }

    public function rules(): array
    {
        return [
            '1' => Rule::in(['patrick@maatwebsite.nl']),

             // Above is alias for as it always validates in batches
             '*.1' => Rule::in(['patrick@maatwebsite.nl']),
             
             // Can also use callback validation rules
             '0' => function($attribute, $value, $onFailure) {
                  if ($value !== 'Patrick Brouwers') {
                       $onFailure('Name is not Patrick Brouwers');
                  }
              }
        ];
    }
}
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

Validating with a heading row

When using the WithHeadingRow concern, you can use the heading row name as rule attribute.

<?php

namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class UsersImport implements ToModel, WithValidation, WithHeadingRow
{
    use Importable;

    public function model(array $row)
    {
        return new User([
            'name'     => $row['name'],
            'email'    => $row['email'],
            'password' => 'secret',
        ]);
    }

    public function rules(): array
    {
        return [
            'email' => Rule::in(['patrick@maatwebsite.nl']),

             // Above is alias for as it always validates in batches
             '*.email' => Rule::in(['patrick@maatwebsite.nl']),
        ];
    }
}
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

Custom validation messages

By adding customValidationMessages() method to your import, you can specify custom messages for each failure.

/**
* @return array
*/
public function rules(): array
{
    return [
        '1' => Rule::in(['patrick@maatwebsite.nl']),
    ];
}

/**
 * @return array
 */
public function customValidationMessages()
{
    return [
        '1.in' => 'Custom message for :attribute.',
    ];
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Custom validation attributes

By adding customValidationAttributes() method to your import, you can specify custom attribute names for each column.

/**
* @return array
*/
public function rules(): array
{
    return [
        '1' => Rule::in(['patrick@maatwebsite.nl']),
    ];
}

/**
 * @return array
 */
public function customValidationAttributes()
{
    return ['1' => 'email'];
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Handling validation errors

Database transactions

The entire import is automatically wrapped in a database transaction, that means that every error will rollback the entire import. When using batch inserts, only the current batch will be rollbacked.

Disable transactions

If you prefer to not have any database transactions around your import (or chunk import), you can change which transaction handler you want to use in the config:

In config/excel.php:

'transactions' => [
    'handler' => 'db',
],
1
2
3

Supported handlers are currently: null or db.

Custom transaction handlers

If you want a custom transaction handler (for e.g. a MongoDB database), you can add your own handler:

$this->app->make(\Maatwebsite\Excel\Transactions\TransactionManager::class)->extend('your_handler', function() {
    return new YourTransactionHandler();
});
1
2
3

The Handler should implement Maatwebsite\Excel\Transactions\TransactionHandler.

Gathering all failures at the end

You can gather all validation failures at the end of the import, when used in conjunction with Batch Inserts. You can try-catch the ValidationException. On this exception you can get all failures.

Each failure is an instance of Maatwebsite\Excel\Validators\Failure. The Failure holds information about which row, which column and what the validation errors are for that cell.

try {
    $import->import('import-users.xlsx');
} catch (\Maatwebsite\Excel\Validators\ValidationException $e) {
     $failures = $e->failures();
     
     foreach ($failures as $failure) {
         $failure->row(); // row that went wrong
         $failure->attribute(); // either heading key (if using heading row concern) or column index
         $failure->errors(); // Actual error messages from Laravel validator
         $failure->values(); // The values of the row that has failed.
     }
}
1
2
3
4
5
6
7
8
9
10
11
12

Skipping failures

Sometimes you might want to skip failures. By using the SkipsOnFailure concern, you get control over what happens the moment a validation failure happens. When using SkipsOnFailure the entire import will not be rollbacked when a failure occurs.

<?php

namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Validators\Failure;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\SkipsOnFailure;
use Maatwebsite\Excel\Concerns\WithValidation;

class UsersImport implements ToModel, WithValidation, SkipsOnFailure
{
    use Importable;

    /**
     * @param Failure[] $failures
     */
    public function onFailure(Failure ...$failures)
    {
        // Handle the failures how you'd like.
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

If you automatically want to skip all failed rows and collect the failures at the end of the import, you can use the Maatwebsite\Excel\Concerns\SkipsFailures trait.

<?php

namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Validators\Failure;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\SkipsOnFailure;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\SkipsFailures;

class UsersImport implements ToModel, WithValidation, SkipsOnFailure
{
    use Importable, SkipsFailures;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Every row that has failed the validation rules, will have been skipped. We can now collect all the failures at the end:

$import = new UsersImport();
$import->import('users.xlsx');

foreach ($import->failures() as $failure) {
     $failure->row(); // row that went wrong
     $failure->attribute(); // either heading key (if using heading row concern) or column index
     $failure->errors(); // Actual error messages from Laravel validator
     $failure->values(); // The values of the row that has failed.
}
1
2
3
4
5
6
7
8
9

Skipping errors

Sometimes you might want to skip all errors, e.g. duplicate database records. By using the SkipsOnError concern, you get control over what happens the moment a model import fails. When using SkipsOnError the entire import will not be rollbacked when an database exception occurs.

<?php

namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\SkipsOnError;
use Maatwebsite\Excel\Concerns\WithValidation;

class UsersImport implements ToModel, WithValidation, SkipsOnError
{
    use Importable;

    /**
     * @param \Throwable $e
     */
    public function onError(\Throwable $e)
    {
        // Handle the exception how you'd like.
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

If you automatically want to skip all exceptions and collect them at the end of the import, you can use the Maatwebsite\Excel\Concerns\SkipsErrors trait.

<?php

namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Validators\Failure;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\SkipsOnError;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\SkipsErrors;

class UsersImport implements ToModel, WithValidation, SkipsOnError
{
    use Importable, SkipsErrors;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Every row that has errored, will have been skipped. We can now collect all the errors at the end:

$import = new UsersImport();
$import->import('users.xlsx');

dd($import->errors());
1
2
3
4

Row Validation without ToModel

If you are not using the ToModel concern, you can very easily do row validation by just using the Laravel validator.

<?php

namespace App\Imports;

use App\User;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Validator;
use Maatwebsite\Excel\Concerns\ToCollection;

class UsersImport implements ToCollection
{
    public function collection(Collection $rows)
    {
         Validator::make($rows->toArray(), [
             '*.0' => 'required',
         ])->validate();

        foreach ($rows as $row) {
            User::create([
                'name' => $row[0],
            ]);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

Validation rules

For a list of all validation rules, please refer to the Laravel document.