It is quick post showing how to import activities status to Primavera using Excel.
Cobra is as an example for a progress measurement system, it can be any other systems.
In P6 always use new template, never trust an old file as things may have changed in the meantime and you end up with discrepancies.
Assuming the schedule is resource loaded, you need first to import
- The new activities status ( actual start, actual finish, remaining duration )
- The new resource status ( Budget hours, Earned Hours, at completion hours)
You need to import first the activity status then the hours.
Export activities status from P6
Make sure you select Export type, Activities
The Activity ID, activity status and WBS code are selected by default, you need to add Actual start, Actual Finish and Remaining duration
P6 will export an excel file LIKE THIS
Now you can transfer the progress data from COBRA, using the activity id as a reference, don’t change the WBS code.
In some cases you may let P6 calculate the remaining duration, personally I prefer to do the calculation outside P6, as it is more flexible, for example you can calculate remaining duration based the actual productivity, which is very easy to do in Excel
Import back to Excel, Make sure to save any error message
Import the hours
The same approach, make sure you select, resource assignment
Make sure to update only the column (Budget units, Actual unit, At Completion Units),
The activity status cannot be updated from this template that’s why you need first to import the activity status and then the resource status.
Note: if the activity status is completed, make sure actual unit = at Completion units.
P6 can assign multiple resources to the same activity, so make sure you don’t have duplicate resources assigned.
Why you must automate the whole thing ?
As you can see the import to P6 is rather straightforward, probably you are asking why I am blogging about it in the first place? And the reason is how do you know your import was successful?
Ok for the hours it is easy, if the total hours match, then the import was probably successful, but what if the total hours do not match? Or how do you know the activity status is updated correctly
One approach is to design a system for example in Excel , Modern Excel or Access that do the following.
- Automatically map P6 activity ID to Cobra.
- Generate the activity status and compare it to the current P6 status
- Import the new status to P6
- Redo step 2 and 3 until the status in P6 match exactly the status in Cobra.
Now if you are spending too much time manually updating P6 ( we all done that), I think it is unfortunate, and honestly it a waste of time better spend in analysis the schedule, ok it is easy said than done, and there are many technical and non technical roadblock to have such a system in place, but I think the most important one, the Planner responsible for the initial preparation of the baseline is more concerned to have his schedule approved than to worry about how the schedule will be maintained later. ( usually it is other planners problems).
but if you stick to this general rules, probably you will do fine
- Only use, contractual Milestones, Access Milestones and resources loaded activities.
- Schedule activities should never be more detailed than your Bill of Quantities
- if you can not measure a task, don’t use it
- don’t use level of effort just for presentation purpose.
Edit : 17-04-2017, for complex project, we just use one generic resource to load the total hours and earned hours per activity, trying to load individual resource type is not feasible and become a nightmare to manage and honestly even getting the “spent Hours” at the activity level is not possible, that’s why I think Earned valued and productivity analysis should be done outside P6
10 thoughts on “How to import Progress to Primavera”
Great method to import progress from Excel to P6, appreciate that. However, the only thing that i can not see here is the EV. May i know how do you transfer the your % complete from Excel to P6?
in my case, we use unit % complete, so we don’t need to transfer that information, it is calculated by P6, all we need is to import the Earned hours and budget hours
that’s what i used to do. but now I’m intending to calculate CPI/SPI in P6 which means my EV and AC can’t be the same.
tracking AC at the activity level is impossible in practice, yes a lot of planners will tell you theoretical how it can be done ( or even P6 manual), but in real world the AC is tracked at the cost account which is higher level than the activities.
but i am curious to hear you experience.
honestly great stuff! However from a theoretical point, I have found that using resources in P6 is so antiquated. I can’t tell you the number of times contractors spend 1000s of hours loading a billion resources into P6, only to toss them out the window when they start updating the project. Just inserting a % to each activity and never looking at resources again. It will be a breath of fresh air to see the planning community realize how useless about 99% of the resources loaded into P6 is. OK, enough of my rant — love your stuff!
totally agree, we use only one generic resource, let’s call it ManHours, we load how much was earned per activity, and how much effort is remaining, but trying to do detailed resource management in a schedule is not feasible, specially in a complex project.
I understand your point and kind of agree with you both as i also used to not gain much from my resources, but now I’m working in a totally different situation where I use different resources (not just a generic/dummy resource) and all my reports and analysis are based on the resources that i have allocated to my tasks.
I hear, but i am just curious how do you get the spent hours per resource per Task, man it is already hard to get the spent hours per task
I use unit% complete for progress update and units are NLUs .So do I need to export twice …once to finalize the remaining duration and second time to finalize units . Will my unit% complete get automatically updated or do I have to put a column for activity % complete .