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

1

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.

answered Apr 18, 2018 at 14:04

thefallen

9,8102 gold badges39 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Bondze Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related