Maatwebsite Excel 3.1 : how do I skip duplicate data when imported?

Question

I made import data using Excel on Laravel Maatwebsite Excel 3.1. but when in the database there is the same data (Primary Key) then there is an error message

integrity constraint violation: 1062 Duplicate entry '188281' for key 'PRIMARY'

I have tried to understand the documentation of the Maatwebsite but it still fails

public function storeData(Request $request)
        {
            //VALIDASI
            $this->validate($request, [
                'file' => 'required|mimes:xls,xlsx'
            ]);
        if ($request->hasFile('file')) {
            $file = $request->file('file');

            // dd($file); //GET FILE;
            Excel::import(new MahasiswaImport, $file); //IMPORT FILE
            return redirect('/mahasiswa')->with(['status' => 'Upload success']);
        }
        return redirect('/mahasiswa')->with(['error' => 'Please choose file before']);
    }



<?php

namespace App\Imports;

use App\Mahasiswa;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\Importable;

class MahasiswaImport implements ToModel, WithHeadingRow, WithChunkReading, ShouldQueue

{

  use Importable;
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
    return new Mahasiswa([
      'nim' => $row['nim'],
      'slug' => str_slug($row['nim']),
      'nama_mahasiswa' => $row['nama_mahasiswa'],
      'email' => $row['email'],
      'kode_kelas' => $row['kode_kelas'],
      'alamat' => $row['alamat'],
      'kode_jurusan' => $row['kode_jurusan'],
      'kode_tahun_akademik' => $row['kode_tahun_akademik'],
      'no_hp' => $row['no_hp'],
      'tempat_lahir' => $row['tempat_lahir'],
      // 'tanggal_lahir' => $row['tanggal_lahir'],
      'password' => $row['password']


    ]);
}


public function chunkSize(): int
{
    return 1000;
}

}

Answer 1

You need to validate your row. You can read docs about Row Validation.

So you need something like this in your Import:

public function rules(): array
{
    return [
        'nim' => Rule::unique('mahasiswa', 'nim'), // Table name, field in your db
    ];
}

public function customValidationMessages()
{
    return [
        'nim.unique' => 'Custom message',
    ];
}

Or some deprecated way:

public function model(array $data)
{
    foreach($data as $row) {
       $data = Mahasiswa::find($row['nim']);
       if (empty($data)) {
          return new Mahasiswa([
                 'nim' => $row['nim'],
                 'slug' => str_slug($row['nim']),
                 ...
                 ]);
       } 
   }
}

Answer 2

Using Maatwebsite Excel 3.1; how do I skip duplicate data when imported? You can check if any column already has the value in the db like this.

public function model(array $row)
{
    $bin = DB::table('bin_info')->get();

    // Get all bin number from the $bin collection
    $bin_number = $bin->pluck('bin_number');
    
    // Checking if the bin number is already in the database
    if ($bin_number->contains($row[0]) == false) 
    {
        return new Bin([
            'bin_number' => $this->binNumberCheck($row[0]),
            'type' => $this->typeCheck($row[1]),
            'product' => $row[2],
            'category' => $row[3],
            'bank' => $row[4],
        ]);
    }
    else null; // if the bin number is already in the database, return null
}