I'm using Laravel Excel from Maatwebsite to import a .xlsx file with headers, the problem is when I try to import the file, laravel returns an error saying:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'confirmation_number' cannot be null

My controller

public function createLettersFromImport(Request $request)
{
    if($request->hasFile('file')){
        Excel::import(new LetterImport, $request->file('file')->store('temp'));

        return redirect()->route('mailsender.index');
    }
}

My LetterImport.php

<?php

namespace App\Imports;

use App\Models\Confirmation;
use Illuminate\Support\Facades\Auth;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class LetterImport implements ToModel, WithHeadingRow
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new Confirmation([
            'confirmation_number'           => $row['confirmation_number'],
            'guest_name'                    => $row['guest_name'],
            'guest_address'                 => $row['guest_address'],
            'email'                         => $row['email'],
            'checkin'                       => $row['checkin'],
            'checkout'                      => $row['checkout']
        ]);
    }

    public function headingRow(): int
    {
        return 1;
    }
}

My Confirmation.php model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Confirmation extends Model
{
    use HasFactory;

    protected $table = 'confirmations';

    protected $fillable = ['confirmation_number', 'guest_name', 'guest_address', 'email', 'checkin', 'checkout'];
}

When I make dd($row) on LetterImport.php $row has data from the file .xlsx

But the error stills the same

My file to import:

Edit: I can successfully import .csv files with the same structure, but not .xlsx

CodePudding user response：

Looks like Laravel Excel is picking up "Phantom Rows". When you're entering data into Excel, if you hit "Enter", your cursor moves into an empty cell below your current row (vs hitting "Tab", which moves to the next Cell in the current row). When this happens, Excel inserts metadata into that Cell, which is likely being picked up as a filled in row in your import code. You have a couple ways to handle this:

1. Delete the Extra rows in your Excel File:

This one is simple; just select everything below the rows you want to import and Right Click > Delete Rows. That should clear out any metadata from those Cells/Rows, and on your next Import, should only target Cells/Rows that were actually filled in.

2. Import as a .csv file:

As you stated in your question, a .csv file worked fine. This is due to .csv being text-based, and empty rows would render as ,,,,,,, (a bunch of Commas, with no values). It would be a lot more obvious that there is an extra row(s), and deleting them would be much easier.

3. Handle this in your Code:

This is a little trickier. An easy way to handle this is to return null; if all row['properties'] values are null:

if ($row['confirmation_number'] === null && $row['guest_name'] === null) {
  return null;
}

return new Confirmation([
  'confirmation_number' => $row['confirmation_number'],
  // ...
]);

The syntax get's a little silly with multiple Columns you're checking, but the approach would work the same with a foreach() loop, for example:

$columns = ['confirmation_number', 'guest_name', 'guest_address', 'email', 'checkin', 'checkout'];

$allBlank = true;
foreach ($columns as $column) {
  // If _any_ column has a value, update `$allBlank` to `false`
  if ($row[$column] !== null) {
    $allBlank = false;
  }
}

// If `$allBlank` is `true`, you hit a phantom row; return `null`
if ($allBlank) {
  return null;
}

// All good; return a new `Confirmation`
return new Confirmation([
  'confirmation_number' => $row['confirmation_number'],
  // ...
]);

Also have a look at Row Validation; this might be able to validate and ignore Phantom Rows by making each column value required.