How to store some columns as Json data after importing an excel file using maatwebsite-CodePudding

I need to import an excel file which has Name, Email, Phone_number, Department, Division and some more dynamic columns.

IFile import code:

$file = $request->file('file');
        $location = 'uploads';
        $filename = $file->getClientOriginalName();
        $file->move($location, $filename);
        $filepath = public_path($location . "/" . $filename);

        $items = \Excel::toArray(new EmployeesImport(), $filepath);
        dd($items);

After importing an excel file,I get an array as this:

array:1 [
  0 => array:3 [
    0 => array:5 [
      "name" => "ABC"
      "email" => "[email protected]"
      "phone_number" => 1863184077
      "department" => "IT"
      "division" => "S/W"
    ]
    1 => array:5 [
      "name" => "DEF"
      "email" => "[email protected]"
      "phone_number" => 1671536101
      "department" => "Business"
      "division" => "Marketing"
    ]
    2 => array:5 [
      "name" => "GHI"
      "email" => "[email protected]"
      "phone_number" => 184325432
      "department" => "Transport"
      "division" => "Bus"
    ]
  ]
]

employees table has name``email``phone_number and meta_data(json) field. How can I store Division, Department and some dynamic columns in meta_data field as json data? Please help me to find a solution.

CodePudding user response：

First, add WithHeadingRow. To use heading row (1st row) as array keys of each row :

use Maatwebsite\Excel\Concerns\WithHeadingRow;

class UsersImport implements WithHeadingRow...

And then,

$items = \Excel::toArray(new UsersImport(), $filepath);

foreach($items[0] as $item){
    $name = $item['name'];
    array_shift($item);

    YourModel::create([
        'name' => $name,
        'meta_data' => $item
    ]);
}

PS : Don't forget to use array cast. array cast is particularly useful when working with columns that are stored as serialized JSON.

class YourModel extends Model
{
    protected $casts = [
        'meta_data' => 'array',
    ];

    ...
}

CodePudding user response：

I've found my solution by adding this code-

foreach($items[0] as $item){
                $name = $item['name'];
                $email = $item['email'];
                $phone_number = $item['phone_number'];
                $remove = ['name', 'email', 'phone_number'];
                $meta_data= array_diff_key($item, array_flip($remove));
                $password = (\Str::random(8));

                User::create([
                    'name' => $name,
                    'email' => $email,
                    'phone_number' => $phone_number,
                    'meta_data' => $meta_data,
                    'password' => \Illuminate\Support\Facades\Hash::make($password),
                ]);
            }

Hope someone will be benefited.