update table with csv using laravel

Question

I'm trying to update a table using Maatwebsite/Laravel-Excel.

public function import(Request $request) { if($request->file('imported-file')) { $path = $request->file('imported-file')->getRealPath(); $data = Excel::load($path, function($reader) { })->get(); if(!empty($data) && $data->count()) { foreach ($data->toArray() as $row) { if(!empty($row)) { $dataArray[] = [ //'name' => $row['name'], 'age' => $row['age'], 'phone' => $row['phone'], //'created_at' => $row['created_at'] ]; } if(!empty($dataArray)) { //Item::insert($dataArray); DB::table('items') ->where('name', $row['name'])->update($dataArray); return view('imported')->with('success', 'Course updated'); } } } } }

But its giving error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list' (SQL: update items set 0 = 20 where name = james

Here's my csv

name,age,phone James,20,888839939 Joseph,54,3444444 Hanson,30,99999999

The above is the csv file i'm trying to update.

@TheFallen SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list' (SQL: update items set 0 = 20 where name = james — James Bondze
– James Bondze, Commented Apr 18, 2018 at 13:59

thefallen · Accepted Answer · 2018-04-18 14:04:48Z

The problem is that $dataArray is an array of arrays, so to make it work you have to loop each one:

if(!empty($dataArray)) { foreach ($dataArray as $array) { DB::table('items') ->where('name', $row['name']) ->update($array); } return view('imported')->with('success', 'Course updated'); }

But this wouldn't make much sense, because every time it would be updating the row with name = $row['name'], so you probbaly need to update the line where you set a value to the $dataArray from $dataArray[] = ... to $dataArray = ...*, so it could have a single value.

This worked partially, only the first row of the file was picked, the other rows was ignore. I tried updating $dataArray[] = ... to $dataArray = ...* but it threw an exception.

James Bondze · Accepted Answer · 2018-04-19 07:28:54Z

In case any body comes across this, this is how i solved it.

public function import(Request $request) { if($request->file('imported-file')) { $path = $request->file('imported-file')->getRealPath(); Excel::load($path)->each(function (Collection $csvLine) { DB::table('items') ->where('id', $csvLine->get('id')) ->update(['name' => $csvLine->get('name'),'phone' => $csvLine->get('phone'),'age' => $csvLine->get('age')]); }); return view('imported')->with('success', 'Course updated'); } }

I used the each() collection method to loop through the csv file and it won the battle.

Collectives™ on Stack Overflow

update table with csv using laravel

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related