Welcome to PCO Blogs

This is a place for Registered Users to share their technical view and opinions in form of blogs which they think might be useful to the greater PCO community. We invite prolific writers to share their insights and witticisms, but remind all contributors to be mindful of any copyright rules. ProjectControlsOnline will not be held responsible for any breach in rights and responsible users will be held accounted for the same.

Please note that you may NOT get badges, stars etc. by posting blogs here as they are pretty meaningless as they do NOT make you wealthier, fitter or more attractive to the opposite sex! The best you will get from contributing here is the satisfaction/self-respect from knowing you have helped others.  So, please continue to make positive contribution to this community portal and extend whatever help you can to your fellow colleagues.  Thanks!

 

Please read this help page before creating a blog

spacer
Latest blog entries

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

May 17

Written by:
17/05/2012 14:35  RssIcon

Created by Plannertuts.com
The tutorial is originated from http://www.plannertuts.com/tutorials/using-primaveras-xer-file-parser-import-wbs-from-excel-without-the-sdk-840/
.

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:

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_idwbs_short_namewbs_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 .xeer 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?

Created by Plannertuts.com
The tutorial is originated from http://www.plannertuts.com/tutorials/using-primaveras-xer-file-parser-import-wbs-from-excel-without-the-sdk-840/



1 comment(s) so far...


Gravatar

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

Hello. I developed an Excel tool that allows a user to manage P6 data in Excel tables live on the P6 database without importing.

code.google.com/p/p6xl/

It bypasses the client GUI, thereby facilitating bulk updates. Just as your Wrap Up mentions, it also allows the user to copy and paste P6 schedule data directly to the P6 database through Excel. It uses the SDK, but the user only deals with Excel. I hope it could be of use to you.

By Liam Gaudy on   17/06/2013 19:05

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
CAPTCHA image
Enter the code shown above in the box below
Add Comment   Cancel 
spacer
Search Blogs
spacer