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]
Richard Adkins
User offline. Last seen 2 years 12 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?


Steven Auld
User offline. Last seen 10 hours 27 min ago. Offline
Joined: 13 Sep 2017
Posts: 122

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

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


will become: 
 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:   
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
Hope that helps.  Steven
Zoltan Palffy
User offline. Last seen 2 days 16 hours ago. Offline
Joined: 13 Jul 2009
Posts: 2882
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 1 day 23 hours ago. Offline
Joined: 15 Apr 2005
Posts: 322

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 1 day 23 hours ago. Offline
Joined: 28 Nov 2006
Posts: 301
Groups: None


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