Laravel Importing CSV throws error on rows with dates-CodePudding

Import.php

...
return new Statement([
                    'account_number'            => $row['accountno'],
                    'account_name'              => $row['name'],
                    'reading_date'              => \Carbon\Carbon::createFromFormat('m/d/Y', $row['billdate']),
                    'due_date'                  => \Carbon\Carbon::createFromFormat('m/d/Y', $row['duedate']),
                ]);
...

Error:

Illuminate\Database\QueryException PHP 8.1.6 9.37.0
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '10/18/2022' for column `mubsdb`.`statements`.`due_date` at row 1
INSERT INTO
  `statements` (`due_date`, `reading_date`)
VALUES
  ( 10 / 18 / 2022, 10 / 03 / 2022),
  (
    10 / 18 / 2022,
    10 / 03 / 2022
  ),
  ( 10 / 18 / 2022, 10 / 03 / 2022),
  ( 10 / 18 / 2022, 10 / 03 / 2022),
  (10/18/2022, 10/03/2022), (10/18/2022, 10/03/2022), (10/18/2022, 10/03/2022),

DB Structure:

Name                Type           Null      Default    
reading_date         date           Yes      NULL
due_date             date           Yes      NULL

I'm trying to import and save csv rows to my DB but I get error with dates. I tried \Carbon\Carbon::createFromFormat('m/d/Y', $row['billdate']) and \Carbon\Carbon::parse($row['billdate'])->format('Y-m-d') but neither seems to work

CodePudding user response：

weirdly, this worked.

'reading_date' => $row['billdate'] ? \Carbon\Carbon::createFromFormat('m/d/Y', $row['billdate'])->format('m/d/Y') : null,
'due_date' => $row['duedate'] ? \Carbon\Carbon::createFromFormat('m/d/Y', $row['duedate'])->format('m/d/Y') : null,

CodePudding user response：

If you're using the newest version of laravel-excel, then you'll notice at this page a date column is exported using Date::dateTimeToExcel:

// ...

Date::dateTimeToExcel($invoice->created_at),

// ...

That is because date is stored as numbers in excel, thus a datetime object needs to be converted first in order to show the value correctly.

This rule also exists in import. So personally I would add a rule in the import class to make sure that the date we're receiving is a number (which actually a date format in excel):

use Maatwebsite\Excel\Concerns\WithValidation;

class MyImport implements WithValidation
{
    public function rules(): array
    {
        return [
            'created_at' => ['numeric'],
        ];
    }
}

And then, when about to importing the data using model, convert the number to datetime before modifying it with Carbon:

use PhpOffice\PhpSpreadsheet\Shared\Date;

// ...

return new Import([
   // ...
   
   'created_at' => Carbon::instance(Date::excelToDateTimeObject($row['created_at'])),
   // or any other Carbon methods

   // ...
]);