0

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

Karan
  • 1,146
  • 1
  • 10
  • 24
Pablo
  • 1,357
  • 1
  • 11
  • 40

1 Answers1

1

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(),
     );
}
Karan
  • 1,146
  • 1
  • 10
  • 24
  • Thanksss it works fine now, everytime I have date with that format on my $collection I need use `['date']`? – Pablo Nov 22 '19 at 04:31
  • 1
    when you fetch from table no need to like this.. in this case you have json into json means nested json so...we convert into array means array into array – Karan Nov 22 '19 at 04:33
  • I get error whenever I only have 1 row to insert. but working fine if I have multiple to row to insert.. – Pablo Nov 27 '19 at 08:15