Microsoft Excel has enjoyed widespread adoption for decades. This makes it a preferred medium for the gathering and/or tabulation of project data to import XLSX into Primavera P6 Professional.
On most projects only a few are well versed in Primavera P6 Professional scheduling. This limits the collecting and inputting of project data to a select few personnel. The good news is that Primavera P6 Professional imports XLSX files. This means that project data gathering efforts can include XLSX spreadsheet submittals from P6 illiterate team members. These submissions may have labor resource assignment project data, which helps expedite the sometimes-laborious process of defining schedule resource assignments in Primavera P6 Professional.
This article demonstrates how to import XLSX labor resource assignment project data into Primavera P6 Professional.
We have below in Figure 1, our demonstration project schedule.
Figure 1
This schedule is in the planning stages of an HVAC project. At this phase in the process the schedule has Work Breakdown Structure (WBS) elements and activities, but no relationships. Having a list of activities, we can assign roles (or trade skills) and/or resources to various activities. Our goal is to specify the labor cost of respective activities in our schedule.
We proceed and demonstrate the process of tabulating labor trade skills and/or resource data in an XLSX spreadsheet, and then importing this labor data into a Primavera P6 Professional schedule. The process begins by exporting project data to an XLSX file. This was done to provide the proper layout P6 requires for the import part of the process.
In Figure 2 we begin the process by first exporting project data to an Excel spreadsheet, Figure 2.
Figure 2
Select ‘Spreadsheet – (XLSX) in the export dialog, Figure 3.
Figure 3
In the Export Type dialog, we toggle to export activities and resource assignments, Figure 4.
Figure 4
Our assumption at this juncture is that the resource database includes all the trade skills and resources required for our schedule. We continue and choose to export our Air Conditioning System schedule, Figure 5.
Figure 5
Proceeding onward we click next, which brings us to the Select Template dialog, Figure 6.
Figure 6
We opt to modify an existing layout. You may have to begin by adding a new template. In the Modify Template, Figure 7, we select the Resource Assignments subject area.
Figure 7
We choose the options listed in Figure 7. Note the two columns Resource ID and Role ID. We want both columns for our labor resource assignments. We proceed and click next and choose the excel file to copy the project data into, Figure 8.
Figure 8
Click next, and to complete the export, click Finish, Figure 9.
Figure 9
After a successful export we open the XLSX file and view the TASK tab, Figure 10.
Figure 10
Okay, now we are ready to populate our TASKRSRC worksheet, where we make our labor resource assignments. In Figure 11 we have our TASKRSRC worksheet with labor resource assignments on activities A1032, A1034, and A1036.
Figure 11
Note that P6 does not read project data from columns that have an (*) in their column heading (row 2). So, it reads the Resource ID, but not Resource Name. It reads the Role ID, but not the Role Name. It is also interesting not note that it does not read Resource Type. The Resource Type identification is in the Resource database, which we assume is populated for both our roles and resources.
The two columns to note are the Resource ID and Role ID columns. For these columns make sure to use the same identification scheme as the P6 resource database. Also, you will find that when you have both a Role ID column and a Resource ID column the payrate assigned to activities becomes the payrate of the Role ID. If you want the payrate to be that specified for the Resource ID, you must remove the Role ID column.
Let’s proceed and import our XLSX spreadsheet including both the Resource ID and Role ID columns, Figure 12.
Figure 12
In the Import Format dialog, Figure 13, we choose to import Spreadsheet – (XLSX).
Figure 13
We choose our updated Excel spreadsheet and click next, Figure 14.
Figure 14
At this time, you may have a warning that the user preferences used to create the Excel file cannot be located. The import still works with this warning message, so we choose Yes and proceed with the import, Figure 15.
Figure 15
In the Import Type dialog, we have two types to import, activities and resource assignments. We choose both in Figure 16.
Figure 16
Moving along, we choose to update an existing project, and import to our currently open schedule, Figure 17.
Figure 17
Finally, click Finish, Figure 18.
Figure 18
After the import choose Tools | Recalculate Assignment Costs, Figure 19.
Figure 19
You may also have to depress (F5) refresh data. The resulting schedule with imported labor project data is displayed in Figure 20.
Figure 20
Note that it was important to set the duration type of activities to fixed duration and units/time. This makes the schedule time constrained and resource constrained. Not setting the activities to this duration type results in erroneous resource assignment budgeted units/time values and resulting budgeted cost values.
Again, we imported both Role IDs and Resource IDs. Our activity labor resource assignments were therefore set to the Role ID payrate, which appears to take precedence when both Role IDs and Resource IDs are imported. The labor resource assignments for each activity are viewable in the bottom detail’s resources tab. Observe in the activity table the budgeted labor cost column, which sums into the column for the budgeted total cost of the schedule.
Also, in this example, note that the box for the resources that Calculate Costs From Units is Checked.
Finally, you will want to look at the resource tab at the PROJECT directory and make sure that, under Resource Assignments, the box entitled Resources can be assigned to the same activity more than once is Not Checked. If this is checked and, you import resources again, you can double dip this activity with two of the same resources.
Summary
Schedulers may export their schedule to an XLSX file with a layout compatible for importing the schedule back into P6. This XLSX spreadsheet may then be updated with relevant labor resource assignment project data. In this way schedulers may capture labor resource assignment project data from team members that are less proficient with Primavera P6 Professional or may not have P6 access privileges.