
In the third part of our Primavera P6 databases and data cleansing series, we will show how to use the parser to clean an xer file
In the third part of our Primavera P6 databases and data cleansing series, we will show how to use the parser to clean an xer file. But if you haven’t read them go back and read: XER File Parser Excel utility
- Part 1: 6 Ways to Clean Up Your Primavera Database Nightmare
- Part 2: Understanding Primavera XER Files
Cleaning an XER File for Import
NOTICE: the Primavera XER File Parser is not an officially supported utility. It may:
- Be prone to errors
- Break your XER files.
- Cause other problem you or we didn’t even think about
Use it at your own risk. Make lots of backups.
If you are finding that importing XER files are slow, then you might want to check out this article about POBS data.
Altering or Removing An XER’s Activity codes
Important notes about Activity codes in P6 :
- The EPS activity codes need to exist in the database before you can import them, and they need to be defined in parent node where you are going to import your project, otherwise they will not be imported.
- There is no way to import EPS activity codes with a project unless you use the parser, or other tools like XER Manager.
- Using P6, Global activity codes can be changed to project activity codes by copying the code and re-assigning them to activities – (a lot of work!) – OR you can do that in the parser by changing one line – handy!
We create a test project with three tasks; every task was assigned Global code, Project code and EPS codes.
In the XER file, three tables are related to activity codes.
ACTVTYPE : Activity codes
ACTVCODE: Activity code values
TASKACTV: Activity codes assigned to activities
1) Click on Project
2) Note here the Project_id is 3734. Click on ACTVTYPE.
What can you change?
Now you can change the activity code type.As an example, we will change
Test1 from global to project
3) Assign the Project ID 3734 under Proj_id, and change the AS_Global to AS_Project.
Test2 from EPS to Project
4) Assign the project ID and remove wbs_id, and change from AS_EPS to AS_Project.
You can also remove all Activity Codes by deleting the rows in the table ACTVTYPE.
Altering An XER’s Calendars
- P6 has three different types of calendar; Global, Project and Resource.
- Resources in P6 can be assigned either a Global or a Resource calendar. (Not a Project Calendar).
- If your Project Calendar inherits from the Global Calendar it will be exported too.
In the sample XER we used all the types of calendar to showcase what changes you can do.
Notice here that Project_Calendar_1 inherits holidays from a Global Calendar – Standard 5 Day Workweek.
And here, Project_Calendar_2 does not inherit from a Global Calendar.
A Resource can use either a Global or Resource-specific Calendar.
In the XER file, calendars are recorded in three tables:
Calendar: this is the Calendar Dictionary
TASK : here you can find Calendars assigned to Activities.
RSRC : here you can find Calendars assigned to Resources.
What can you change?
For the calendar is not a simple change of CA_base to CA_Project.
Calendars are complicated data for example:
If a global calendar is assigned to a resource and a task, and you want to change it to project, you have to create another copy of the global calendar as a resource calendar and assign it to the resource.
Project calendars inherit only from the global calendars, so you can not simple chane the global to project.
So I think it is just better to add a prefix to all the calendars to make sure they will not clash with the existing calendars in P6 database and latter manually cleaning them in P6 client.
Or when you import the XER file make sure to change the template to insert new.
Why is this important?
P6 by default keeps the existing calendar, but P6 checks only the calendar name when importing. In the case where you import a file that has the same name calendar but has different working period or holidays, P6 will assume it is the same and keep the existing, and then you end up with a different schedule.
Obviously don’t uses “Update existing” as it will update existing calendar and affect the existing schedule.
Altering an XER’s Resources
Resource dictionaries are stored in RSRC Table. Notice the resource rates are stored in a separate table RSRCRATE.
What can you change?
It is up to you can add a prefix to the resources and use the default keep existing or use the import option “Insert new”. (But do not use “Update Exiting”.)
Why it is important?
When you import a schedule, P6 will check only against the “rsrc_short_name”, so if the imported schedule use the same short name “ R-2”, but different price per unit, or even the new resource is non labor and the existing is labor P6 will keep the same.
The implication is quite simple: the new imported schedule will have a different total cost.
Altering an XER’s User Defined Fields
If you have to give only one reason to use the Parser, probably it is for the UDF, Planners tend to use a lot of UDF for temporary reason, they can be used as a calculated field in the Global change, and some reason, planners use a lot of different names for the same item, for example for a bill item: Billitem, Pay Item, id, Item_Number.
In the sample XER, there are four UDF, three at the activity level (#Before Start, #Before Finish, Bill Quantity)
And one at the project level (Notes).
In the XER file, UDF are recorded in two tables
UDFTYPE : UDF dictionary
UDFVALUE: UDF assignment
What can you change?
You have many options:
- You can delete UDF that you don’t need ( make sure to delete all the reference in all the tables otherwise you end with orphan records, and the xer will not be imported),
- Add a prefix to those UDF, so they can easily clean them later in the database.
- Map to the existing UDF, ( change the name of the UDF to the existing one in the database)
Altering an XER’s Currencies
P6 export all the currencies in the database even if they are not used by the resources, it make sense to delete those unused currencies. The data is recorded into two tables:
CURRTYPE: currency dictionary
RSRC : Currency used by the resources
The Algerian dinar can be deleted as it is unused
Other Things You Can Alter
For the remaining data type (Expense, Project Codes, Issues, Notebook, etc) download and have a look at the P6 Database schema documentation , all tables, and tables interdependencies are detailed there.
Wrap Up
What else can be easier than opening a file? We do it every day instinctively, with dozen of documents (word, Excel, pictures …).
P6, as any database application, is different. When you open an XER file; in reality you are importing a huge number of data into your database. If you are a standalone user, that may be not that harmful, but if it is installation shared by other planners, that’s becoming quite dangerous and inconvenient.
The fundamental issue here is that the import of an XER file brings in all sorts of Global data associated with the project you’re importing. What can be worst and a pure waste of time than if someone unintentionally alters that shared calendar or resource price/unit after importing an XER file – and you’re left trying to figure out why your data has changed?
P6 is a power database application, and to maintain your database integrity, and avoid a huge waste of time and money, simple rules apply.
- Implement a draconian security policy and ensure the privilege to import XER is governed by a clear procedure.
- Limited access rights for the users – make different security profiles based on skills, planner, senior planner, etc.
The XER FileParser is a handy tool, but it is manual and complicated as you need to understand how P6 record the data. If you import project occasionally, it is a perfect little utility. But if you deal with a lot of external schedules, you may have a look at some commercial software that does this process in an automated way. I use XER Manager myself.
Download
Download the XER File Parser Excel utility here:
- P6xerFileParserBuilder.xls – MS Excel 2003 and prior
- xerFileParserBuilder_2007 – MS Excel 2007 and later
Another great article Mimoune.
Just a word of caution with cell-size limitations of Excel when importing Calendar information that have many Exception dates. Excel will truncate the cell contents at 32,767 characters so you may not notice that you’ve lost some of your exceptions when you save the XER out through the Parser.
Thanks John,
we will update the post with your note.
There is a good reason that oracle doesn’t support this utility 🙂 I hope readers will use it with caution.
Regards
Mimoune
Hi Mimoune,
Something off the topic. Do you know a book or video series that will show how to build a project from scratch on excel till it is fully loaded in Primavera. I am learning about it but getting picks and pieces around is taking a lot of time. I am not finding it very helpful to read the explanation of every function in Primavera without applying it in an example. Please give me any tips that you find it useful for the best way to start to become an expert in it. I have the Ref manual and I have a series in Arabic that show how to load a project into Primavera but I think the guy made a mistake not sure though. Instead of creating WBS for the tasks he imported all the activities and WBS to Primavera as activities then he coded them with activities coding so they are activities in Primavera but treated them as WBS by coding and sorting activities under them. I am trying to be professional so that is why I am trying to find a better way to learn it is for free so surely I cant complain. Thank you.
This simply remarkable message
Hi;
These files do not work for Excel 2010 or 2013?
Great Article, I am having trouble finding a copy of the file parser that works on Excel 2013.
I keep having errors when I try to load .xer file.
I am excited to try this out as my database is totally corrupt after working with a client with a massive database.
Thanks,
Greg
Hi Mimoune,
I have downloaded 2007 version of excel spreadsheet, which was mentioned in this tutorial. When I hit “Load XER” button its giving me error message “Could not load an object because it is not available on this machine:
My guess is that it’s not able to locate XER file. How to solve this problem.
Thanks
Vicky
I am also getting the same error. Could not load and open a VBA Page for “TRIM” error.
Please assist.
When I try to load an xer I get an error that tells me a “license information for this component cannot be found”?
Please visit http://www.avtain.com for automated cleaning of the XER file. You need to specify what to remove and what to convert from global to project structures.
When Xerfileparserbuilder2007 was opened on excel 2010 ver, error happened
Error code : &h 80040111(-2147221231) classfactory is not supported
But on excel 2007 , work well. I think that Active x need to be updated or modifiy code