Hi,
I'm importing to projeqtor several activities from XLS files using the "import data" functionality. I have no problem with the import process itself, but I realised that the dates that are being recorded in projector are diferent from the ones in the XLS file by " -01 day ". Example: if in the XLS file the activity START and END dates are "2015/01/20" and "2015/01/30", when imported to projeqtor these dates will be "2015/01/19" and "2015/01/29" (date format yyyy/mm/dd).
Please see the attached XLS file with an example of activities which were imported and the result after importing them in projeqtor in "Planning screen". Pay attention to the start and end date, comparing XLS file dates X projeqtor dates.
I'm not sure the reason of it. Just to make sure, I've attached my global configurations related to days and work allocation, to make sure it is not wrong.
PS: in the XLS file, the activity status is set as "Registrado" (it is Brazilian Portuguese), which is equal to "recorded" in English.
Kind regards.
Very strange.
I cannot reproduce the issue.
When importing your file, dates are imported exactly as in the file.
(see attached screenshot)
Do you also reproduce if your import file is in CSV format ?
Hi,
I found a hint : it seems that XLSXReader::toUnixTimeStamp() takes into account Timezone.
Try and add "date_default_timezone_set('UTC');" on line 166 of /model/Importable.php
switch($extension){
case "csv":
$data=Importable::importCSV($fileName);
break;
case "xlsx":
$data=Importable::importXLSX($fileName);
date_default_timezone_set('UTC');
break;
default:
errorLog("ERROR - File Type not recognized");
errorLog("File Name : ".$fileName);
$msg='ERROR - File Type not recognized
Import aborted
Contact your administrator';
self::$importResult=$msg;
return $msg;
break;
}
Please confirm that this fixes the issue so that I can include it in V5.0.
Hi,
I'll take a look until Wednesday and then come back with the results.
Thank you very much.
Hi babynus,
I've just added the command "date_default_timezone_set('UTC');" on line 166, imported the XLS file again, and now dates were correctly imported. After that, I've changed the dates in XLS file, imported it again, and all dates were correct in projeqtor.
Just one question: the command "date_default_timezone_set('UTC');" shouldn't be added on line 163? I don't know PHP, but I wonder if that piece of code does not manage imports of CSV files. (PS: sorry if the question is kinda stupid. I really don't know PHP and have no programming skills :blush: )
Thank you very much for your help.
Kind regards.
Hi,
No, you're question is not stupid - in fact, the only stupid question is the one that is not asked 😉
You're proposale could be correct, but in fact the issue is not coming from date in PHP but from XSLX reading library wich expect to deal with dates includng time.
For this library 2015-05-20 00:00:00 (as stored in XLSX file) corrected with location time can be (for instance with -4 from UTC) something like 2015-05-19 20:00:00, and was imported as 2015-05-19 in ProjeQtOr.
In my case (with +2 from UTC) same date was converted as 2015-05-20 02:00:00, and imported as 2015-05-20 (correct date).
Now, with new function to make PHP believe the locale time is UTC, the same date is alway retrieved as 2015-05-20 00:00:00 and so correctly imported.
For CSV import, there is not conversion depending on locale : date as 2015-05-20 00:00:00 is read and stored as is.
So, adding the command as you propose will have exacly NO effect 😉
Hope that was clear.
Regards.
It certainly was clear. Thank you very much for explaining it.
Regards