Occasionally we revisit some of our reader’s favourite posts from the annals of the Plan Academy tutorial library, and today we’re revisiting a topic that is always hot – importing updates from excel. Let’s take a look!
Often large projects demand many sub-teams or subcontractors update a portion of the master project plan on an on-going basis.
If your subcontractors are not up to speed on their Primavera skills, you may need a solution that simplifies gathering updates and getting them into Primavera P6.
Enter Excel….and Primavera P6’s import from Excel feature.
This video tutorial will walk you through the process of filtering and exporting a subcontractor’s activities to excel and re-importing the updated spreadsheet into P6.
How to Import Primavera P6 Updates From Excel
Hi everyone, it is Michael. Thanks for coming back to the Plan Academy Online Primavera P6 Training. We are always striving to deliver excellent video tutorials and hopefully today is no exception. I woke up this morning and thought, “What do I want to teach today? How can I help some folks out there do better at planning and what can I deliver that hopefully it’s going to give them some new insights on how to use Primavera?”
What I came up with is to revisit this concept of Reflection projects. If you are not completely familiar with Reflection projects, we have already done a great video tutorial on the site. If you are not there, go ahead check out PlanAcademy.com tutorial menu and go down here to reflections.
If you are not completely up to speed on what reflections are, there is this tutorial here we did using Reflections in Primavera that merge scenarios back in your project. That is going to help give you a starting point for reflections and how to use them.
What we are going to do today with Reflections is show you how to update project plans. How to process updates from sub-contractors, who deliver a monthly update? How do I look at that update and roll it into a master project plan? That is the topic today.
What I have got here is my Railcar maintenance facility project plan. If we scroll down here to the construction section, you can see I have two contract sections. Actually a few but this two we are going to focus on, the grading and drainage contract and the maintenance and building contract.
If I will expand those a little bit, you can see we have this general section under the grading and drainage contract. We are going to assume that the grading and drainage contract is manage by a sub-contractor. They are delivering us updates on that contract via xer file.
Then below, we also have the maintenance-building contract and same sort of thing. That is a sub-contractor, a difference sub-contractor and they are delivering updates to us on a monthly basis. The period that we are going to focus on is this period I have highlighted with curtain on the gap chart.
They are going to deliver us updates for this section. We are going to use Reflections to roll those updates in and approve them before we roll them into the master project plan. That is what the reflection project is going to give to us. I am going to turn the webcam off and I am going to get started.
Let us walk through the process that we are going to follow to set things up before we get started. You will notice among the project screen here that I have my RF0D-1. This is my Railcar maintenance facility.
This is the original project plan. It is the plan that I have been working in that I have been doing my own updating it. Now I want to capture those updates from my contractors on a monthly basis and roll them in.
We need to set up a few things first. Here we go. Here is the set up.
First thing we want to do is to create a Reflection project for each contractor. If I am working with multiple contractors, I will create a Reflection for each one. That is step one.
Step two, we are going to export that XER and we are going to send it to our sub-contractor, in this scenario. They are going to update the plan and send it back to us. We are going to re-import that updated project using the update feature. Then we are going to review/merges the Reflection in.
If we are okay with all of the updates, we have been set up, we will go ahead and locked them in by rescheduling. Let us start by creating reflections. Right click on your project and choose create Reflection.
Primavera does a little bit of thinking. You can see it has come back. It has created my Railcar maintenance facility Reflection it is called RF0D-2 and you can see it is a winner project.
We are going to create another Reflection, the same project. What I want to do is just differentiate this. This goes to my ACME sub-contractor and this goes to BSO. Those are my two sub-contractors.
The one thing I want you to notice here is when I created a Reflection project there’s a field here called source project. You will notice that the source project is essentially is a pointer back to the original project plan. The reason I wanted to bring this up is that there is a bit of a gotcha. That is why I wanted to outline this set up steps for you.
If you send this XER file without creating the Reflection project over to your subcontractor and they update it and bring it back. Now we do not have the ability to do the merge.
For example, look at this project. This is one that came back from ACME. Notice that it has no source project links to it. I cannot really do that merge functionality.
The option does not show up. If that does happen, there is a work around for you. The work around is essentially to take the updates from this and to copy them into a reflection project and then we can do a merge. A little bit of manual work on your part but in case you forget to do this set up steps that I outlined then that will be the work around you’ll want to follow.
Now that I have created these two Reflection projects, the process I would follow would be the open one. Open each of them one at a time and then essentially to export them to an XER file. You can ignore that error. I will export them to an XER file and then I will send them off to the contractors in question. Those contractors will update them, send them back, and let us see what that looks like now.
To import an XER file and update it I need to have that project open first. I go ahead and open my ACME project. Back here and do an import, again you can ignore that error. There is my ACME import files I’m going to go ahead and choose it. This is what we want to see we want to make sure that this import action sees update existing project. Again, we will just make sure we have the selected project. We will go ahead and import. Great, so we will do the same thing with the second project.
Now that I have both of those imports done, I can actually do the merge process now. This is where everything comes to a head. This is the point of this. I will start with ACME. Let us go ahead and merge a reflection into a source project. In case this green is not familiar, what this is a review in a comparison of the data that’s changed in the reflected project versus the original project.
Up here we have the choice this group this changes by subject area so you can see their group to your by activities and it list each activity. It says here is the value before it was merged. Here is the value after we would perform the merge. What you can see is this activity mobilization was not started now it is in progress. It was 20 days of remaining in work now there is only five. We have indicated an actual start of July 7th.
You can go through and see all the changes according to these projects. Now I tend to like to work in this activity view. What that gives me is it gives me the ability to choose what updates will be merged in with these check boxes. Once again, we have the ability to kind of review mobilization this is what has changed and you can see that. Now we have an actual start for this resource assignment as well.
As I merged these changes in if I ever need to go back to the project, how the project is at this moment. I will save a copy of that as a baseline and store it. If ever I need to revert back, I can always restore that baseline as a project and then I’m back where I started. I can also create a backup file if I want to save it to my hard drive. I can have a baseline and an XER file.
I am going to stick with the baseline for now. After merging, what do I want to do with that reflected project? Well, we could keep that reflected project around. Then you will be able to go on and view it or you can delete it if you want because those changes are incorporated in.
Let us go ahead and merge these changes in. Merge process completed successfully. Great, the ACME project is now merged into my Railcar maintenance facility original project. I now do the same process with the second project. Open her up. Right click merge into source project.
Let us look at the report for the BSO sub-contractor. In fact what we are seeing here is we are seeing an in progress activity being marked not started. In a completed activity, be marked not started, what is happening here? Pitfall, when we are working with multi-project merging we need to be cautious that we do not overwrite the previous merged information.
That is exactly what is happening in this situation. We are overwriting the ACME projects updates with a non-updated activities from this BSO project. This is where those check boxes come in handy. We need to use those check boxes to subtract select only activities from the current sub-contractor.
Let us go back to Primavera. These activities are not form the current contractor. The way I can tell is because we have a proper activity number in here. These are all C10 series activities. The real updates from this contractor are the C2G guys, 2G. These two I want to include. This other guys I want to leave out. Here we go.
As you can see if I roll this up, I have left those guys out. I am going to include just these two activities from the 2G C2G series. Let us go and do the merge. Let us go back and have a look at our project now.
You can look at these dates and notice that I have the appropriate actual set on those dates. Here my remaining durations and you can see that the information that we logged in there has come across properly. In fact, some information that does not show up in the report are the percent completes.
Here we logged information about physical percent complete on all of these activities. Although it does not show up in the report, it does come across. Why does it not come across? I cannot say. I am not completely sure.
My gut would tell me that I would like to see that stuff in the report. As we go down to the second sub-contractor, we can see that that information about the physical percent complete has come across as well. The last process would be essentially to reschedule the project and then to go ahead and compare it to the baseline. See how we are doing based on those updates.
That is all I have for you today. Hopefully this tutorial on Reflections has been handy and again if you like it please let us know. Leave a note in our comment section. Have a great day.
Your Comments
Hi Micheal
When building a new project in P6, I will usually will receive my data in Excel format. I create a project in P6 with 1 activity then export the fields that I need to Excel. Next I copy and paste activity names etc. (trying to save key strokes) from the Excel data. I can import the names etc. but the dates/resource qty’s will not import properly to P6. I watched your tutorial and appear to be doing what you have done. I input the qty’s as a text file but it still does not import properly….help
Paul
Paul,
One thing to note is that not all fields can be updated from Excel. If you look at exported data in Excel, you’ll notice that in Row 2 (the 2nd header row), some fields have an asterisk (*) in the column name. Those fields with an asterisk can’t be populated with Excel data. “Start Date” and “Finish Date” or two likely candidates that can’t be updated. That’s because Primavera calculates those fields and populates them. So, my first question is – Are you trying to populate data into a field that is non-updatable?
If the answer to the above is “no”, then I would probably ask if every cell that has data in it in the excel import file has a leading aspostrophe? EG: ‘A1020 or ‘4 or ‘Paul McCartney . Those leading apostrophes need to be there for the import to work. In the tutorial, I mentioned a free excel plug-in called ASAP tools that has a feature to help with this.
One last mention would be about import the right data on the right excel tab. For example, to import resource assignments, the resource must specified on the TASKRSRC excel tab. If you don’t have this tab in your file it might be because of the options you picked on the first export. You have to make sure to check the appropriate data on the “Export Type” screen when running the export wizard.
Paul, hopefully, some of these suggestions point you in the right direction. Please reply back if you’re still having problems and I will see if I can dive in some more to help you out.
Happy planning!
Michael
Hi Michael,
Greetings from Brazil !
I try to use the SDK in Primavera. My version is 8.1.
I already conclude the installation process, but when I need to enter the name and password I can´t stablish a connection with the database.
Can you help me ?
I need to solve this situation very quickly because I need to convert my WBS into a activity-code basis, using the tutorial displayed in this website.
Regards,
Bruno.
Hi Bruno,
You’ll need to send me more information – are you getting an error message? What is happening on login?
Before you can login using the SDK, the user you are logging in with (most likely “admin”?) needs proper security privileges – make sure the user has the Global Profle .
FYI: Bruno’s question relates to another post here -> http://www.plannertuts.com/tutorials/how-to-convert-a-projects-wbs-to-activity-codes-in-oracle-primavera-p6-464/
Send me more info Bruno…and would you mind posting your next comment on the other tutorial…thanks!
Michael
Dear Michael-
Thanks for your Tutorials.
Question->
As per Comment above-
For Updating via Excel-Should not the following be “Editable” in Excel-
Actual Start/Actual Finish/Percent Complete/Remaining Duration/Expected Finish?
Just as an Example-the Expected Finish when Exported has an Asterisk in the Column Header->Which should not be.
expect_end_date
(*)Expected Finish
Could you please confirm in your reply which of the above Fields are “Editable” In Excel by Sub-Contractors-that is not having an Asterisk in the Column Headers?
Thanks Very Much-
Aftab Khan
Hi Aftab,
Unfortunately, many fields that you think would be editable in Excel for reimport are not. I do not know why Expected Finish has an asterisk and is not editable.
However, for me there is no asterisk on
– Actual Start / Finish
– Activity % Complete
– Remaining Duration
– Activity Status
Meaning that these ARE editable in Excel for re-import.
Hope that helps,
Michael
Thanks Michael-
Question->Did the Field for “Expected Finish” Give You an Asterisk in concerned “Column” when Exported from P6 to Excel?
Thanks.
Aftab Khan
Hi Aftab,
Yes there is an asterisk on Expected Finish when exporting that field to Excel. It cannot be updated through excel export / import in P6.
Michael
Hi,
I tried importing to Excel. I have a small problem when I do it.
Some of the activities that primavera imports from excel loose their place on the WBS and move to the top of the program. Is there a way to fix this?
Thanks,
Vijay
Hi Vijay,
Not completely sure. The only thing I can think of is that the data in the wbs_id column in excel was edited so that on the import, P6 doesn’t know the data in that field or can’t link it to an existing WBS. I would check that field before you import to make sure it has valid data in it before you import.
Let me know how it works out,
Michael
Hi
I have a question about WBS UDF in P6 . How can i write thise kind of fild with excel import and export or excel sdk?
Hi Javad,
The only way to import WBS is either using the xer parser http://www.plannertuts.com/tutorials/using-primaveras-xer-file-parser-import-wbs-from-excel-without-the-sdk-840/ or using the SDK http://www.plannertuts.com/tutorials/how-to-import-wbs-from-excel-to-primavera-p6-using-the-sdk-722/ . I would try either of those to import a UDF for WBS.
Hope that helps!
Michael
Michael, It is my first time viewing your website. It is very helpful. It unlocked the export update door for me. Can I highlight rows and add text to make it easier for my subs to know the locations they are updating.
Hi Bob,
I’m sure you can highlight rows by changing cell colors, but adding text is not recommended. P6 is finnicky and won’t import if you edit the column headers, tabs, and add text around. I would do some testing to make sure.
Let us know how you make out.
Michael
Hi Michael,
I’ve problem with the import process from excel to Primavera 6.2.1. The total number of activities that will be imported is around 10.000 – 30.000 activities with 21 fields for each activities.
Currently I’ve to split the data into small number around 2000 activities with 10 fields data and it took around 20 minutes per excel import. I’ve to conduct the process twice a week.
kindly need your advise, may be you have a better way to do this.
Thanking you in advance.
Dear Michael,
Iam trying to import percentage to P6 but i cant , what i did before is exporting xls sheet from P6 then i load the required data including percentage ( physical & duration ) then import to P6 but no percentage come out .
So can you help me ? I just want to know how to transfer percentage from xls to P6 ?
Thanks alot
@Mohamed,
Unfortunately both Physical and Duration % complete can’t be imported from Excel (look for the asterisk in the column name in Excel – indicates that it can’t be updated). The workaround is as follows:
1) Create a User Defined Field for each column and export them to Excel
2) Update the %’s in Excel and re-import
3) Create a Global Change (Tools-> Global Change) to copy the UDF imported-values back to the original fields.
Hope that helps!
Michael
@Michael:
Would this UDF workaround work for setting predecessors and successors?
What about non asterisk fields not importing? In my case, activity codes are not importing.
Thanks.
Hi Michael
With reference to the Physical % Completion, it is a (*) filed in Primavera and it is not a calculated field.
I need to import Physical % completion in Primavera from Excel becaues I need to calculate the EV based on that.
Is there a way to import that instead of entering that manually ?
Thanks
Gihan
When i am updating progress , By going to tools – updated progress,suppose my schedule has 50 activities ,out of that only 10 activities is started actually at site ,when i update the that activities ,the remaining activities dates is taking as base line dates only
@Sha.
This is going to be a bit more than we can troubleshoot over comments. If you’d like to contact me directly about this issue, I can offer some consulting help at a reasonable rate. michael@plannertuts.com
Our case is we have a custom form in oracle EBS this form is a progress sheet for a projects, and its calculate the % of completion for each task (Activity).
What we need is to update % of complete for each activity in primavera , we try to search for a API’s or Interfaces for Primavera but we didn’t have luck .
Our objective is to update multiple activities % of complete programmatically or by API’s or Interfaces to prevent update each activity manually so if there any way to achieve this objective it will be appreciated .
Hi Michael, nice tutorial.
I’ve tried this but also included for suspend and resume dates (I often find an activity may be interupted and need to use this to keep an accurate record).
Unfortunately it didn’t import the suspend and resumes even though they’d been filled in; any idea why?
Thanks
Ah, I’ll answer my own question, the column is asterisked; can’t import, and no to physical % complete as well…
Okay, I gues the subco could fill those in anyway and I’ll enter manually.
Michael, now I really do have a strange one!
I’ve exported using the exact same filters in the source project in the export set-up. I’m exporting from a Reflection. The spreadsheet is only showing activities down to about level 3, most of the project is missing. I’ve tried this a few times and checked all the setting I know about, also tried exporting from the source project (same result).
Any idea why this is?
Turns out this is a know bug in P6. Check out the ‘My Oracle’ website where they suggest a workaround.
Thanks Michael for your commitment towards helping planners and schedulers all over the world. Some of your tutorials have helped some keep their jobs and become better in what they do. Thank you.
Dear Michael,
Can i import Budgeted units for any resources distributed over time from excel to Primavera?
If so, please tell me how.
Thanks,
Rizwan
HI Mike,
Suppose contractor have deleted some activities in their updates. When you are merging a reflection in your master schedule it will automatically delete the deleted activities or you have to go and do it manually?
Your opinion on this will be appreciated. We are managing a project that have 20 to 25 contracts that needs to maintain in a master schedule. It is time consuming for our team to go delete all the activities manually.
Thanks.
A.H
Don’t believe it will delete activities – you’ll have to do it manually.
Dear Michael ,
when i am importing resources on p6 using a spread sheet from the same schedule but previous update some of the duration%complete went up to 100% although it doesn’t have actual finish . How can i get over that ?
Hi Michael, Just came back to you for a solution for importing progress. THansk again for an easy and clear explanation
Regards
from Australia
My pleasure Tom.
Very helpful, thanks for the upload!
Does this work with version 15.2?
Absolutely!
Very clear and thorough explanation, thanks and good job.
Thanks – always aim to be clear and helpful.
Thank You Very Much for this Tutorial. I am about to start a Planning Work of 1 meager Project that will involve a Team of 4 Planners. And this will be useful within the Planning Teams
Hi Michael,
Thanks for this tutorial. Few more questions:
Trying to import remaining cost and at completion total cost, are these two columns can be imported?
And can I import few projects data same time, not one by one?
Thanks
Remaining Cost – Yes. But “At Completion Total Cost” is a calculated field.
I don’t think you can import multiple projects at the same time for excel.