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

When importing Excel data, there is a transformDateTime error – Laravel

I’m attempting to import Excel using the following code:

<?php

namespace AppImports;
use CarbonCarbon;
use AppVch;
use MaatwebsiteExcelConcernsToModel;
use MaatwebsiteExcelConcernsWithHeadingRow;
use MaatwebsiteExcelConcernsWithBatchInserts;
use MaatwebsiteExcelConcernsWithChunkReading;
use MaatwebsiteExcelImportsHeadingRowFormatter;

HeadingRowFormatter::default('none');

class VchImport implements ToModel, WithHeadingRow, WithBatchInserts, WithChunkReading
{
    /**
    * @param array $row
    *
    * @return IlluminateDatabaseEloquentModel|null
    */

    private function transformDateTime(string $value, string $format = 'd-m-Y')
    {
        try {
                return Carbon::instance(PhpOfficePhpSpreadsheetSharedDate::excelToDateTimeObject($value))->format($format);
            } 
            catch (ErrorException $e) 
            {
                return Carbon::createFromFormat($format, $value);
            }
    }
    
    public function model(array $row)
    {
        return new Vch([
            'no_vch' => $row[1],
            'nilai_vch' => $row[2],
            'batch' => $row[3],
            'expired' => $this->transformDateTime($row[4]),
            'nama_penerima' => $row[5],
            'alamat_penerima' => $row[6],
            'status' => $row[7],
        ]);
    }
}

When I try to import Excel, there is an error like this:

AppImportsVchImport::transformDateTime(): Argument #1 ($value) must be of type string, null given, called in C:Apache24htdocsgmpro-devappImportsVchImport.php on line 41

I’ve tried changing the date format via code, excel, and the computer still doesn’t work.
enter image description here

I’m debugging with dd($row); and get this result: row (4) it’s supposed date:

array:10 [▼
  0 => null
  1 => "CP/WS-GADD/III/23-MR001799"
  2 => 100000
  3 => "0269"
  4 => 45366
  5 => null
  6 => null
  7 => "Beredar"
  8 => null
  9 => null
]
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    My Excel is clean. No code and no formula. This error is due to my Excel format. I changed from .xlsx to .xls and it worked perfectly. Thank you for your attention.


  2. 0

    the model method runs on every row of excel file . so when you get dd of row
    the result is belongs to first row of excel and maybe the problem is caused by another row .

    the obvious cause of this error is that in one row of excel the 4th row is empty. so you need to handle this exception :

     public function model(array $row)
    {
        return new Vch([
            'no_vch' => $row[1],
            'nilai_vch' => $row[2],
            'batch' => $row[3],
            'expired' =>$row[4]? $this->transformDateTime($row[4]):null,
            'nama_penerima' => $row[5],
            'alamat_penerima' => $row[6],
            'status' => $row[7],
        ]);
    }

    or another way:

     private function transformDateTime(string $value=null, string $format = 'd-m-Y')
    {
        if (!$value)
         return null ;

        try {
                return Carbon::instance(PhpOfficePhpSpreadsheetSharedDate::excelToDateTimeObject($value))->format($format);
            } 
            catch (ErrorException $e) 
            {
                return Carbon::createFromFormat($format, $value);
            }
    }
    Login or Signup to reply.
  3. 0

    You should handle this error by validating the rows by WithValidation concern. you can define rules, ex:

      public function rules(): array
    {
        return [
            '4' => 'required',
        ];
    }

    By define this rules the $row[4] cannot be empty

    With the SkipsOnFailure concern you get control over what happens the moment a validation failure happens by the onFailure function

    
public function onFailure(Failure ...$failures)
    {
        // Handle the failures how you'd like.
    }

    or just use the SkipsFailures trait.

    Your first row is empty, you should use the WithStartRow concern, and define the startRow function like this:

    public function startRow(): int
    {
        return 2;
    }

    With this the import begins from the second row, jumped out the empty row, or another solution

    private function transformDateTime($value, string $format = 'd-m-Y')
{
 if(!is_null($value)){
        try {
                return Carbon::instance(PhpOfficePhpSpreadsheetSharedDate::excelToDateTimeObject($value))->format($format);
            } 
            catch (ErrorException $e) 
            {
                return Carbon::createFromFormat($format, $value);
            }
 }
 else{
  Handle if the date is null!
 }
}

    Handle in the code if the date is null, in this way you can avoid the error

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search