Question 17296 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel Importing CSV throws error on rows with dates

Import.php

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

Error:

IlluminateDatabaseQueryException 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 CarbonCarbon::createFromFormat('m/d/Y', $row['billdate']) and CarbonCarbon::parse($row['billdate'])->format('Y-m-d') but neither seems to work

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    weirdly, this worked.

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

  2. 0

    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 MaatwebsiteExcelConcernsWithValidation;

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 PhpOfficePhpSpreadsheetSharedDate;

// ...

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

   // ...
]);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search