phplaravelcyberduck

Insert data from excel (xlsm,xls) in laravel (cyber-duck)


Currently I can get the json data from my excel file with format like this

sample.xlsx

    $excel = Importer::make('Excel');
    $excel->hasHeader(true);
    $excel->load($savePath.$fileName);
    $collection = $excel->getCollection();

    if(sizeof($collection[1]) == 5)
    {
        return $collection;
    }
    else
    {
        return redirect()
        ->back()
        ->with(['errors'=> [0=> 'Please provide date in file according to your format.']])
        ->with('modal',$modal);
    }

output

[{"id":1,"first_name":"j.doe17","last_name":"Jun Doe","email":"j.doe@gmail.com","birthdate":{"date":"1996-09-07 00:00:00.000000","timezone_type":3,"timezone":"Asia\/Taipei"}},{"id":2,"first_name":"j.doe18","last_name":"Jun Doe","email":"jan.doe@gmail.com","birthdate":{"date":"1996-09-07 00:00:00.000000","timezone_type":3,"timezone":"Asia\/Taipei"}}]

Now I'm trying to use this code, just insert those json data to my database table > tbl_sampleimport

         foreach ($collection->toArray() as $key => $value)  {
            foreach ($value as $row) {
                $insert_data[] = array(
                    'first_name'  => $row['first_name'],
                    'last_name'  => $row['last_name'],
                    'email'  => $row['email'],
                    'birthdate'  => $row['birthdate'],
                    'created_at'  => now(),
                    'updated_at'  => now(),
                );

            }
        }
        DB::table('tbl_sampleimport')->insert($insert_data);

My Table tbl_sampleimport

enter image description here

But this gives me an error like this Illegal string offset 'first_name'

Laravel : v5.8.*

cyber-duck/laravel-excel


Solution

  • use json_decode() to convert into array

    $arr = json_decode($collection, true);
    foreach($arr as $row) {
        $insert_data[] = array(
              'first_name'  => $row['first_name'],
              'last_name'  => $row['last_name'],
              'email'  => $row['email'],
              'birthdate'  => $row['birthdate']['date'],
              'created_at'  => now(),
              'updated_at'  => now(),
         );
    }