Laravel Excel validation import

Question

I am using the Maatwebsite/excel component to import excel files. In this case, importing users. I need to validate that the mail does not exist and then import the unique data

I have this code in the import file

namespace App\Imports;

use App\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;

use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithValidation;

class UsersImport implements ToModel, WithValidation
{
    use Importable;
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        $user = User::create([
            'name'     => $row[0],
            'email'    => $row[1], 
            'password' => Hash::make($row[2]),
        ]);

        $user->assignRole('guest');
    }

    public function rules(): array
    {
        return [
            '0' => 'required|string',
            '1' => 'unique:users',
        ];
    }

}

And this code in my controller:

public function import() 
{
    Excel::import(new UsersImport, request()->file('file'));

    return back()->with('success', 'Importado con éxito!');
}

When I want to import duplicate data, I get the following error

SQLSTATE[42S22]: Column not found: 1054 Unknown column '1.1' in 'where clause' (SQL: select count(*) as aggregate from users where 1.1 = [email protected])

Follow this stackoverflow.com/questions/37769433/laravel-excel-validation Link — user9968800
– user9968800, Commented Aug 24, 2019 at 17:40

Robbin Benard · Accepted Answer · 2019-08-24 18:33:05Z

5

The validator's exist rule will use the key of the array (1 in your case) as the column to search inside the users table.

You can specify the column like this and it should work.

'1' => 'unique:users,email',

From: https://laravel.com/docs/5.8/validation#rule-exists

answered Aug 24, 2019 at 18:33

Robbin Benard

1,68713 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Drennos Over a year ago

Excelent! that was

Drennos · Accepted Answer · 2019-08-24 19:15:14Z

I found the solution for my question

namespace App\Imports;

use App\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;

use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithValidation;

class UsersImport implements ToModel, WithValidation
{
    use Importable;
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
public function rules(): array
{
    return [
        '1' => 'unique:users,email'
    ];

}

public function customValidationMessages()
{
    return [
        '1.unique' => 'Correo ya esta en uso.',
    ];
}

public function model(array $row)
{
    $user = User::create([
        'name'     => $row[0],
        'email'    => $row[1], 
        'password' => Hash::make($row[2]),
    ]);

    $user->assignRole('guest');
}


}

Collectives™ on Stack Overflow

Laravel Excel validation import

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related