P3EC Export as XER file Calendar Data Issue

Oracle Primavera - PM6
D
Denise Eagle 👤 Member for 11 years 11 months
· Permalink

Hi,

I have just figured it out too, it took me days. I am working in SQL and I needed to read the database, specifically the CALENDAR table and the clndr_data field. This is the same as the data that you have above, it is in XER format as well.

So when you see the numbers...

(0||Exceptions()

((0||0(d|36126)())

(0||1(d|36153)())

(0||2(d|36311)())

You will note that there are empty brackets after the numbers. If someone is working on that day then the brackets would contain the data for the hours to be worked in that day. So there is no work happening on the above days.

This is what I search for "d|36126)())", but used the date that I had converted to a number.

First I converted todays date to a number and then double checked it in excel. If you put a date into excel and then change the format of that cell from date to number it will give you the equivalent number. In SQL I cast the date to an integer and used that number, but, the number was out by 1, not sure, so I manually added one as an offset number to ensure I had the right number before searching.

Next, I created a function to do a string search but included the empty brackets. If it returned 0 then it was a normal working day, and if it returned a value than then the resource was not working on that day.

Hope that helps,

Denise

D
Denise Eagle 👤 Member for 11 years 11 months
· Permalink

I am not sure that is correct. I have a date number of 36982 that is supposed to be a date in July 2014 but with the cdate function above comes out as 1st April, 2001, so I am not quite there with converting this number to a date.

When I put this number into Excel and convert the value to a date, it too comes out as 1st April, 2001.

When I open this particular calendar in P6 then I can see that the calendar is July 2014.

I am trying to interpret the codes and not having too much luck. :-(

J
John Griffiths 👤 Member for 21 years 5 months
· Permalink

Hi Ronald,



Thanks for your help I realised on Saturday after having checked all the calendars that were in the XER file in P3 that they all started with the same number and loaded the exception numbers into Excel and changed to dates and bingo. Yes agree with you why start the dates from 1899 is soemthing of a mystery.



Once again thanks for your assistance.



John

R
Ronald Winter 👤 Member for 23 years 5 months
· Permalink

Those 5-digit numbers are day numbers, beginning with 12/30/1899. I am not sure why Microsoft starts there but if you perform the VBasic function



Print cdate(36216)



you get the answer,



11/27/1998



Good luck!

Forum Sponsor

Top Posters

Viet Tran
8 posts
Ola Gbotoso
0 posts
Jaturapit Multongka
1 posts
James Williams
74 posts
Haque Nawaz
10 posts
EViLL1
1 posts
Dimitrios Theocharidis
3 posts
MichaelFuelsC
0 posts
anwar zeb
1 posts
olva seselima
1 posts