Laravel Excel import, date column in Excel cell returns as unknown format number

In this tutorial, I will give you an example of “How to fix Laravel Excel import, date column in Excel cell returns as unknown format number” so you can easily apply it to your Laravel 5, Laravel 6, Laravel 7, Laravel 8, Laravel 9, and Laravel 10 applications.

Import date of birth into the collection in Laravel Excel

When using Laravel Excel with Maatwebsite\Excel 3.1, if a date column in an Excel cell is returning as an unknown format number, you can try the following solutions:

<?php
namespace App\Imports;
use DB;
use App\Models\User;
use App\Models\Department;
use Illuminate\Support\Str;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;

use Maatwebsite\Excel\Concerns\WithChunkReading;

class ImportUser  implements ToCollection,WithHeadingRow,WithChunkReading
{
    /**
    * @param Collection $collection
    */
   
    public function collection(Collection $rows)
    {
        Validator::make($rows->toArray(), [
        '*.first_name'     => 'required|max:100',
        '*.last_name'      => 'required|max:100',
        '*.email'          => 'required|max:100|email|unique:users,email',
        '*.phone_number'   => 'required|regex:/^([0-9\s\-\+\(\)]*)$/|max:15|unique:users,phone_number',
        '*.date_of_birth'  => 'required|before:-16 years',
        '*.department'     => 'required|max:150',
        ])->validate();

        try{
            DB::beginTransaction();

        foreach ($rows as $row)
        {
            $user =  User::create([
            'first_name'     => $row['first_name'] ?? '',
            'last_name'      => $row['last_name'] ?? '',
            'email'          => $row['email'] ?? '',
            'phone_number'   => $row['phone_number'] ?? '',
            'date_of_birth'  => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['date_of_birth'] ?? '')->format('Y-m-d'),
            'user_type'      => '2',
            ]);

            $department = [
            'user_id' => $user->id ?? '',
            'department_name' => $row['department'] ?? '',
        ];
        $department = Department::create($department);
       
        DB::commit();
      }
      catch(\Exception $e){
      DB::rollback();
      dd('something goes wrong, please upload employee as manually.');
      throw $e;
      }
    }

    public function chunkSize(): int
    {
        return 10000;
    }
  
   }
}

Specify Date Format: Specify the date format in your import class. For example, if the date format in the Excel sheet is ‘Y-m-d’, you can specify it in your import class like this:

Note: The date format issue will work only in collections when you using the heading row with the Excel header name, It will throw an error in the row and say undefined array key.

Update row to heading element :

'dob' => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['2']),


'dob' => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['dob']),

'date_of_birth'  => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['date_of_birth'] ?? '')->format('Y-m-d'),

I hope that this article helped you learn how to fix the date format issue in the Laravel Excel Maatwebsite in the Laravel application. You may also want to check out our guide on How to Search for first name and last name in Join Query in Laravel.

Import date of birth into the collection in Laravel Excel

Related Posts