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

}

Did you try something like this: `$data = Mahasiswa::create([ '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'], 'password' => $row['password'] ]); return $data;` — mare96, Jul 11 '19 at 09:56
Your error is from sql because you have duplicated key for your primary key. Are some data from `$row` your primary key? — mare96, Jul 11 '19 at 09:58
Yes. NIM is Primary key,. therefore if the data is already in the database, then the data is skipped — Tammam, Jul 11 '19 at 10:02
Yes, i need validation. sorry if the statement is post ambiguous — Tammam, Jul 11 '19 at 10:12

score 4 · Accepted Answer · edited Jun 13 '22 at 10:04

4

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']),
                 ...
                 ]);
       } 
   
}

edited Jun 13 '22 at 10:04

MOHD FARAZ

160
3
7

answered Jul 11 '19 at 10:25

mare96

3,749
1
16
28

sorry, but an error appears "Class 'App\Imports\Rule' not found" why? – Tammam Jul 11 '19 at 10:30
Add `use Illuminate\Validation\Rule;` in your Import, and let me know whats happening. – mare96 Jul 11 '19 at 10:32
it can, but the data is not inserted into the database. for example, I enter 2 data into excel, 1 data is in the database and 1 data does not yet exist. Can the existing data in Excel be skipped and the existing data stored? – Tammam Jul 11 '19 at 10:38
1

Try with second way, I updated my answer. That should work. – mare96 Jul 11 '19 at 10:44
You are welcome. Maybe you can try to find better way but I think it's helpful. Good luck! – mare96 Jul 11 '19 at 10:50
It showing following error: #message: "Trying to access array offset on value of type int" – MOHD FARAZ Jun 12 '22 at 16:26

score 0 · Answer 2 · edited Apr 29 '22 at 20:00

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
}

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

2 Answers2