Laravel excel import, date column in excel cell returns as floating value. How to solve this?

Question

By using Maatwebsite/Laravel-Excel to import excel sheet, here I faced an issue date time column of the excel sheet returns float value. How to solve this? Example : Consider Cell value "08-04-2016 13:08:29" and returns as "42104.487060185" when import.

Answer 1

This only happend when use chunk. This issue could be solved by:

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

Answer 2

Known bug, see https://github.com/Maatwebsite/Laravel-Excel/issues/404 for details.

But basically, when using chunk() to read the cells in, it fails to convert Excel's datetime format from a float into a Carbon date object.

There is currently no fix, You can work around this by calling config before the call to load:

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

Excel::filter('chunk')->load($file)->chunk(100 function($rows) { ... });

If you're not using the chunk filter, then see http://www.maatwebsite.nl/laravel-excel/docs/import#dates on how to explicty set formats on cells (setDateColumns()), but those should be converting automatically unless you change the defaults.