[BUG] import date format issue

[hardikdangar]

• 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.

Versions

• PHP version: 7.1
• Laravel version: 5.7
• Package version: 3.1

Description

I created new import with WithChunkReading and batch size. The issue i am facing is importer converts date columns to timestamp( i believe its timestamp) 43257.0. after investing the issue i found very old thread #404 and one of the solution that fixed it for me was setting true value to false in class ReadChunk available at vendor/maatwebsite/excel/src/Jobs/ReadChunk.php. line is $this->reader->setReadDataOnly(true);
This solution works for now but when we will do composer update it will be gone as its not configurable in library.

Steps to Reproduce

1. create excel
2. add column with any date format.
3. import excel using chunk method importer via library.

Expected behavior:

I would expect the library to upload the date as expected.

Actual behavior:

library converts date to timestamp( assuming its timestamp)

Additional Information

Here is my import class,

`<?php
namespace App\Imports;

use App\Sample;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Imports\HeadingRowFormatter;

HeadingRowFormatter::default('none');

class Sample implements ToModel, WithHeadingRow, WithBatchInserts, WithChunkReading
{

public function model(array $row)
{


    return new user([
        'UserName'           => $row['UserName'],
        'Password'           => $row['Password'],
        'date'               => $row['date'],
    ]);
}

public function batchSize(): int
{
    return 1000;
}

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

}`

[patrickbrouwers]

Untested, but I believe you need to do:

 return new user([
        'UserName'           => $row['UserName'],
        'Password'           => $row['Password'],
        'date'               => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['date']),
    ]);

I remember setReadDataOnly to cause problems in some other situations, so I'm not comfortable just adding that again. Perhaps we can make it an opt-in concern or something.

[devinfd]

I also experienced this issue. excelToDateTimeObject brought back the actual date but it might be nice to have a concern for this.

Perhaps something like:

    interface WithDateObjects
    {
        /**
         * An array of columns to convert to DateTime Objects.
         * Either named column headers or coordinate columns.
         *
         * @return array
         */
        public function dateColumns(): array;
    }

    class MyImport implements WithDateObjects
    {
        public function dateColumns(): array
        {
            return ['birthday', 'wedding_date'];
        }
    }

Then laravel excel would detect if the import implements WithDateObjects and set \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($this->cell->getValue()) as needed?

PS: Should this convert to Carbon?

[patrickbrouwers]

I'd accept a PR that adds the WithDates concern that returns Carbon dates ;)

[t0n1zz]

well i got the issue too, before then it just works fine and now i got error unexpected data found, and even when i add this

\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['date'])

and still got error

A non well formed numeric value encountered

also tried to convert it with carbon but not working

[patrickbrouwers]

Your $row['date'] is not an integer then. excelToDateTimeObject only works when the date is formatted as a date in excel.

[t0n1zz]

hmmm it already formatted on date

i am using "maatwebsite/excel": "^3.1",

[patrickbrouwers]

Try to debug it, see what's in $row['data'].

[devinfd]

I can send a PR with WithDates but it will be a least a week. I am at a conference and cant focus on that right now.

[t0n1zz]

it is 1/1/18 it should be automatically convert from excel date to date in mysql table right? i use this package before and it working just find... but now getting this error

[patrickbrouwers]

If you get back 1/1/18, it's cleary not an integer :) So you shouldn't use excelToDateTimeObject. Instead you need to use Carbon::createFromFormat(...) to deal with this

[devinfd]

@patrickbrouwers Perhaps the withDates concern can first attempt excelToDateTimeObject and if that throws an exception try Carbon::createFromFormat(). The dateColumn method could indicate the format of the date.

public function dateColumns(): array
        {
            return ['birthday' => ‘d/m/Y’, 'wedding_date'];
        }

Ps: I tried writing this code on my phone I can’t figure out how to do the back ticks

[patrickbrouwers]

@devinfd Sounds good :)

ps fixed your back ticks ;)

[Sparclex]

@devinfd how far are you with the implementation already? Would be interested as well.

[devinfd]

