CODEWITHSUNDEEP
laravelmaatwebsite-excel
joenpc npcsolution
joenpc npcsolution

Reputation: 767

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

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

Upvotes: 7

Views: 28710

Answers (9)

code hunger
code hunger

Reputation: 1

I think the best way to it via skip empty rows, follow below steps

  1. add this use Maatwebsite\Excel\Concerns\SkipsEmptyRows; in your namespace
  2. then implement in this manner class ParadeImport implements ToModel, WithHeadingRow, WithCalculatedFormulas, WithChunkReading, SkipsEmptyRows {}

I hope now you understand how you skip empty rows in maatwebsite package

Upvotes: 0

JunaidAS009
JunaidAS009

Reputation: 1

you can ignore the empty row by updating the config/excel.php file.

from:

'ignore_empty' => false,

to:

 'ignore_empty' => true,

Upvotes: 0

MJ07
MJ07

Reputation: 26

If you are using maatwebsite-exvel 3.1, you can use SkipsEmptyRows concern to skip empty rows

like this :

<?php
    
    namespace App\Imports;
    
    use App\Models\Student;
    use Maatwebsite\Excel\Concerns\SkipsEmptyRows;
    use Maatwebsite\Excel\Concerns\ToModel;
    
    class StudentsImport implements ToModel, SkipsEmptyRows
    {            
        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], 
          ]);
       }
    }

as mentioned in documentation

Upvotes: 0

Hadayat Niazi
Hadayat Niazi

Reputation: 2480

I use a workaround which helps in my case. Validation is extended by nullable on all rows to avoid errors and then In the method, I have added below code and it worked

public function model(array $row) 
{
  if(!array_filter($row)) {
     return null;
  } 

  // your code will be here
   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 skip these rows at this point. Not really happy with this as I cannot return an useful error message but as I found no solution the best way to get the code running. I found this from here. https://github.com/SpartnerNL/Laravel-Excel/issues/1861#issuecomment-520753182

Upvotes: 4

user4370090
user4370090

Reputation:

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],
            ]);
        }
    }
}

Upvotes: 0

Ali
Ali

Reputation: 171

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.

Upvotes: 2

Ghiurutan Catalin
Ghiurutan Catalin

Reputation: 1

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
    ];
}

Upvotes: 0

Andy
Andy

Reputation: 3012

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

Upvotes: 4

atymic
atymic

Reputation: 3128

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
    ];
}

Upvotes: 6

Related Questions