[BUG] date field is not imported

[schel4ok]

Prerequisites

• [Х] Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
• [Х] Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
• [Х] Checked that your issue isn't already filed.
• [Х] Checked if no PR was submitted that fixes this problem.
• [Х] Filled in the entire issue template

Versions

• PHP version: 7.2.19
• Laravel version: 6.17.1
• Package version: 3.1

Description

created_at field is not imported in database. Instead I see 1970-01-01 in phpmyadmin

Steps to Reproduce

namespace App\Imports;

use App\NewsPost;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class NewsImport implements ToCollection, WithHeadingRow

{
    public $rowCount = null;

    public function collection(Collection $rows)
    {
        News::truncate();

        foreach ($rows as $row) 
        { 
          if( !empty($row['category_id'])) {  
  
            NewsPost::create([

               'category_id'=> $row['category_id'],
               'title'      => $row['title'], 
               'sef'        => $row['sef'], 
               'introtext'  => $row['introtext'], 
               'fulltext'   => $row['fulltext'], 
               'image'      => $row['image'], 
               'metatitle'  => $row['metatitle'], 
               'metakey'    => $row['metakey'], 
               'metadesc'   => $row['metadesc'], 
               'hits'       => $row['hits'], 
               'created_at' => $row['created_at'], 

            ]);
          }

        }
        
      $this->rowCount = $rows->count();

    }

}

namespace App\Admin\Controllers;

use Illuminate\Support\Facades\DB;
use App\Imports\NewsPostImport;
use Maatwebsite\Excel\Facades\Excel;

class SeedController extends BaseAdminController
{

    public function index(Content $content)
    {
        DB::statement('SET FOREIGN_KEY_CHECKS = 0');
        Excel::import($newsPost     = new NewsPostImport,       'import/news_posts.xlsm');
        DB::statement('SET FOREIGN_KEY_CHECKS = 1');

        return $content
            ->title('Import & Export')
            ->description('Laravel excel')
            ->withSuccess('Импорт завершен успешно', 
               '<p>Импортировано '. $newsPost->rowCount.' новостей</p>');
    }

}

[patrickbrouwers]

Is created_at fillable on your model?

[schel4ok]

Yes. I have all fields fillable

[patrickbrouwers]

Try to dd() and see what's in there.

[schel4ok]

sorry for delay. I was on vacation.
I put dd here

            if (empty($row['slug'])) {
              News::create([
                 'category_id'  => $row['category_id'],
                 'title'        => $row['title'],
                 'slug'         => Str::slug($row['title'], '-'),
                 'fulltext'     => $row['fulltext'],
                 'image'        => $row['image'],
                 'metatitle'    => $row['metatitle'],
                 'metakey'      => $row['metakey'],
                 'metadesc'     => $row['metadesc'],
                 'hits'         => $row['hits'],
                 'published_at' => $row['published_at'],
                 'created_at'   => $row['created_at'],
                 'updated_at'   => $row['updated_at'],
                 'deleted_at'   => $row['deleted_at'],
              ]);
              dd($row);
            }

and I see strange date output on the page

Illuminate\Support\Collection {#1986 ▼
  #items: array:14 [▼
    "category_id" => 4
    .......
    "published_at" => null
    "created_at" => 41767
  ]
}

in my excel I have date like that

[schel4ok]

any idea?

[schel4ok]

I found problem.
I put dd($row) before News::create and check what comes from excel.
And I saw 41767 again.
Then I go to excel and check formatting of created_at field. It was date. But when I change format from date to text immideately I see 41767.
Doing that I understood that before import all dates in excel are converted to text.
So I converted these fields to text manually, put dates inside and it works now.

[schel4ok]

Only once I was able to do import succesfully and then I still have some problems, which I cannot understand.
It is headache to import dates. I cannot see rule, which working always.

I tested few times how should I keep date in Excel before import and I see not stabile behaviour.

I make all date fields format = text in excel.
When I put date 08-05-2014 and format as text.
Anyway I have error during import

InvalidArgumentException
Unexpected data found.
 Unexpected data found.
 Data missing
http://localhost.test/admin/seed

pointing on \vendor\nesbot\carbon\src\Carbon\Traits\Creator.php:623

        if (static::isStrictModeEnabled()) {
            throw new InvalidArgumentException(implode(PHP_EOL, $lastErrors['errors']));
        }

But when I do dd($row) I see

 "created_at" => "08-05-2014"

What is wrong?

[schel4ok]

Seems that I found solution.
Only working way is to keep date 2014-05-08 in Excel and format as text.
Because in this order it is stored in MySQL = YYYY-MM-DD
Any of these is not working
08-05-2014 - wrong order
08.05.2014 - wrong delimiter and wrong order
2014.05.08 - wrong delimiter

If date fields formated as date in Excel in my case date 2014-05-08 was always converted to 41767 before import. I don't know why.
I tryed date to timestamp converter and it gives me
2014-05-08 = 1399507200.
08-05-2014 = 1407182400

Only way I could get 41767 is when I tryed in Excel formula =text(08.05.2014;0)
So I suppose it is converted by Excel.
And it is converted even if I adjust date field to YYYY-MM-DD format.

So only working solution is text format with YYYY-MM-DD text inside.
Correct me if this solution is not completely right.

[lonnylot]

I'm having the same issue. The 'date' fields are returning a number (a ref, I guess?) and not the value.

[patrickbrouwers]

To get Carbon when having the date column formatted as a date: Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date:: excelToDateTimeObject($row['created_at']))

[schel4ok]

It works for not empty fields. But when import script reach empty field it stops with following error.
This problem appears only in Laravel 7. With Laravel 6 I don't have this problem.

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '1970-01-01 00:00:00' for column 'published_at' at row 1 (SQL: insert into `news_posts` (`category_id`, `title`, `slug`, `fulltext`, `image`, `metatitle`, `metakey`, `metadesc`, `hits`, `is_published`, `published_at`, `created_at`, `updated_at`, `deleted_at`) values ...

What to do?
IF clause is not possible inside import

                if (empty($row['published_at'])) { 
                    'published_at' => $row['published_at'],
                } else {
                 'published_at' => Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['published_at'])),
                }

It gives error syntax error, unexpected 'if' (T_IF), expecting ']'

[patrickbrouwers]

Put it into a temporary variable

[schel4ok]

what do you mean under "temporary variable"?

[schel4ok]

I found acceptable solution

        foreach ($rows as $row) 
        {  
          if (!empty($row['category_id'])) {
            $slug = empty($row['slug']) ? Str::slug($row['title'], '-') : $row['slug'];
            $published_at = empty($row['published_at']) ? $row['published_at'] : Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['published_at']));
            $created_at = empty($row['created_at']) ? $row['created_at'] : Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['created_at']));
            $updated_at = empty($row['updated_at']) ? $row['updated_at'] : Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['updated_at']));
            $deleted_at = empty($row['deleted_at']) ? $row['deleted_at'] : Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['deleted_at']));

              NewsPost::create([
                 'category_id'  => $row['category_id'],
                 'title'        => $row['title'],
                 'slug'         => $slug,
                 'fulltext'     => $row['fulltext'],
                 'image'        => $row['image'],
                 'metatitle'    => $row['metatitle'],
                 'metakey'      => $row['metakey'],
                 'metadesc'     => $row['metadesc'],
                 'hits'         => $row['hits'],
                 'is_published' => $row['is_published'],
                 'published_at' => $published_at,
                 'created_at'   => $created_at,
                 'updated_at'   => $updated_at,
                 'deleted_at'   => $deleted_at,
              ]);             

          }

        }