[Solved] How to import a large Excel file with data validation in a queue?

[BehroozBvk]

Hi
I want to use row validation on queue
That way, if there is a validation error, I will save it in another file and show it to the user at the end to be notified. How can this be done?

Question: Does the queue stop when a validation error occurs? And the job fails?

[BehroozBvk]

✅ My solution to do this:
I would be happy if someone has a better solution to suggest.

1- Create abstract Class with name ToCollectionImport
ToCollectionImport.php

<?php


namespace App\Imports;


use Illuminate\Support\Facades\Log;
use Throwable;
use Illuminate\Support\Str;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Validator;
use Maatwebsite\Excel\Validators\Failure;
use Maatwebsite\Excel\Concerns\SkipsOnError;
use Maatwebsite\Excel\Concerns\ToCollection;
use Illuminate\Validation\ValidationException;
use Maatwebsite\Excel\Concerns\SkipsOnFailure;
use Maatwebsite\Excel\Concerns\WithValidation;

abstract class ToCollectionImport implements ToCollection
{
    public static $allFailures;
    /**
     * Set additional user resolve callback.

    abstract public function processImport(Collection $rows);

    abstract public function rules(): array;

    abstract public function getUser();

    abstract public static function getOrganization();

    /**
     * @param Collection $rows
     * @throws ValidationException|Throwable
     */
    public function collection(Collection $rows)
    {
        if ($this instanceof WithValidation) {
            $rows = $this->validate($rows);
        }

        try {
            $this->processImport($rows);
        } catch (Throwable $e) {
            $this->recordOrThrowErrors($e);
        }

        if ($this->failures()->count() > 0) {
            self::$allFailures = $this->failures();
        }

        if ($this->errors()->count() > 0) {
            Log::error($this->errors());
        }
    }

    /**
     * Validate given collection data.
     *
     * @param Collection $rows
     *
     * @return Collection
     * @throws ValidationException
     *
     */
    protected function validate(Collection $rows): Collection
    {
        $validator = Validator::make($rows->toArray(), $this->rules());

        if (!$validator->fails()) {
            return $rows;
        }

        if ($this instanceof SkipsOnFailure) {
            $this->onFailure(
                ...$this->collectErrors($validator, $rows)
            );

            $keysCausingFailure = collect($validator->errors()->keys())->map(function ($key) {
                return Str::before($key, '.');
            })->values()->toArray();

            return $rows->except($keysCausingFailure);
        }

        throw new ValidationException($validator);
    }

    /**
     * Get all validation errors.
     *
     * @param $validator
     * @param Collection $rows
     *
     * @return array
     */
    protected function collectErrors($validator, Collection $rows): array
    {
        $failures = [];

        foreach ($validator->errors() as $attribute => $messages) {
            $row = strtok($attribute, '.');
            $attributeName = strtok('');
            $attributeName = $attributes['*.' . $attributeName] ?? $attributeName;

            $failures[] = new Failure(
                $row,
                $attributeName,
                str_replace($attribute, $attributeName, $messages),
                $rows[$row] ?? []
            );
        }
        return $failures;
    }

    /**
     * Records an error or throws its exception.
     *
     * @param Throwable $error
     *
     * @return void
     * @throws Throwable
     */
    protected function recordOrThrowErrors(Throwable $error)
    {
        if ($this instanceof SkipsOnError) {
            return $this->onError($error);
        }

        throw $error;
    }
}

2- Create UsersImport Class
UsersImport.php

<?php

namespace App\Imports;

use App\Exports\UsersFailuresImportedExport;
use App\Models\Organization;
use App\Models\User;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Concerns\SkipsEmptyRows;
use Maatwebsite\Excel\Concerns\SkipsErrors;
use Maatwebsite\Excel\Concerns\SkipsFailures;
use Maatwebsite\Excel\Concerns\SkipsOnError;
use Maatwebsite\Excel\Concerns\SkipsOnFailure;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Events\AfterImport;
use Spatie\Permission\Models\Role;

