Step 1 – Copy the WBS from to Excel
For simplification, every step of the tutorial has its own Excel worksheet in the spreadsheet (see WBS-Import-P6-Tutorial.xls in Tutorials Assets). I’ve numbered each worksheet appropriately.
For this tutorial, we’re using a sample WBS from this site. We’ve already cleaned-up and pasted it for you into the WBS-Import-P6-Tutorial.xls on Worksheet 1.
I have modified the original WBS to add a root node (SAMPLE – the first entry in the WBS list) as the project name.
Step 2 – Split the WBS Path into Distinct Levels
Copy the column WBS Code from “Worksheet 1″ to “Worksheet 2″ in Excel. We will use the Text to Columns function on the Excel Data tab (Excel 2010), it a very easy tool to split one column by delimiters into multiple columns.
Highlight the WBS Code column, and click on Text to Columns.
When the wizard screen comes up, select Delimited, and click Next.
Set the delimiter to a dot "."
Make sure to format the columns as Text (if your code is 01, Excel will keep it 01 as text, otherwise it will change to 1.
Here’s the result.
Step 3 – Organize The WBS by Level.
Copy the new split columns to “Worksheet 3″ and paste each under the appropriate L1, L2, L3, L4 or L5 heading (use the yellow L columns, not the green ones). If you have more levels, just adjust the spreadsheet to add them in.
The Level column on Worksheet 3 is not needed for this tutorial. I have just added it to show that we can determine the level of each WBS by counting how many times the separator (or the delimiter) is repeated.
The green L-columns contain some text formulas that will recombine part of the WBS Path to build parent and children fields that we’ll need for the import.
Step 4 – Organize the WBS by Parent & Child
We’re going to combine all of the green L-columns now on Worksheet 4. Start by copying the data under green-L1 and pasting it to the Parent column on Worksheet 4. Copy the green-L2 data and paste it to theChild column on Worksheet 4.
The next steps might seem unintuitive, but they are important to follow correctly. We’ll then clean things up afterwards.
Copy the data as follows:
Essentially the column data should be combined like this:
| Parent col | Child col |
|---|---|
| L1 data | L2 data |
| L2 data | L3 data |
| L3 data | L4 data |
| L4 data | L5 data |
Actually that’s the most important step in this tutorial, in order for the SDK (a relational database) to understand the structure of the WBS; this one must be presented as Parent and children table.
Step 5 – Remove Duplicates
Copy the columns Parent and Child from Worksheet 4, and paste them to Worksheet 5.
Highlight both columns. On the Excel Data tab, click Remove Duplicates to clean up any duplicate entries we’ve created.
Next, select ok
In the column “check the same relation”, I put a formula to check if there is the same item in parent and child. Click the column Filter and select only “No”. This will filter out any other entries we don’t need for the import.
Step 6 – Separating Child and Parent WBS Items
With the column filter still set to “No” highlight the Parent and Child columns, copy and paste them to Worksheet 6 as follows:
The WBS Code column is automatically generated by removing from the WBS ID CHILD code the equivalent Parent WBS ID.
Now our WBS is ready to be imported to Primavera P6.
Step 7 – Open Primavera P6
In Admin -> Admin Preferences, check the maximum WBS Code length that Primavera P6 can accept. The default of 20 will work for this tutorial, however, you may want to increase this value to 30 or 40 characters for future imports.
You’ll want to have an empty project already setup in P6 that we will import to. If you don’t have one, login to Primavera and create an empty project now, then come back to this step.
By default, the first level of the WBS is the name of the project.
In this case change the name of the project to SAMPLE, once you finish importing you can modify the name.
Step 8 – Open The P6 SDK WBS.xls
As usual input the password, and click on Update Project List.
Select the empty project you created. **Make sure you select the right project.
Click on “Get WBS”.
As expected, only the WBS root node is imported into the spreadsheet.
Copy and paste from the Excel file the columns Parent WBS ID, WBS Code and the WBS Name
Don’t overwrite the root node that is already there.
Click “Set WBS”.
It will take some time to process the data (the SDK is famous for very being slow).
Step 9 – Enjoy
Wrap Up
We explained how to import a WBS dictionary to Primavera P6 using Excel and the P6 SDK. Here are some examples as to why it is very useful for planners:
Things for you to try:
