[Question] How to skip empty rows (3.1)

[mCassy]

• Package version: 3.1

Description

How to skip empty rows in the doc?

[ghost]

Thanks for submitting the ticket. Unfortunately the information you provided is incomplete. We need to know which version you use and how to reproduce it. Please include code examples. Before we can pick it up, please check (https://github.com/Maatwebsite/Laravel-Excel/blob/3.0/.github/ISSUE_TEMPLATE.md) and add the missing information. To make processing of this ticket a lot easier, please make sure to check (https://laravel-excel.maatwebsite.nl/docs/3.0/getting-started/contributing) and double-check if you have filled in the issue template correctly. This will allow us to pick up your ticket more efficiently. Issues that follow the guidelines correctly will get priority over other issues.

[patrickbrouwers]

Duplicate of #1834

[Otienoh]

use ToCollection method the wrap everything within if($row->filter()->isNotEmpty())

public function collection(Collection $rows)
   {
      foreach($rows as $row) {
   	 	if($row->filter()->isNotEmpty()){
   			// you logic can go here
      
       		$user = User::create([
               	'name' => ucwords($row['name']),
               	'class' => $row['class'],
   				...
   			]);
   		}
   	}	
   }

[notflip]

How does it work for the toModel way?

[patrickbrouwers]

You can return null in toModel, will skip those rows.

[seven21]

Hello,
I have tried to use $row->filter()->isNotEmpty() but due to the fact that I use WithValidation the codes stops with an validation error. My rules look like this:

public function rules(): array { return [ 'name' => 'required', '*.name' => 'required', 'email' => 'required|email', '*.email' => 'required|email', 'discount' => 'nullable|numeric', '*.discount' => 'nullable|numeric', ]; }

Unfortunately, if you have an excel file an delete the content of some rows at the end of the excel file Laravel-Excel still recognizes them as not empty. Then the validation fails. I want to skip such rows before validation.

[mohamednizar]

@seven21 , did you find a solution for this ? does anyone got resolved this issue ?

[seven21]

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
public function model(array $row) {if(!array_filter($row)) { return null;} return new Customer([..... 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.

[mohamednizar]

@seven21 Thank you very much for your instance and useful message, But I'm also thinking this isn't a good way if we actually using validations.

[siarheipashkevich]

@mohamednizar how did you resolve this issue?

[mohamednizar]

I didn't completely resolve the issue, but for my solution I added following custom functions to return the batch size and row limit. the row limit only will be count if data exists in the column.

 public function limit(): int {
        $highestColumn = $this->worksheet->getHighestDataColumn(3);
        $higestRow = 0;
        for ($row = $this->startRow(); $row <= $this->highestRow; $row++) {
            $rowData = $this->worksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
            if (isEmptyRow(reset($rowData))) {
                continue;
            } else {
                $higestRow += 1;
            }
        }
        return $higestRow;
    }

   public function batchSize(): int {
        $highestColumn = $this->worksheet->getHighestDataColumn(3);
        $higestRow = 1;
        for ($row = $this->startRow(); $row <= $this->highestRow; $row++) {
            $rowData = $this->worksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
            if (isEmptyRow(reset($rowData))) {
                continue;
            } else {
                $higestRow += 1;
            }
        }
        if ($higestRow == 0) {
            exit;
        } else {
            return $higestRow;
        }
    }

Some time this may help you to resolve some sort of problems.

[MarJose123]

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

from:

        /*
        |--------------------------------------------------------------------------
        | Ignore Empty
        |--------------------------------------------------------------------------
        |
        | When dealing with imports, you might be interested in ignoring
        | rows that have null values or empty strings. By default rows
        | containing empty strings or empty values are not ignored but can be
        | ignored by enabling the setting ignore_empty to true.
        |
        */
        'ignore_empty' => false,

to:

        /*
        |--------------------------------------------------------------------------
        | Ignore Empty
        |--------------------------------------------------------------------------
        |
        | When dealing with imports, you might be interested in ignoring
        | rows that have null values or empty strings. By default rows
        | containing empty strings or empty values are not ignored but can be
        | ignored by enabling the setting ignore_empty to true.
        |
        */
        'ignore_empty' => true,

[jonjieviduya]

@whoami213 It works. Thanks a lot man