I started working on a PR but quickly realized that it was going to be complicated to implement. @patrickbrouwers was going to review the codebase to see what the best way forward would be.

In the mean time I am just using a helper method on my Import object:

/**
 * Transform a date value into a Carbon object.
 *
 * @return \Carbon\Carbon|null
 */
public function transformDate($value, $format = 'Y-m-d')
{
    try {
        return \Carbon\Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value));
    } catch (\ErrorException $e) {
        return \Carbon\Carbon::createFromFormat($format, $value);
    }
}

class Sample implements ToModel
{
    public function model(array $row)
    {
        return new user([
            'name' => $row[0],
            'email' => $row[1],
            'birth-date' => $this->transformDate($row[2]),
        ]);
    }
}

[boldstar]

Also having an issue with this.

@devinfd
I am using your helper function to format my date. It returns in this format 1943-10-15 00:00:00

Is there anyway I could return it as 10/15/1943 ?

Or would it be better to handle that type of formatting on the front end?

[nickpoulos]

I am curious, does anybody want/expect/have a use case for....date fields to come back as these weird integers? It seems this stuff should happen automatically on import. I can't imagine a scenario where a date that is viewable in excel as "2019-01-10" should come back to me as 43473.0

[patrickbrouwers]

@nickpoulos that's just how Excels stores it when the numberformat is a date format.
PhpSpreadsheet attempts to convert it to a datetime object, see NumberFormat line 649. If that fails, there's not a lot we can do about it.

[VictorMonroyHz]

How can you validate that the date comes empty?

[Tushar0012]

Anybody can post the code of progress status show, on import excel file.

[jaikangam]

Did anyone solve the problem??

[shahburhan]

Did anyone solve the problem??

@Jaikangam93
A little late, but I happened to fix it with the help of WithCustomValueBinder wrote a little check for the coordinate of date column and used excelToDateTimeObject method on PhpOffice\PhpSpreadsheet\Shared\Date and finally set the value as for the cell as string.

Here is the code:

   public function bindValue(Cell $cell, $value)
   {
       if(preg_match('/^E*\d*$/', $cell->getCoordinate())){
                $cell->setValueExplicit(Date::excelToDateTimeObject($value)->format('Y-m-d'), DataType::TYPE_STRING);
        }
        else{
            $cell->setValueExplicit($value, DataType::TYPE_STRING);
        }

        return true;
    }

Make sure to import all the namespaces and implement WithCustomValueBinder on the importer.

[mariavilaro]

I have a problem, I'm importing a file that in some cells can have any value (numeric, string, date...), how can I know that the original value in that cell was a date, so I can format it correctly?

[shahburhan]

You'll need to specifically know what column would contain a date entry and then reference same in your fix. If you are using Headings, you can check with the column key. Generally figuring out a date value would be easy if its generic, but with excel, it stores the date as excel timestamp which is basically a string. What you can do is figure out a pattern on the timestamps and accordingly make a check for each column and if there is a match, you can further with the formatting.

From what I have seen excel timestamps generally have the pattern \d*\.\d which is a bunch of digits followed by a dot and then another digit.

Hope that helps.

[mariavilaro]

That's not possible, the data in this columns can have any value type (numbers, dates, strings) and I can't know it beforehand, this data is finally loaded into a json field. And it could be perfectly a value like 42434.12 and not be a date.

[vin33t]

I started working on a PR but quickly realized that it was going to be complicated to implement. @patrickbrouwers was going to review the codebase to see what the best way forward would be.

In the mean time I am just using a helper method on my Import object:

/**
 * Transform a date value into a Carbon object.
 *
 * @return \Carbon\Carbon|null
 */
public function transformDate($value, $format = 'Y-m-d')
{
    try {
        return \Carbon\Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value));
    } catch (\ErrorException $e) {
        return \Carbon\Carbon::createFromFormat($format, $value);
    }
}

class Sample implements ToModel
{
    public function model(array $row)
    {
        return new user([
            'name' => $row[0],
            'email' => $row[1],
            'birth-date' => $this->transformDate($row[2]),
        ]);
    }
}

