How to skip blank rows in maatwebsite-excel 3.1 for model-way import on Laravel

Question

I'm working on laravel project with maatwebsite-exvel 3.1 to import excel file from file uploding method. This is my StudentsImport class.

public function model(array $row)
{
    return new Student([
        'school_uuid' => Auth::user()->school_uuid,
        'cardid'     => $row[0],
        'prefix'    => $row[1], 
        'name'    => $row[2], 
        'lastname'    => $row[3], 
        'dob'    => $row[4], 
        'address'    => $row[5], 
        'phone'    => $row[6], 
    ]);
}

And below is controller.

 Excel::import(new StudentsImport,  $request->file('file'));

Code work fine. I can import excel's data to database but blank rows also imported. I would like to filter/validate to skip these blanks before put to database. Any advice or guidance on this would be greatly appreciated, Thanks

Answer 1

As per the package documentation, support is provided for using Laravel's validation to prevent invalid rows from being inserted.

To use it, implement the WithValidation interface on your importer class and add a rules() method that return the validation rules that should be used to ensure that the row is valid.

public function rules(): array
{
    return [
        '0' => 'required|string',
        '1' => 'required|string',
        '2' => 'required|numeric',
        // so on
    ];
}

Answer 2

Please note, the rules() method does not skip empty rows, but throws an error which leads to failure of import and the rows which where validated will be rolled back.

However i found this feature exist in the documentations, simply you have to implement this to your import class: "Maatwebsite\Excel\Concerns\SkipsOnFailure"

Like this for exemple:

class UsersImport implements ToModel, WithValidation, SkipsOnFailure {
    // Your import class
}

Then define a method within your import class to handle the errors:

/**
 * @param Failure[] $failures
 */
public function onFailure(Failure ...$failures)
{
    // Handle the failures how you'd like.
}

Now the rules() method won't throw errors anymore on empty rows and you can handle errors silently, you should still be using the rules() method tho, simply because you need it to throw the errors which are now able to be handled by you, perhaps you could log them to a file or however you do it normally.

In the end you will be able to collect all errors, here is an example from the docs:

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

dd($import->errors());

So, now you can silently catch the errors and eventually return it to your request, you can even return detailed info like this:

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.
}

This is not copy paste material ok, i just explained how you can access the errors based on examples given in the docs, i leave it up to you to handle this data.

Here is a link to the docs where i found all this info: https://docs.laravel-excel.com/3.1/imports/validation.html#skipping-failures

Answer 3

Try using ToCollection and then try if($row->filter()->isNotEmpty())

public function collection(Collection $rows)
   {
      foreach($rows as $row) {
        if($row->filter()->isNotEmpty()){
            // you logic can go here
           }
        }   
   }

Don't forget to include use Maatwebsite\Excel\Concerns\ToCollection; It worked for me perfect

Reference can be found here.

Answer 4

Instead of passing the file directly to this function directly, create a function to filter the data having the blank fields something like below:

function returnNotEmpty($array){
    return array_filter($array, function($i){
        return !empty($i);
    });
}

Use the function like this:

Excel::import(new StudentsImport,  $this->returnNotEmpty($request->file('file'))); 

Answer 5

You can also skip rows base on the validation that you make. You can put in your model a return null if one of your requests ar not cover

public function model(array $row)
{
    if ($row[2] === null || $row[3] === null ...) {
      return null;
    }
    return new Student([
        'school_uuid' => Auth::user()->school_uuid,
        'cardid'     => $row[0],
        'prefix'    => $row[1], 
        'name'    => $row[2], 
        'lastname'    => $row[3], 
        'dob'    => $row[4], 
        'address'    => $row[5], 
        'phone'    => $row[6], 
    ]);
}

And also you can add in the rules in the required field an validation rule "required_with" if in other fields exists something the validation will drop and if don't exists to skip the row returning null in the model area.

public function rules(): array
{
    return [
        '0' => 'required_with:*.1,*.2,...', // here to be the others rows if they are populate to trow an validation error, 
        '1' => 'string|nullable|required_with:*.1,*.2,...',',
        // so on
    ];
}

Answer 6

You have to create a collection and use it with a filter rather than an array. First trim the value as a space is not empty. Then make it a collection after that filter the empty row.

    public function collection(Collection $rows)
    {
    foreach($rows as $row) {
        $em1=trim($row[0]);           

        $em = collect([$em1]);
       

        if($em->filter()->isNotEmpty()){
            // you logic can go here

            $user = Content::create([
                'email'     => $row[0],
                'phone'    => $row[1],
                'first_name'    => $row[2],
                'last_name'    => $row[3],
                'zip_code'    => $row[4],
                'address'    => $row[5],
            ]);
        }
    }
}