How to Import Activity Resource Data into P6 with Excel

Importing activity resource data with excel spreadsheet is to

(a) To update the Resource Names/ID, Budgeted Units, Max Units/Time and Resource Type which already have in existing project.

(b) To add Resource Names/ID, Budged units, Max Units/Time and Resource type for the new activities do not have resources and budgeted unit.
In order to facilitate the process of importing resources, you need to define necessary resources in Resource dictionary. Activity resources that you are trying to import to P6 should be available in the Resource dictionary. As default, there are 6 activity resource columns in Resource dictionary; Resource ID, Resource Name, Resource Type, Unit of Measure, Primary Role, Default units/time.
Remember to change the Resource Type to Materials when you want to create materials resource and change the Resource Type to Nonlabor when you assign equipment or machinery resources as the Resource Type is always equivalent to Labor as default.

Before attempt to export/import budgeted unit for activity resource, remember to set the Unit of Time in the user preferences. This is to avoid unnecessary rework. If you intend to import man-day unit, then set to the day from the Unit of Time drop down list in User Preferences setting. If you want to import man hour unit, then select the hours.

You will find it under, “Edit” then “User Preferences”. Select the Hour, day, Week, etc from the Unit of Time drop down list in the Time Units tag.

After completing the preparation work, you can proceed to to export activity resource excel spreadsheet data from P6 as below;

1.  Open the project you intend to export/import.
2. Go to File > Export > Select Spreadsheet-(XLS) in Export format window, and then select the type of data you want to select (in this case select Resource and Resource Assignments)
3. After selecting Next, Projects To Export window will appear and place a check mark to Export.

4. Add new template if there is no template available. Modify the template if existing template is available. As default 9 column data is available as shown in figure below.



5. Give the template name that you made changes according to requirement and that will help you to reduce the rework when you want to export the same data in future. In this case, Budgeted Units column is added from the Available Options in order to import the new budgeted quantity (budgeted unit) for each activity resource. See figure below.


 
6. Then, select Next after completing the template. Select the location and give the file name in order to save the exported file.


7. Suppose the exported excel file is as below.  Select TASKRSRC worksheet to update budgeted units.

As I stated in the earlier post, the following tips should be consider before attempt to import to P6.

• Make a copy of your project before importing into existing one
• The database field names where the data are in the first row of spreadsheet must not be modified. In this case, task_id, TASK__status_code, rsrc_id, role_id, acct_id, rsrc_type, cost_per_qty, target_qty, start_date, end_date, 'delete_record_flag as shown in above figure.
• Activity ID should not be changed otherwise existing activities can not be imported.
• Some of the column heading containing (*) sign must not be changed.
• RSRC and USERDATA sheet must not be deleted.

After changing the data, and when you are ready to import,

1. Go to File > Import > Select Spreadsheet-(XLS) in Import format window > then click Next > and proceed to Select Excel File that you want to import > then click Next
2. Import Type window will appear in order to select Resource and Resource Assignments.
3. Select the Project in Import Project Options. You can select the opened project only. Hence you must open the project that you try to import. And then click Next and Finish.
4. Remember to check the budgeted labor unit, budgeted non labor unit so that you can compare the data after imported back.