Works like a Charm. Thank You

[thrazu]

@vin33t @patrickbrouwers
Check for an empty field first (before try / catch), otherwise the function will return the date 1970-01-01 if empty:

if(!strlen($value)) return null;

[devcodemarcos]

Hi, i find this solution, works for me!!!

protected function formatDateExcel($date) { 
        if ('double' === gettype($date)) {
            $date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($date);

            return $date->format('n/j/Y');
        }

        return $date;
 }

and i use

$this->formatDateExcel($row['birthday']);

[mkantautas]

Anyone can explain how to deal with 04/04/1977 as in m/d/Y formatting?

Date::excelToDateTimeObject($row['birth_date'])

as I see it doesn't accept format argument?

for demo purposes:

'birthday' => $row['birth_date'] ? Date::excelToDateTimeObject($row['birth_date']) : null,

And of course we there is 04/04/1977 I get 0000-00-00...., because it doesn't know which is the month and which one is the day 🤦‍♂️

UPDATED:
Nevermind It turns out the issue is only for people whose birthday is bellow 1970...

2nd edit changed column type from timestamp to dateTime - case closed.

[nizardani]

I want to import an excel file with the date field, but I have an error between the format 1992/03/01 being made 1992-03-01, or is there another way to overcome this?
Please help me overcome this problem.

[jaikangam]

I want to import an excel file with the date field, but I have an error between the format 1992/03/01 being made 1992-03-01, or is there another way to overcome this?
Please help me overcome this problem.

Hi nizardani,
Try like this, on your Excel file Edit on coloum of date field 1992-03-01.
This Date 1992-03-01 will not accept by Laravel Excel so you need to update/change the column field type.
Step to solve :-

1. Open the excel file to Import. Select the date coloum Right Click, select the Format Cell
2. Format cell> Number > Custom> Type> dd-mm-yyyy hh:mm:ss
   Sample will be like this > 03-06-2018 00:00:00
   After changing the Format cell. If it is appearing like 03-06-2018.
   Make sure you double click on that so that it changes into like this 03-06-2018 00:00:00.

Again Import File.... I hope it will work now.

[nizardani]

I want to import an excel file with the date field, but I have an error between the format 1992/03/01 being made 1992-03-01, or is there another way to overcome this?
Please help me overcome this problem.

Hi nizardani,
Try like this, on your Excel file Edit on coloum of date field 1992-03-01.
This Date 1992-03-01 will not accept by Laravel Excel so you need to update/change the column field type.
Step to solve :-

1. Open the excel file to Import. Select the date coloum Right Click, select the Format Cell
2. Format cell> Number > Custom> Type> dd-mm-yyyy hh:mm:ss
   Sample will be like this > 03-06-2018 00:00:00
   After changing the Format cell. If it is appearing like 03-06-2018.
   Make sure you double click on that so that it changes into like this 03-06-2018 00:00:00.

Again Import File.... I hope it will work now.

I get the problem again after I change a date type in excel, what should I use carbon nesbot?

[judgej]

So has anybody worked out how to tell the cell is a date format before deciding whether it needs converting to a date or datetime object? Excel obviously knows, since a saved date field will return as a date field when the spreadsheet is loaded again into Excel. So there must be some metadata for that cell that can tell us it is a cell that contains a date.

Had a quick peek, and it's in the worksheet:

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" mc:Ignorable="x14ac xr xr2 xr3" xr:uid="{A7382266-55D2-4E6B-95C9-90E61705D12D}">
   ...
   <sheetData>
      <row r="4" spans="1:1" x14ac:dyDescent="0.25">
         <c r="A4">
            <v>54321</v>
         </c>
      </row>
      <row r="5" spans="1:1" x14ac:dyDescent="0.25">
         <c r="A5" s="1">
            <v>43924</v>
         </c>
      </row>
   </sheetData>
   ...
</worksheet>

Cell A4 is an integer, and cell A5 is a date. The different is the s="1" attribute. That makes it a date. If I add this attribute to cell A4 by editing my Book1.xlsx, opening the spreadsheet gives me two dates. 54321 is 20th September 2048, in case you were wondering ;-)

