-
-
Notifications
You must be signed in to change notification settings - Fork 1.9k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
[BUG] import date format issue #1832
Comments
Untested, but I believe you need to do:
I remember |
I also experienced this issue. 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 PS: Should this convert to Carbon? |
I'd accept a PR that adds the |
well i got the issue too, before then it just works fine and now i got error
and still got error
also tried to convert it with carbon but not working |
Your |
Try to debug it, see what's in |
I can send a PR with |
it is |
If you get back |
@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 |
@devinfd Sounds good :) ps fixed your back ticks ;) |
@devinfd how far are you with the implementation already? Would be interested as well. |
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]),
]);
}
} |
Also having an issue with this. @devinfd Is there anyway I could return it as Or would it be better to handle that type of formatting on the front end? |
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 |
@nickpoulos that's just how Excels stores it when the numberformat is a date format. |
How can you validate that the date comes empty? |
Anybody can post the code of progress status show, on import excel file. |
Did anyone solve the problem?? |
@Jaikangam93 Here is the code:
Make sure to import all the namespaces and implement WithCustomValueBinder on the importer. |
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? |
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. |
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. |
Works like a Charm. Thank You |
@vin33t @patrickbrouwers
|
Hi, i find this solution, works for me!!!
and i use
|
Anyone can explain how to deal with
as I see it doesn't accept format argument? for demo purposes:
And of course we there is UPDATED: 2nd edit changed column type from |
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? |
Hi nizardani,
Again Import File.... I hope it will work now. |
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 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 |
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 |
Saddly this method change my date to 1900-01-08 |
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
|
Hello. I have same issue. I am using ToCollection interface. The method 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.
ToModel I can not use, as already implemented collection(). And the format of XLS is not linear. Need custom processing. |
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',
];
} |
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. |
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:
now all the non numeric cells will be converted to string and the numeric cells will be converted to a numeric data type. |
Don't forget to convert the string to intiger add (int) => (int)$row['date'] |
I have created a function to check the file extensions. which parse date as per the file extension
|
I use this formula, and it works for me, I've been searching for a whole week for this :)
|
Versions
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
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
{
}`
The text was updated successfully, but these errors were encountered: