Can't import less than 5 rows in Laravel/Excel

Question

I am trying to import multiple rows from Excel file to the database. If rows are equal to 5 or greater the records successfully adds to the database but if I try less than 5 records it gives SQL error.

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'customer_name' cannot be null (SQL: insert into `shipments` (`user_id`, `customer_name`, `customer_address`, `customer_email`, `customer_phone`, `destination_country`, `destination_city`, `service_type`, `tb_date`, `tb_time`, `pickup_location`, `product_name`, `quantity`, `consignment_no`, `weights_id`, `product_value`, `product_reference`, `remarks`, `status`, `updated_at`, `created_at`) values (2, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1220125, 1, ?, ?, ?, Booked, 2020-10-26 12:26:29, 2020-10-26 12:26:29))

This is my import class

<?php

namespace App\Imports;

use App\Shipment;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
Use Auth;
use App\Settings;
use App\ShipmentTracking;
use App\Alert;

class ShipmentImport implements ToCollection, WithHeadingRow
{
    public function  __construct($weight_id)
    {
        $this->weight_id= $weight_id;
    }

public function collection(Collection $rows)
{
    $user_id = Auth::User()->id;
    

    foreach ($rows as $row) 
    {
        $consign_no = Settings::where('name', 'consign_no')->first()->value;
        $increment = $consign_no+1;

        $initial = "12".date("y");
        $consignment_no1 = $initial.$increment;

        $update_setting = Settings::where('name', 'consign_no')->first();
        $update_setting->value = $increment;
        $update_setting->save();

        $shipment = Shipment::create([
            'user_id'                   => $user_id,
            'customer_name'             => $row['customer_name'],
            'customer_address'          => $row['customer_address'],
            'customer_email'            => $row['customer_email'],
            'customer_phone'            => $row['customer_phone'],
            'destination_country'       => $row['destination_country'],
            'destination_city'          => $row['destination_city'],
            'service_type'              => $row['service_type'],
            'tb_date'                   => $row['tb_date'],
            'tb_time'                   => $row['tb_time'],
            'pickup_location'           => $row['pickup_address'],
            'product_name'              => $row['product_name'],
            'quantity'                  => $row['product_quantity'],
            'consignment_no'            => $consignment_no1,
            'weights_id'                => $this->weight_id,
            'product_value'             => $row['product_value'],
            'product_reference'         => $row['product_reference'],
            'remarks'                   => $row['remarks'],
            'status'                    => 'Booked',
        ]);  
    }
  }
}

I have Laravel version 7. I am using the package for Excel file is: maatwebsite/excel: 3.1

do some validation to check if the keys are actually set before you create the model, you probably have a blank row, hidden row or something similar going on — Ballard, Oct 26 '20 at 09:02
that doesn't change what I said, do some debug of the first 5 rows, work out if its 4, 3, 2,1 etc... — Ballard, Oct 26 '20 at 09:41

score 0 · Answer 1 · answered Aug 03 '21 at 22:38

This might be and old question but this could help you

    try {
        Excel::import(new YourImportClass(), request()->file('importar_datos'));
        return back()->with("toast_success", "Empleados importados correctamente");
      }catch(\Exception $e)
      {
        return back()->with("toast_error", "Line error " . $e->failures()[0]->row() . " on column: " .
         $e->failures()[0]->attribute() . "with message " . $e->failures()[0]->errors()[0] );
      }

On your Import class implement this

use Maatwebsite\Excel\Concerns\WithValidation;
class YourClass implements WithValidation{
 

    public function rules(): array
    {
        return [
            //Your validation logic
        ];
        
    }
}

Theres also a function called SkipEmptyrow you can check the docs here https://docs.laravel-excel.com/3.1/imports/validation.html#skipping-empty-rows

Can't import less than 5 rows in Laravel/Excel

1 Answers1