So the data is in the spreadsheet to inform this library that this number is not a number, but a date. I've not looked this up in the spec, so I expect there is more subtlety involved than that, but the point is, the application importing the spreadsheet should not have to know what the data type is in advance.

Update: really great answer here The s=1 goes through a couple of redirect references in styles.xml and ultimately gives you an internal format or a custom format. I suspect the display format is all you then have to determine whether the intention was for the number to be a date. Some of the internal formats are easy enough to hard code, but custom formats will be a little more difficult to decode.

[patrickbrouwers]

PhpSpreadsheet does know about this format. However for performance reasons it's recommend to read the Excel file in read_only mode. If you can disable that here: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/config/excel.php#L47 PhpSpreadsheet should then read the date as date automatically. If you prefer the more performant setting, you can format the dates yourself using the PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($date)

[felloz]

Untested, but I believe you need to do:

 return new user([
        'UserName'           => $row['UserName'],
        'Password'           => $row['Password'],
        'date'               => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['date']),
    ]);

I remember setReadDataOnly to cause problems in some other situations, so I'm not comfortable just adding that again. Perhaps we can make it an opt-in concern or something.

Saddly this method change my date to 1900-01-08

[Nanod10]

if your need involves resolving datetime fields dynamically, I have written a method that is responsible for automatically detecting if the value is a datetime dynamically (regardless of whether or not you know if there will be a datetime in that column) or I have tried various data types and it works fine

       /**
     * @param Cell $cell
     * @param $value
     * 
     * @return boolean;
     */
    public function bindValue(Cell $cell, $value)
    {
        $formatedCellValue = $this->formatDateTimeCell($value, $datetime_output_format = "d-m-Y H:i:s", $date_output_format = "d-m-Y", $time_output_format = "H:i:s" );
        if($formatedCellValue != false){
            $cell->setValueExplicit($formatedCellValue, DataType::TYPE_STRING);
            return true;
        }

        // else return default behavior
        return parent::bindValue($cell, $value);
    }


    /**
     * 
     * Convert excel-timestamp to Php-timestamp and again to excel-timestamp to compare both compare
     * By Leonardo J. Jauregui ( @Nanod10 | siskit dot com )
     * 
     * @param $value (cell value)
     * @param String $datetime_output_format
     * @param String $date_output_format
     * @param String $time_output_format
     * 
     * @return $formatedCellValue
     */
    private function formatDateTimeCell( $value, $datetime_output_format = "Y-m-d H:i:s", $date_output_format = "Y-m-d", $time_output_format = "H:i:s" )
    {

        // is only time flag
        $is_only_time = false;
        
        // Divide Excel-timestamp to know if is Only Date, Only Time or both of them
        $excel_datetime_exploded = explode(".", $value);

        // if has dot, maybe date has time or is only time
        if(strstr($value,".")){
            // Excel-timestamp to Php-DateTimeObject
            $dateTimeObject = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);
            // if Excel-timestamp > 0 then has Date and Time 
            if(intval($excel_datetime_exploded[0]) > 0){
                // Date and Time
                $output_format = $datetime_output_format;
                $is_only_time = false;
            }else{
                // Only time
                $output_format = $time_output_format;
                $is_only_time = true;
            }
        }else{
            // Only Date
            // Excel-timestamp to Php-DateTimeObject
            $dateTimeObject = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);
            $output_format = $date_output_format;
            $is_only_time = false;
        }
            
        // Php-DateTimeObject to Php-timestamp
        $phpTimestamp = $dateTimeObject->getTimestamp();

        // Php-timestamp to Excel-timestamp
        $excelTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $phpTimestamp );
            
        // if is only Time
        if($is_only_time){
            // 01-01-1970 = 25569
            // Substract to match PhpToExcel conversion
            $excelTimestamp = $excelTimestamp - 25569;
        }

        /* 
        // uncoment to debug manualy and see if working
        $debug_arr = [
                "value"=>$value,
                "value_float"=>floatval($value),
                "dateTimeObject"=>$dateTimeObject,
                "phpTimestamp"=>$phpTimestamp,
                "excelTimestamp"=>$excelTimestamp,
                "default_date_format"=>$dateTimeObject->format('Y-m-d H:i:s'),
                "custom_date_format"=>$dateTimeObject->format($output_format)
            ];
            
        if($cell->getColumn()=="Q"){
            if($cell->getRow()=="2"){
                if(floatval($value)===$excelTimestamp){
                    dd($debug_arr);
                }
            }
        }

        */
        
        // if the values match
        if( floatval($value) === $excelTimestamp ){
            // is a fucking date! ;)
            $formatedCellValue = $dateTimeObject->format($output_format);
            return $formatedCellValue;
        }else{
            // return normal value
            return false;
        }
        
    }