class UsersImport extends ToCollectionImport implements SkipsOnFailure
    , SkipsOnError
    , SkipsEmptyRows
    , WithValidation
    , WithStartRow
    , WithHeadingRow
    , WithChunkReading
    , WithBatchInserts
    , ShouldQueue
    , WithEvents
{
    use Importable, SkipsErrors, SkipsFailures, RegistersEventListeners;

    public static function afterImport(AfterImport $event)
    {
        if (!empty(self::$allFailures)) {
            $path = "excels/userFailures/import_failures_" . now()->format('Y-m-d H:i:s') . '.xlsx';
            (new UsersFailuresImportedExport(self::$allFailures))->store($path);
        }
    }

    /**
     * skip heading row and start next row.
     * @return int
     */
    public function startRow(): int
    {
        return 2;
    }

    /**
     * @return string[][]
     */
    public function rules(): array
    {
        return [
            '*.first_name' => ['required'],
            '*.last_name' => ['required'],
            '*.username' => ['required', 'unique:users,username'],
            '*.password' => ['required'],
            '*.mobile' => ['unique:users,mobile'],
            '*.email' => ['email', 'unique:users,email'],
        ];
    }


    /**
     * @return int
     */
    public function chunkSize(): int
    {
        return 5000;
    }

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


    /**
     * @param Collection $rows
     * @return User
     */
    public function processImport(Collection $rows): User
    {
        $role = Role::findByName('normal-user');
        foreach ($rows as $row) {
            $user = User::create([
                'first_name' => $row['first_name'],
                'last_name' => $row['last_name'],
                'username' => $row['username'],
                'password' => Hash::make($row['password']),
                'mobile' => $row['mobile'],
                'email' => $row['email'],
            ]);
            if ($user) {
                $user->assignRole([$role->id => ['organization_id' => Organization::DEFAULT]]);
            }
        }
    }

    public function getUser()
    {
        return 125;
    }

    public static function getOrganization()
    {
        return 1;
    }
}

3- Create UsersFailuresImportedExport Class for export validations failures.
UsersFailuresImportedExport.php

<?php

namespace App\Exports;

use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class UsersFailuresImportedExport implements FromCollection,
    ShouldAutoSize,
    WithEvents,
    WithHeadings,
    ShouldQueue
{
    use Exportable;

    private int $row_count;
    protected Collection $failures;

    public function __construct(Collection $failures)
    {
        $getAllFailures = $failures->map(function ($item) {
            return ['row' => $item->row(), 'attribute' => $item->attribute(), 'errors' => $item->errors()];
        })->groupBy('row');
        $errors = [];
        $collection = new Collection();
        foreach ($getAllFailures->toArray() as $row => $error) {
            foreach ($error as $e) {
                $errors[$row][] = str_replace($e['attribute'], trans("validation.attributes.{$e['attribute']}"), $e['errors'][0]);
            }
            $collection->push((object)[$row, implode("\r\n", $errors[$row])]);
        }
        $this->row_count = $collection->count() + 1;
        $this->failures = $collection;

    }

    public function collection(): Collection
    {
        return $this->failures;
    }

    public function registerEvents(): array
    {
        $rowCount = $this->row_count;
        return [
            AfterSheet::class => function (AfterSheet $event) use ($rowCount) {
                $event->sheet->getDelegate()->setRightToLeft(true)
                    ->getStyle("A1:B{$rowCount}")
                    ->getAlignment()
                    ->setHorizontal(Alignment::HORIZONTAL_CENTER)
                    ->setVertical(Alignment::VERTICAL_CENTER);
                $event->sheet->styleCells(
                    "A1:B1",
                    [
                        'font' => [
                            'bold' => true,
                            'color' => ['rgb' => 'ffffff']
                        ]
                    ]
                );
                $event->sheet->getDelegate()->getStyle('A1:B1')->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()->setRGB('f44336');
            }
        ];
    }

    public function headings(): array
    {
        return [
            'row',
            'errors'
        ];
    }
}

4- Use in controller

UserController.php

  public function import(Request $request): RedirectResponse
    {
        $request->validate([
            'file' => 'required'
        ]);

        $import = new UsersImport;
        $import->import($request->file('file'));
}