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

Issue

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

}

dd($row) in model

Solution

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)
{
    
       $data = Mahasiswa::find($row['nim']);
       if (empty($data)) {
          return new Mahasiswa([
                 'nim' => $row['nim'],
                 'slug' => str_slug($row['nim']),
                 ...
                 ]);
       } 
   
}

Answered By – mare96

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published