How to clean a Primavera P6 XER file using the XER File Parser

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:

1. Be prone to errors
2. Break your XER files.
3. 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) 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