Create P6 resources dictionary using excel without using SDK

The SDK (Software Development Kit) is an awesome tool that allow P6 database to connect to excel and exchange information in order to create the schedule using excel. But sometimes the SDK is not installed on the Primavera machine and the scheduler doesn’t have the privilege to install it or the resource sheet in the SDK is not working with P6 version that the scheduler has. In this article I will show you how to create the resources dictionary using excel and without the need to use the SDK or XER Parser tool.

 

Step no. (1) – Create P6 resource dictionary that you want to import in excel

Make sure that the Resources ID doesn’t exceed 20 characters and the name doesn’t exceed 255 characters. You can check using the LEN formula

Step no. (2) – Create the project name crew in P6 and export the XER file

  • Create the parent resource in P6 which is in this example called ABC Project Crew

  • Adjust the currency, default unit/time and the calendar

  • Export the resources XER


 

 

Step no. (3) – Open the XER file using notepad and copy the content to excel

  • Right click on the XER file – Open with notepad

  • Right click and select all (Ctrl + A) then right click and copy (Ctrl + C)

  • Open excel sheet then right click and paste (Ctrl + V)

 

 

Step no. (4) – Delete all the tables except the RSRC table and keep only the parent resource entry

Step no. (5) – In the resources dictionary spreadsheet insert the resource ID and parent ID and resource type

  • Copy the resources id from the XER spreadsheet and paste it in the resource dictionary spreadsheet and fill down

  • Copy the project resource ID (Cell A2) and paste it in the parent column next to the first main crew

 

  • Filter column D and uncheck the blanks and fill down the parent column

  • Filter column D on the blanks and remove the entries in column A

  • Insert the following formula in cell A4 :  =IF(A3<>"",A3,B3) and fill down

  • Make column C equal column E and Column D equal column F and fill down

  • Clear the filter and copy column A to D and paste values to remove the formulas

  • Fill down column A

  • Filter column D on the labors, material and equipment and insert in column H either (RT_Labor, RT_Mat, RT_Equip)

Step no. (6) – Copy the content from the resources spreadsheet to the XER spreadsheet

  • Copy columns A & B from the resources sheet and paste them in column B & C in the XER sheet

  • Fill down column A in the XER sheet and add “%E“ at the end

  • Fill down column D in the XER sheet

  • Fill down sequentially Column J in the XER sheet

  • Copy column D in the resource sheet and paste it in column O in the XER sheet

  • Copy column C in the resource sheet and paste it in column P in the XER sheet

 

  • Copy column G in the resource sheet and paste it in column R in the XER sheet

  • Fill down columns S to Y in the XER sheet

  • Copy column H in the resource sheet and paste it in column AA in the XER sheet

Step no. (7) – Insert the data in the XER file and import it to P6

  • Edit the XER file in notepad and delete all it content

  • Copy the data in the XER spreadsheet and paste it in the notepad

 

  • Save and import to P6

 

 

Comments

Yes, for sure the SDK is very

Yes, for sure the SDK is very useful. But sometimes it doesn't work with a specific version of P6 or you are not an administrator on the machine that has P6. That's when this method becomes handy.

Wow. Very Clever......But it

Wow. Very Clever......

But it just confirms to me that the SDK, or a utilty such as Collabro's Legare that populates all the Enterprise dictionaries using the API is essential

Market Place

Primavera P6 and Microsoft Project books, on-line video training courses and training material available from an internationally recognised publisher and PMI accredited REP. Teach yourself using on-line or book based learning or run your own in-house or public PMI accredited courses.
For 25+ years, APMX has been providing competency based project management training to Fortune 500 companies around the world, applying the principles of project based learning, designed to produce measurable results, generating a favorable “return on training investment”.
See how the TIME - LOCATION - VIEW brings clearness to your work programmes