Incorrect dates imported from XLS file

francis2301
Topic Author
Offline
Premium Member

08 May 2015 14:31 - 08 May 2015 14:33 #1 by francis2301

Incorrect dates imported from XLS file was created by francis2301

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.

Last edit: 08 May 2015 14:33 by francis2301. Reason: correcting wrong sentence

Please Log in or Create an account to join the conversation.

babynus
Offline
Administrator
Moderator

15 May 2015 09:43 #2 by babynus

Replied by babynus on topic Incorrect dates imported from XLS file

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 ?

Babynus
Administrator of ProjeQtOr web site

Please Log in or Create an account to join the conversation.

babynus
Offline
Administrator
Moderator

15 May 2015 10:55 - 15 May 2015 10:55 #3 by babynus

Replied by babynus on topic Incorrect dates imported from XLS file

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='<b>ERROR - File Type not recognized</b><br/>Import aborted<br/>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.

Babynus
Administrator of ProjeQtOr web site

Last edit: 15 May 2015 10:55 by babynus.

The following user(s) said Thank You: francis2301

Please Log in or Create an account to join the conversation.

francis2301
Topic Author
Offline
Premium Member

18 May 2015 14:08 #4 by francis2301

Replied by francis2301 on topic Incorrect dates imported from XLS file

Hi,
I'll take a look until Wednesday and then come back with the results.
Thank you very much.

Please Log in or Create an account to join the conversation.

francis2301
Topic Author
Offline
Premium Member

19 May 2015 22:01 - 19 May 2015 22:08 #5 by francis2301

Replied by francis2301 on topic Incorrect dates imported from XLS file

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.

Last edit: 19 May 2015 22:08 by francis2301.

Please Log in or Create an account to join the conversation.

babynus
Offline
Administrator
Moderator

20 May 2015 00:55 #6 by babynus

Replied by babynus on topic Incorrect dates imported from XLS file

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.

Babynus
Administrator of ProjeQtOr web site

The following user(s) said Thank You: francis2301

Please Log in or Create an account to join the conversation.

Moderators: babynus, protion

Time to create page: 0.063 seconds

Incorrect dates imported from XLS file

Attachments:

Attachments:

Cookies settings

Functional Cookies

Session

Other cookies

Incorrect dates imported from XLS file

Attachments:

Attachments:

Cookies settings

Functional Cookies

Session

Other cookies

Login Form