Forum Sponsor:

An innovation in P6 Professional training from RPCuk in partnership with Oracle. All the topics that matter to you in workshops customised to your role and industry.  Now get superior training value when you book our Blended Learning Bundle. Learn more here.

Viewer for .xer, xml and xls schedule files Schedule Reader™Free Trial

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

XER File Parser - How to interpret or import to Excel the clndr_data table?

4 replies [Last post]
Richard Adkins
User offline. Last seen 43 weeks 6 days ago. Offline
Joined: 10 Dec 2018
Posts: 4
Groups: None

I've just started to examine the tables within XER files. Within the Calendar table, there is a field called clndr_data, which has the calendar data for each calendar, i.e. the working time / days within the calendar and exceptions.

The problem is how to take this data and then import to say Excel to understand the data further and take a long alphanumeric string and convert this into something more useful. So far I've not found a delimiter.

Has anyone got any advice?

Replies

Steven Auld
User offline. Last seen 1 week 2 days ago. Offline
Joined: 13 Sep 2017
Posts: 106

The clndr_data field contains nested arrays of the Calendar Day's

The Brackets in the Data "()" give the line breaks, so:

(0||CalendarData()((0||DaysOfWeek()((0||1()())(0||2()((0||0(f|16:00|s|08:00)())))(0||3()((0||0(f|16:00|s|08:00)())))(0||4()((0||0(f|16:00|s|08:00)())))(0||5()((0||0(f|16:00|s|08:00)())))(0||6()((0||0(f|16:00|s|08:00)())))(0||7()())))(0||Exceptions()((0||0(d|41519)())(0||1(d|41155)())(0||2(d|40364)())(0||3(d|40910)())(0||4(d|41633)())(0||5(d|41094)())(0||6(d|40427)())(0||7(d|40536)())(0||8(d|41235)())(0||9(d|40179)())(0||10(d|40903)())(0||11(d|40543)())(0||12(d|40791)())(0||13(d|41421)())(0||14(d|40728)())(0||15(d|41275)())(0||16(d|41606)())(0||17(d|40329)())(0||18(d|41459)())(0||19(d|40871)())(0||20(d|40507)())(0||21(d|41057)())(0||22(d|40693)())(0||23(d|41268)())))))(0||CalendarData()

will become: 
(0||CalendarData()
((0||DaysOfWeek()
((0||1()
()
)(0||2()
((0||0(f|16:00|s|08:00)()
)))(0||3()
((0||0(f|16:00|s|08:00)()
)))(0||4()
((0||0(f|16:00|s|08:00)()
)))(0||5()
((0||0(f|16:00|s|08:00)()
)))(0||6()
((0||0(f|16:00|s|08:00)()
)))(0||7()
()
)))(0||Exceptions()
((0||0(d|41519)()
)(0||1(d|41155)()
)(0||2(d|40364)()
)(0||3(d|40910)()
)(0||4(d|41633)()
)(0||5(d|41094)()
)(0||6(d|40427)()
)(0||7(d|40536)()
)(0||8(d|41235)()
)(0||9(d|40179)()
)(0||10(d|40903)()
)(0||11(d|40543)()
)(0||12(d|40791)()
)(0||13(d|41421)()
)(0||14(d|40728)()
)(0||15(d|41275)()
)(0||16(d|41606)()
)(0||17(d|40329)()
)(0||18(d|41459)()
)(0||19(d|40871)()
)(0||20(d|40507)()
)(0||21(d|41057)()
)(0||22(d|40693)()
)(0||23(d|41268)()
)))))
 In the DaysOfWeek section, this is the base hours per day, where 0||1() starts on the Sunday & ending with 0||7() being the Saturday.The Line under the Monday entry "0||2()" gives the Work Period Start & Finish times: ((0||0(f|16:00|s|08:00)(), so thiis starts at 08:00 & finishes at 16:00. There may be multiple lines under this if there are multiple work periods - each line shows the start & finish times for that period.  In the Exceptions section, the breakdown is as follows:   
0||0(d|41519)()
Shows start of LinePosition of Date in array (Starting from 0 to however many exceptions there are)Date in numeric format - if you take this number into excel then change the Formatting to Date, it will give you the correct dates.
 If there is no Start & Finish Time, then the exception is a holiday (No defined Work Periods)If there are Start & Finish Times after each exception date then this indicates a different Work Period is used instead of the standard Work Week.The example above is one of the calendars in the Sample Files that are included as an option in Primavera - this has the standard Monday to Friday Work Periods, with some exceptions with No Work Periods (i.e. Holidays / Non Working Days) I have used Notepad++ to add the line breaks instead of having one long string, then I can paste this into Excel & then perform any calculations from there. The Calendar Data above then gives the following: 

Code Value
CategoryDay / DateStartFinishHours
(0||CalendarData()     
((0||DaysOfWeek()DaysOfWeek    
((0||1()DaysOfWeekSunday   
()DaysOfWeekSunday   
)(0||2()DaysOfWeekMonday   
((0||0(f|16:00|s|08:00)()DaysOfWeekMonday08:0016:008
)))(0||3()DaysOfWeekTuesday   
((0||0(f|16:00|s|08:00)()DaysOfWeekTuesday08:0016:008
)))(0||4()DaysOfWeekWednesday   
((0||0(f|16:00|s|08:00)()DaysOfWeekWednesday08:0016:008
)))(0||5()DaysOfWeekThursday   
((0||0(f|16:00|s|08:00)()DaysOfWeekThursday08:0016:008
)))(0||6()DaysOfWeekFriday   
((0||0(f|16:00|s|08:00)()DaysOfWeekFriday08:0016:008
)))(0||7()DaysOfWeekSaturday   
()DaysOfWeekSaturday   
)))(0||Exceptions()Exceptions    
((0||0(d|41519)()Exceptions02-09-2013   
)(0||1(d|41155)()Exceptions03-09-2012   
)(0||2(d|40364)()Exceptions05-07-2010   
)(0||3(d|40910)()Exceptions02-01-2012   
)(0||4(d|41633)()Exceptions25-12-2013   
)(0||5(d|41094)()Exceptions04-07-2012   
)(0||6(d|40427)()Exceptions06-09-2010   
)(0||7(d|40536)()Exceptions24-12-2010   
)(0||8(d|41235)()Exceptions22-11-2012   
)(0||9(d|40179)()Exceptions01-01-2010   
)(0||10(d|40903)()Exceptions26-12-2011   
)(0||11(d|40543)()Exceptions31-12-2010   
)(0||12(d|40791)()Exceptions05-09-2011   
)(0||13(d|41421)()Exceptions27-05-2013   
)(0||14(d|40728)()Exceptions04-07-2011   
)(0||15(d|41275)()Exceptions01-01-2013   
)(0||16(d|41606)()Exceptions28-11-2013   
)(0||17(d|40329)()Exceptions31-05-2010   
)(0||18(d|41459)()Exceptions04-07-2013   
)(0||19(d|40871)()Exceptions24-11-2011   
)(0||20(d|40507)()Exceptions25-11-2010   
)(0||21(d|41057)()Exceptions28-05-2012   
)(0||22(d|40693)()Exceptions30-05-2011   
)(0||23(d|41268)()Exceptions25-12-2012   
Hope that helps.  Steven
Zoltan Palffy
User offline. Last seen 9 hours 28 sec ago. Offline
Joined: 13 Jul 2009
Posts: 2433
Groups: None

yes xertool kit is a good one to look at calendars you can print them out also 

Santosh Bhat
User offline. Last seen 9 hours 16 min ago. Offline
Joined: 15 Apr 2005
Posts: 215

Agree with Tom, I spent several days effort in building nested IF statements to figure out the calendar data. I thought I had it, until I realised the way that P6 treats "Exceptions" is very counter-intuitive.

Get a hold of XER Toolkit, its worth the price just to be able to examine calendars.

Tom Boyle
User offline. Last seen 10 hours 46 min ago. Offline
Joined: 28 Nov 2006
Posts: 261
Groups: None

Richard,

The answer depends on what “something more useful” you are shooting for. 

If you actually want to display a graphical calendar in Excel like the one displayed in P6, then parsing the clndr_data string from the xer is the least of your worries.  I’d suggest looking for XER Toolkit, which already does this.  (Maybe also Primavera Reader.)

The data are not so much “delimited” as “nested” into structured blocks of data, using parentheses.  Although all the attributes needed to define the calendar are included, Excel is not going to decipher them on its own.  If you really, really wanted to, I suppose you could write some code to parse each string into the corresponding tables, but there’s not much value in having those tables unless you’re going to do calendar-math scheduling in Excel.  Seems unlikely.  One thing I have done is build a crude table of calendar exceptions using formulas that search for the index of each particular date in each calendar string.  Ultimately not very useful if you have access to P6 or one of the other tools.

Good luck, tom