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

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?

Oracle Primavera - PM6
S
Steven Auld 👤 Member for 8 years 8 months
· Permalink

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 Line
Position 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 Category Day / Date Start Finish Hours
(0||CalendarData()          
((0||DaysOfWeek() DaysOfWeek        
((0||1() DaysOfWeek Sunday      
() DaysOfWeek Sunday      
)(0||2() DaysOfWeek Monday      
((0||0(f|16:00|s|08:00)() DaysOfWeek Monday 08:00 16:00 8
)))(0||3() DaysOfWeek Tuesday      
((0||0(f|16:00|s|08:00)() DaysOfWeek Tuesday 08:00 16:00 8
)))(0||4() DaysOfWeek Wednesday      
((0||0(f|16:00|s|08:00)() DaysOfWeek Wednesday 08:00 16:00 8
)))(0||5() DaysOfWeek Thursday      
((0||0(f|16:00|s|08:00)() DaysOfWeek Thursday 08:00 16:00 8
)))(0||6() DaysOfWeek Friday      
((0||0(f|16:00|s|08:00)() DaysOfWeek Friday 08:00 16:00 8
)))(0||7() DaysOfWeek Saturday      
() DaysOfWeek Saturday      
)))(0||Exceptions() Exceptions        
((0||0(d|41519)() Exceptions 02-09-2013      
)(0||1(d|41155)() Exceptions 03-09-2012      
)(0||2(d|40364)() Exceptions 05-07-2010      
)(0||3(d|40910)() Exceptions 02-01-2012      
)(0||4(d|41633)() Exceptions 25-12-2013      
)(0||5(d|41094)() Exceptions 04-07-2012      
)(0||6(d|40427)() Exceptions 06-09-2010      
)(0||7(d|40536)() Exceptions 24-12-2010      
)(0||8(d|41235)() Exceptions 22-11-2012      
)(0||9(d|40179)() Exceptions 01-01-2010      
)(0||10(d|40903)() Exceptions 26-12-2011      
)(0||11(d|40543)() Exceptions 31-12-2010      
)(0||12(d|40791)() Exceptions 05-09-2011      
)(0||13(d|41421)() Exceptions 27-05-2013      
)(0||14(d|40728)() Exceptions 04-07-2011      
)(0||15(d|41275)() Exceptions 01-01-2013      
)(0||16(d|41606)() Exceptions 28-11-2013      
)(0||17(d|40329)() Exceptions 31-05-2010      
)(0||18(d|41459)() Exceptions 04-07-2013      
)(0||19(d|40871)() Exceptions 24-11-2011      
)(0||20(d|40507)() Exceptions 25-11-2010      
)(0||21(d|41057)() Exceptions 28-05-2012      
)(0||22(d|40693)() Exceptions 30-05-2011      
)(0||23(d|41268)() Exceptions 25-12-2012      
Hope that helps.
 
 
Steven
Z
Zoltan Palffy 👤 Member for 16 years 10 months
· Permalink

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

S
Santosh Bhat 👤 Member for 21 years 1 month
· Permalink

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.

T
Tom Boyle 👤 Member for 19 years 6 months
· Permalink

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

Forum Sponsor

Top Posters

Julian Pegg
1 posts
Peter Nagy
2 posts
Raymund de Laza
17 posts
Syed_Asad
0 posts
Tony Greyvenstein
0 posts
Ahmed Al-Jubouri
13 posts
Umar Alvi
3 posts
Sibusiso Mahlalela
0 posts
Michael Samanyayi
3 posts
Simon Gumede
0 posts