chunk import date format issue

[adnandogar]

I am importing a file.xlsx in this there is field DOB = 16-OCT-1992 , after the getting values in file DOB is converted into 34215 ,i think it is not calculating formulas in DOB field , Please help me out ? Urgent

Excel::filter('chunk')->load('file.xlsx')->chunk(250, function($results)
{
foreach($results as $row)
{
// do stuff
}
});

[patrickbrouwers]

When you import it without ->chunk() do you have the same issue?

[adnandogar]

without chunk it is working fine with reader->formatdate..., but i can not go without chunk because file is too big .

I tested with CSV , it works fine because csv removes all calculations and formats ,but it need to work with xlsx .

[patrickbrouwers]

Try to include the ->formatDates in this script as well then.

[adnandogar]

i tried with this ,but no success .

[patrickbrouwers]

Can you share the code that works (without chunk) and the chunk attempt with formatDates()

[TheLand]

I've got the same issue, i'm importing a xls file with the chunk method, but it converts date in a float format and i cannot access the $reader variable to set what are dates columns.

Have you found any solutions?

Thanks

[adnandogar]

finally i solved this by converting first into csv file then load again this csv , because csv removes all formatting etc .

[cooperaj]

We've just his this issue in a project of ours. When chunking an excel file (currently containing something like 10 rows) we lose the formatting of any cells defined as dates - they just come out as numbers like 27777.

We've managed to solve that in this case by not using chunking and for this project that might just work. I think the issue is that on line 496 of LaravelExcelReader.php the function setReadDataOnly gets turned on. According to PHPOffice/PHPExcel#15 having that turned on means date cells don't get parsed correctly.

[thrazu]

Same problem here!

[gustavosz]

Have you found any solutions?

[haakym]

Experiencing the same issue with the following set up (simplified)...

My excel file:

name	birth_date
John Smith	09/09/1987

Import code in my controller using chunk:

$file = \Input::file('student_file');

\Excel::filter('chunk')
    ->selectSheets('students')
    ->load($file)
    ->formatDates(true)
    ->chunk(250, function($results) {

        dd($results);

    });

Result of dd():

RowCollection {#1019 ▼
  #title: null
  #items: array:1 [▼
    0 => CellCollection {#789 ▼
      #title: null
      #items: array:9 [▼
        "name" => "John Smith"
        "birth_date" => 32029.0
      ]
    }
  ]
}

If I do not use the chunk filter, as so:

\Excel::selectSheets('students')
    ->load($file, function($reader) {

        dd($reader->get());

    });

I get the following from the dd():

RowCollection {#527 ▼
  #title: "students"
  #items: array:1 [▼
    0 => CellCollection {#628 ▼
      #title: null
      #items: array:9 [▼
        "name" => "John Smith"
        "birth_date" => Carbon {#627 ▼
          +"date": "1987-09-09 00:00:00.000000"
          +"timezone_type": 3
          +"timezone": "Europe/London"
        }
      ]
    }
  ]
}

Any suggestions?

[0plus1]

Reporting issue on my end as well.

get() works and returns a Carbon object, chunk() returns a float.

EDIT: on latest "maatwebsite/excel": "~2.1.0",

[ryanaddams]

Having the same issue with the chunk import filter and dates getting read as floats.

[bsiu]

Same issue here with chunk. Our workaround was to add the date field from $row to a base date (1900-01-01):

$date_aux = Carbon::create(1900, 01, 01, 0);
$date_correct = $date_aux->addDays($row->date_wrong - 2);

Have to substract 2 from the wrong date to get the right one.

Hope this helps.

[dzgrief]

Same problem here

[dexter21r]

Same problem here

[trinvh2]

Same problem here

[groundbreaker08]

same problem here

[thiaguvert]

This issue could be solved by
$UNIX_DATE = ($row->DOB - 25569) * 86400;
$date_column = gmdate("d-m-Y H:i:s", $UNIX_DATE);

[Dmitri10]

such old bug and no own solution?... same problem!
Thank you, bsiu

[jamestowers]

+1

[jamestowers]

Another solution is to set the dates columns in config programatically before you make the chunk call:

config(['excel.import.dates.columns' => [
            'deleted_at,
            'updated_at'
        ]]);

Excel::filter('chunk')->load($file)...

[garygreen]

+1 having the same issue. When using chunk it doesn't appear to convert dates correctly. If I do:

$import = Excel::filter('chunk')->load($file)->setDateColumns(['my_date'])->chunk(1, function($results) {
    dd($results);
}, false);
// my_date is a numeric value

With:

$import = Excel::load($file)->setDateColumns(['my_date'])->dd();
// my_date is carbon.

[garygreen]

@jamestowers that's a nice solution - remember if your using queued chunks remember to call config in a service provider or something otherwise it won't work. Hope this bug is fixed soon @patrickbrouwers

[hovik-geodakyan]

Same issue. Naming date columns is not the solution we were looking for, but it'lll do now. Why not read both values AND formats (but not styles and other stuff), PHPExcel?

[SlyDave]

Ran into this issue today, 2 years since it was reported, still not resolved. project abandoned ?

[hardy2defy]

Same issue 👍

[timkley]

The problem does in fact occur because, like @cooperaj said, setReadDataOnly gets set to true in ChunkedReadJob. Commenting out this line solves this issue.

@patrickbrouwers I would be happy to provide a PR for this, but I think you had a reason for disabling getting the formatting of the cells in the ChunkedReadJob?

Since so many people have this problem it would be great if we could address this somehow.

[yogendrakarekar]

Solved the porblem with this line

setDateColumns() comes as a saviour

$holidaylist = Excel::load('storage/app/public/holidaylisting.xlsx', function($reader) {})->setDateColumns(['holiday_start_date','holiday_end_date'])->get();

[HeartlandTechie]

I've been pulling my hair out over this one. Why isn't this fixed? I've missed two deadlines because of a Mickey Mouse problem in an Excel import routine that is over 2 years old?

[mouradmamassi]

the solution is when your read your column type date you should formatted to string so try this please
\PHPExcel_Style_NumberFormat::toFormattedString($row['date_example'], 'YYYY-MM-DD']

[patrickbrouwers]

We are closing all old tickets, because version 2.* is reaching the End-of-Life stage. Our full attention is now allocated to version 3.0. It's still possible to use 2.*, but there will be no active support anymore.