38
In the previous chapter, we learned how to how to import WBS from Excel to Primavera P6 using the SDK. In this chapter we explain how to import a WBS dictionary in Primavera P6 without the SDK
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
39
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 descriptions 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 is 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.
40
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.
41
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.
42
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
43
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 add the facility to copy and paste from Excel – other Planning software can do it, so why not P6?
44