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

011712_0329_HowToImport6.png

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_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?

Comments

  1. says

    I tried the test file as u mentioned, it worked. But i worked on file which i need as i did earlier…and once i tried to import the file, it says the “The import file is invalid”. I ran the diagnostic and found nothing wrong. What could be the reason. please help. Thanks in advance.

  2. says

    I tried the test file as u mentioned, it worked. But i worked on file which i need as i did earlier…and once i tried to import the file, it says the “The import file is invalid”. I ran the diagnostic and found nothing wrong. What could be the reason. please help. Thanks in advance.

  3. says

    Excellent possibility for wbs construction. Thanks

    I am using P6V7SP3

    I tried the parser and when building the XER file, I don´t get the header line in the XER, so it can not be imported to Primavera.
    Also, there is a column not included in your example thatt appears in my Primavera export: est_wt. It seems we can have it empty for new rows

    Thanks again

    EG

  4. says

    Mimoune, How can we check whether the P6 loaded on our system has XER File Parser utility provided by Oracle? If not then can we get this Parser from the internet?

    Rohit

  5. says

    Michael-

    Is there a simple way to Import Cost IDs and Cost ID Names (Cost Dictionary)-after populating these Fields in Excel and then Importing them into P6? Will a Direct Import work?

    Thanks.

    Aftab Khan

  6. says

    Aftab,
    A simple way? Not really. Populating them in excel and then importing to P6 will not work I think and there is no direct import feature that I know of. Best to enter them manually into P6.
    Michael

  7. says

    Hi,

    Load your .xer file into the P6XerfileParserbuilder spreadsheet. Goto the TASK tab of the spreadsheet. Look for the column called “phys_complete_pct”. That is the Physical Percent Complete field you will want to update.
    Good luck!
    Michael

  8. says

    Excelent tutorial,

    Is the Parser able to generate activities also?

    Any word on an upcomming version of Primavera?

    If so, will this new version be able to copy and paste from Excel?

    Thanks for the info

  9. says

    Hi Luis,
    The parser simply builds an .xer file – you decide what data goes in it, including activities.
    We’re expecting an update to version 8 – 8.3 that is – to be released in Jan 2013, but there’s no info as to what enhancements Primavera will make.
    Warmest regads,
    Michael

  10. Mudi Ewere says

    When I click on ‘Load XER File’, I get the following error message on Microsoft VBA:

    Compile error Cant find project or library

    I sent the same XER File Parser Builder to my colleague and he can open his XER file.

  11. Mudi Ewere says

    Thanks Michael,

    I have tried the instructions in the link. I still get the initial error message when i click on ‘Load XER file’. However it now gives me the option to find the XER file and once I click on browse I get a new error message: ‘compile error – variable not defined’.

  12. says

    Mudi, what is different between you and your colleague. Are you using the same version of Excel? Are you loading the same .xer file? I suspect it’s a configuration issue on your pc.

  13. says

    Hello!

    Nice idea!

    But when I try to click on buttons then following massage appears: “License information for this component not found. You do not have an appropriate license to use this functionality in the design environment.” And macro don’t work.

    How to fix it?

    I use MS Office 2010 Professional on Windows 7 Pro 32bit

  14. says

    Igor, it looks like you have problems with you installation of Excel and Visual Basic. It’s not related to the file parser specifically. I googled the error and found this microsoft support page – http://support.microsoft.com/kb/177799 . Try that or google the error message and see if someone has posted a solution on some of the forum sites.
    Hope that helps,
    Michael

  15. Igor says

    Hello, Michael!

    Thanks for googling and link. I will try to patch (via our sysadmin).

    What type of ActiveX elements require additional licenses?
    Witch VBA’s version required for using this XER-parser?

    Thanks!

  16. Igor S says

    Hello again!

    I tried to apply Microsoft patches, but nothing changed (patches for VBA v open Macros (VBA Editor) > open original P6xerFileParserBuilder.xls > and drag Modules and forms from original P6xerFileParserBuilder.xls …

    And when I touched and tried to drag from “frmPrompt” then appeared dialog “License information for this component not found. You do not have an appropriate license to use this functionality in the design environment.”

    What’s wrong with “frmPrompt”? How this form related to licenses?

  17. Igor S says

    Hello again!

    I tried to apply Microsoft patches, but nothing changed (patches for VBA v6.0 or less)

    Info about VBA at office PC: “Version 1619 / VBA Retail 7.0.1619 / Forms3: 14.0.0.6009.100″ (At home I have the same VBA’s version an? all works fine).

    I tried to create at office PC new Excel-file > open Macros (VBA Editor) > open original P6xerFileParserBuilder.xls > and drag Modules and forms from original P6xerFileParserBuilder.xls …

    And when I touched and tried to drag from “frmPrompt” then appeared dialog “License information for this component not found. You do not have an appropriate license to use this functionality in the design environment.”

    What’s wrong with “frmPrompt”? How this form related to licenses?

  18. says

    Igor,
    I just found a different version of the file parser spreadsheet optimized for excel 2007 and later. I have updated the post with a link to download it. Please try this file instead to see if it resolves your issues.
    Regards – Michael

  19. says

    Igor,
    I’m not the owner or developer. Oracle is. The file is available through the Oracle Support website.
    However, if you would like to add your enhancements, we can offer this updated version here on plannertuts. Email me michael[at]plannertuts.com
    Michael

  20. says

    Great tutorial and I’ve used it with a bit of success. but I do have a problem, the WBS code is not importing, it is coming in as just the short name from the original export and not coming in with all the coding? any help would be great

  21. says

    Hello,

    Can I use this paser file to create new activity codes and import into P6?
    I need to create around 500 activity codes, it is very difficult when I need to create one by one in enterprise field.

  22. says

    @Ahmed – sorry, the file parser is not meant for that sort of work. Here’s what to do briefly – Visit our post here http://www.plannertuts.com/tutorials/how-to-convert-a-projects-wbs-to-activity-codes-in-oracle-primavera-p6-464/ . Download the Dictionary6v7.xls file that works with the p6 sdk (you’ll have to install the sdk first to use this spreadsheet). Then you can use the Actiivity Codes button in the spreadsheet to write activity codes to the db.
    Best of luck,
    Michael

  23. sara says

    hi
    I just have the problem like Mr mudi and I will be happy if you can help me because i think that it’s a very useful topic

  24. Clive Perkins says

    Tried using paser builder_2007 with Primavera P6 version 7 sevice pack 3. When I import the re-built XER file I get a meesage “the import file is invalid”.
    Please can you advise what am I doing wrong?

    Regards,
    Clive Perkins

  25. Clive Perkins says

    Micheal,
    Re:“the import file is invalid”
    I get the error message when I import the re-compiled XER file back into Primavera P6.

    Regards,
    Clive Perkins

  26. says

    Clive

    sometime the xer parser delete the first header in the recompiled xer file.

    just open the original xer file using notepad, copy the first line and past it to the modified xer .

    the missing header is something like

    ERMHDR 7.0 2013-12-05 Project admin Mimoune etc …..

    regards
    Mimoune

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>