Using Primavera’s .Xer File Parser – Import WBS From Excel Without The SDK

In this tutorial we explain how to import a WBS dictionary in Primavera P6 without the SDK.   NOTE: The steps needed to transform a classical WBS presentation (which is WBS Path and Description) to a format understood by the database (which is parent / child) is covered in Steps 1 to 6 of this tutorial.

To load the WBS data into Primavera P6, instead of using the SDK we will use the XER File Parser utility provided by Oracle.  Essentially, the File Parser is an excel file that can read the data contained in a .xer file, and gives the user the ability to modify some information and to build a new .xer.

It is definitely one of the most powerful utilities for Primavera P6, either standalone or enterprise, and it is much safer to use than the SDK, as you don’t write directly to the Primavera database – unlike the SDK.

Tutorial Files

Complete this tutorial by downloading the following files:

• P6xerFileParserBuilder.xls - MS Excel 2003 and prior
• xerFileParserBuilder_2007 - MS Excel 2007 and later
• WBS_id_Generator.xls

Before You Start

Primavera P6 has a well defined database at its core, but you don’t need to be an IT graduate to understand how it works.  Here’s what you DO need to know:

• Understand what a Primary Key is used for
• Understand what a Foreign Key is used for
• Understand the type of relationships between tables in the database

I recommend googling the terms above. There are a lot of courses available on database structures.

The description of the tables of Primavera P6 r8.2 database tables are available here.  It can be very useful to understand how the P6 database is organized (WBS, Activity Codes, Resources, etc.)

Also, here are some best practices on working with the XER File Parser utility:

• Always work on a copy of the .xer file, and make sure to always build a new .xer file.
• Never directly update a program from a modified .xer file, first import it as a separate project, and check if everything is ok, then you can update your program.
• Never import any .xer file directly to a production database, it’s too easy to contaminate P6 global data objects.  Import to a test database first.

Step 1 – Generate a dummy xer file.

Create a blank dummy schedule in P6 – don’t add any WBS or activities.  Export the blank schedule to .xer format.  The purpose is to have a .xer file with minimal data.

For the purposes of this tutorial, the name of the schedule is Test, and the WBS code, is Sample Oil, Gas WBS.

Step 2 – Open the Xer file using the Excel Parser Utility.

Open the Excel XER File Parser spreadsheet.  Click on Load XER file, select TEXT.xer file then click OK.

Here’s what you should see.  The table for the WBS dictionary is called PROJWBS.   Click the PROJWBS link to edit WBS data.

Here is the result.

The most important columns are wbs_id, wbs_short_name, wbs_name and parent_wbs_id.

In the next steps, we’ll show you how to generate those ids from a WBS.

Step 3 – Generate the WBS_id and the Parent_WBS_id

Open the WBS_id_Generator.xls Excel file.  In the second row, copy and paste the values from the Excel File Parser.  Please notice, we have changed the WBS Code from “TEST” to “Sample”.

Now copy the data in yellow from the Excel file done in the previous tutorial.

The primary key for the table PROJWBS is wbs_id.  We generate a new wbs_id for the new WBS.  Please notice for the first wbs_id we add 100 (40484 — 40584) then we increase the number by 1.

Then for the column Parent_wbs_id, by using a vlookup formula, we can find the corresponding wbs_id for the Parent.

Step 3 – Build the new xer file with the new WBS.

Now all what we have to do is fill the columns with the data of the WBS.

• Wbs_id, wbs_short_name, wbs_name, parent_wbs_id ; copy in the values from the WBS_id_Generator.xls.
  
• Proj_id, obs_id, sum_data_flag, status_code, ev_user_pct, ev_etc_user_value, orig_cost, indep_remain_total_cost, ev_compute_type, ev_etc_compute_type ; copy and keep the same data from the first row
• proj_node_flag : all rows have the value N.
• seq_num : copy the same value to any new rows (which is first value +100)
• Guid : this is another primary key generated by the database, for any new rows leave this column empty.
• Don’t add anything to columns with empty values.

Now click on Build XER file.  Select a new file name (TEST1) then click on OK.

Voila, now you have a new .xer file, loaded with the WBS dictionary.  You can now import that file to your P6 database.  But remember to have a look at it on a test database first – don’t import it to a Production database until you’ve checked the import works.

Wrap Up

The XER File Parser is a must-have utility for any planner especially if he deals with external schedules.  In this tutorial we showed only one particular use, but this utility has much broader use.

But who knows, perhaps one day Primavera will the add the facility to copy and paste from Excel – other Planning software can do it, so why not P6?