[alexinside]

Hello. I have same issue. I am using ToCollection interface.

The method public function collection(Collection $collection) is declared ok and is called and it has all the processings.

Problem: In the collection() method, one datetime cell is converted to a number instead of a datetime string. How do I disable this conversion? It does not understand the value format. And I can not modify XLS file anyway.

class MyImport implements WithCustomValueBinder, ToCollection
— does not work neither for me, as bindValues is not called before the collection();

ToModel I can not use, as already implemented collection(). And the format of XLS is not linear. Need custom processing.

[lucadegasperi]

Hello, similar issue here. I wanted to use laravel's validator to check on a certain date format and make sure it's before another date field. Is this somehow possible? The only solution I found so far is saving the date time cell as string in the xlsx file and run the validator like this:

public function rules(): array
    {
        return [
            'start' => 'required|date_format:d/m/y H:i|before:*.end',
            'end' => 'required|date_format:d/m/y H:i|after:*.end',
        ];
    }

[alexinside]

Yes, I also manually change the cell format to String and then it parses ok. But I can not modify always the xls files, as they will be regularly streamed with cell format Dateteme.

Problem still persists.

[ephraimlambarte]

In my case i am importing excel files with dynamic column names, so as a solution i override the Maatwebsite/Excel/Cell::getValue function into the function below:

 public function getValue($nullValue = null, $calculateFormulas = false, $formatData = true)
 {
        $value = $this->cell->getFormattedValue();
         
        if (is_numeric($value)) {
            $value = (double) $value;
        }
        return $value;
}

now all the non numeric cells will be converted to string and the numeric cells will be converted to a numeric data type.

[codewithenima]

Don't forget to convert the string to intiger add (int) => (int)$row['date']
'date' => Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject((int)$row['date']))->format('Y-m-d'),

[diveshc22]

I have created a function to check the file extensions. which parse date as per the file extension

1. Controller code

      public function store(FileImportRequest $request)
      {
          try {
            Excel::import(new FileImport($request->file->extension()), $request->file);
            }catch (\Error $ex) {
                        throw new \Exception('Error:' . $ex->getMessage());
                    }
            }
   

2. Inside the FileImportRequest Class.

              public function rules()
                {
                    return [
                        'file' => 'required|file|mimes:csv,xlsx,xsl'
                    ];
                  }
   

3. Inside the FileImport class.

   protected $extension;

        public function __construct($extension)
        {
            $this->extension = $extension;
        }   
   
        public function model(array $row)
        {
            return new FileImport([
                'expires_at' => $this->checkExtension($row[8]),
                'created_at' => $this->checkExtension($row[9]),
            ]);
        }
   
    private function  checkExtension($dateTime)
      {
          switch ($this->extension) {
              case 'csv':
                  return Carbon::parse($dateTime)->toDateTimeString();
                  break;
              case 'xlsx':
              case 'xsl':
                  return \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($dateTime);
                  break;
          }
       }
   

[MipanZuu]

I use this formula, and it works for me, I've been searching for a whole week for this :)
reference: link

$unixDate = ($row[3] - 25569) * 86400;
        $date =  gmdate("Y-m-d H:i:s", $unixDate);