How to update data in database using import excel

Question

how to update data in database with import excel. i am using laravel 5.7 and maatwebsite 3.1

this is my controller :

public function import()
{
   $data = Excel::toArray(new ProdukImport, request()->file('file')); 
   if ($data) {
       DB::table('produk')
            ->where('id_produk', $data['id'])
            ->update($data);
   }
}

This is my Import Class:

<?php

 namespace App\Imports;

 use App\Produk;
 use Maatwebsite\Excel\Concerns\ToModel;
 use Maatwebsite\Excel\Concerns\WithHeadingRow;


 class ProdukImport implements ToModel, WithHeadingRow
 {
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */
    public function model(array $row)
    {
       return new Produk([
          'id_produk' => $row['id'],
          'nama_produk' => $row['produk'],
          'harga_jual' => $row['harga']
       ]);
     }
  }

this dd($data) result :

array:1 [▼
   0 => array:8 [▼
      0 => array:3 [▼
         "id" => 1.0
         "produk" => "Pomade"
         "harga" => 90000.0
      ]
      1 => array:3 [▼
         "id" => 2.0
         "produk" => "Shampoo"
         "harga" => 90000.0
      ]
      2 => array:3 [▼
         "id" => 3.0
         "produk" => "Sikat WC"
         "harga" => 90000.0
      ]
    ]
]

the $data result is from this :

 $data = Excel::toArray(new ProdukImport, request()->file('file'));

array:1 [▼ 0 => array:8 [▼ 0 => array:3 [▼ "id" => 1.0 "produk" => "Pomade" "harga" => 90000.0 ] 1 => array:3 [▶] 2 => array:3 [▶] 3 => array:3 [▶] 4 => array:3 [▶] 5 => array:3 [▶] 6 => array:3 [▶] 7 => array:3 [▶] ] ] — Rahmat Effendi, Jan 22 '19 at 09:40
i have tried it, and i got this error : SQLSTATE[42S22]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'id'. (SQL: update [produk] set [id] = 1, [produk] = Pomade, [harga] = 90000 where [id_produk] = 1) in my excel file, header table like this : Id | Produk | Harga and in my db the table like this : id_produk | nama_produk | harga_jual . There is something wrong in my header table in excel file? — Rahmat Effendi, Jan 22 '19 at 10:18

Mozammil · Accepted Answer · 2019-01-22T10:29:56.887

5

Based on the structure of your $data array, you could probably achieve what you want with something like this:

public function import()
{
    $data = Excel::toArray(new ProdukImport, request()->file('file')); 

    return collect(head($data))
        ->each(function ($row, $key) {
            DB::table('produk')
                ->where('id_produk', $row['id'])
                ->update(array_except($row, ['id']));
        });
}

edited Jan 22 '19 at 10:29

answered Jan 22 '19 at 09:55

Mozammil

8,520
15
29

i have tried it, and i got this error : SQLSTATE[42S22]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'id'. (SQL: update [produk] set [id] = 1, [produk] = Pomade, [harga] = 90000 where [id_produk] = 1) in my excel file, header table like this : Id | Produk | Harga and in my db the table like this : id_produk | nama_produk | harga_jual . There is something wrong in my header table in excel file? – Rahmat Effendi Jan 22 '19 at 10:16
Edited my answer, but I am confused. Do you have a field called `id` in `produk`? – Mozammil Jan 22 '19 at 10:19
in my database, i'm using id_produk in produk table. And in my header excel file i'm using id – Rahmat Effendi Jan 22 '19 at 10:27
I understand. I think the updated answer should do the trick. Let me know if it works :) – Mozammil Jan 22 '19 at 10:28

score 2 · Answer 2 · answered Nov 24 '20 at 11:07

I had the same issue. Here's how I did it.

My controller looks like this:

public function import(Request $request){
        try {

            Excel::import(new ProductImport, $request->file('file')->store('temp') );
            return redirect()->back()->with('response','Data was imported successfully!');
        } catch (\Exception $exception){
            return redirect()->back()->withErrors(["msq"=>$exception->getMessage()]);
        }

    }

And this is the model method on my ProductImport class

public function model(array $row)
    {
        $product = new Product();
// row[0] is the ID
        $product = $product->find($row[0]);
// if product exists and the value also exists
        if ($product and $row[3]){
            $product->update([
                'price'=>$row[3]
            ]);
            return $product;
        }
    }

score 0 · Answer 3 · answered Aug 27 '23 at 20:58

0

return collect(head($data))
        ->each(function ($row, $key) {
            DB::table('produk')
                ->where('id_produk', $row['id'])
                ->update(array_except($row, ['id']));
        });

to

return collect(head($data))
        ->each(function ($row, $key) {
            DB::table('produk')
                ->where('id_produk', $row['id'])
                ->update(Arr::except($row, ['id']));
        });

and use use Illuminate\Support\Arr;

answered Aug 27 '23 at 20:58

Nova Andriana

21
5

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 29 '23 at 14:06

How to update data in database using import excel

3 Answers3