Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

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]
Thu, 2018-12-13 13:02
Richard Adkins
User offline. Last seen 5 years 19 weeks 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?

Top

Replies

Sun, 2018-12-16 13:32
#1
Steven Auld
User offline. Last seen 7 weeks 1 day ago. Offline
Joined: 13 Sep 2017
Posts: 126
Groups: Petro-Chem / Oil & Gas, TURNAROUND & SHUTDOWN (T/A & S/D)

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      


          

  


  

    

      Top
    


          

              

      





    


  

    

      Fri, 2018-12-14 13:51
          


          #2 
      


  


    

      


 

    

      
 Zoltan  Palffy 


      
              

  

      
      

        User offline. Last seen 4 weeks 3 days ago.
        Offline
      


            
      
          


    

              

          Joined: 13 Jul 2009        

      
      
              

          Posts: 3089        

      
              

          Groups: None        

          


    

      
      
              
    


    

      
      
            
          

  



    


    

      
      

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


          

  


  

    

      Top
    


          

              

      





    


  

    

      Fri, 2018-12-14 06:55
          


          #3 
      


  


    

      


 

    

      
 Santosh Bhat 


      
              

  

      
      

        User offline. Last seen 1 year 3 weeks ago.
        Offline
      


            
      
          


    

              

          Joined: 15 Apr 2005        

      
      
              

          Posts: 381        

      
              

          Groups: Civil Engineering        

          


    

      
      
              
    


    

      
      
            
          

  



    


    

      
      

        
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.
      


          

  


  

    

      Top
    


          

              

      





    


  

    

      Thu, 2018-12-13 18:30
          


          #4 
      


  


    

      


 

    

      
 Tom Boyle 


      
              

  

      
      

        User offline. Last seen 4 weeks 4 days ago.
        Offline
      


            
      
          


    

              

          Joined: 28 Nov 2006        

      
      
              

          Posts: 304        

      
              

          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
      


          

  


  

    

      Top