Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Import codes from Excel to P6 activity codes directory

9 replies [Last post]
Marat Vildanov
User offline. Last seen 6 years 21 weeks ago. Offline
Joined: 16 Oct 2017
Posts: 4
Groups: None

Good day.

I have desined model of activity codes in excel and now need to import it to P6 instead of add every code and value one by one (i have more than 600 values). The question is is there any way to import it?

Thanks!

Replies

Zoltan Palffy
User offline. Last seen 3 days 19 hours ago. Offline
Joined: 13 Jul 2009
Posts: 3089
Groups: None

that is correct set it up then import the values

its a 23set process

step 1 create the code name 

step 2 create the code value

step 3 assign the code value to the activity. (this part can be done thru excel easily)

Steven Auld
User offline. Last seen 3 weeks 1 day ago. Offline
Joined: 13 Sep 2017
Posts: 126

Zoltan,

Apologies - I should have read more into the Typically in your comment "typically you need to already have the code name and code value set up in the activity code dictionary in p6"

Steven

Zoltan Palffy
User offline. Last seen 3 days 19 hours ago. Offline
Joined: 13 Jul 2009
Posts: 3089
Groups: None

Steven I know this 

Steven Auld
User offline. Last seen 3 weeks 1 day ago. Offline
Joined: 13 Sep 2017
Posts: 126

Marat,

No problem.

Zoltan,

Whilst Primavera will not Export Activity Codes unless they are assigned to activities, it will Import them from the XER file without having to be assigned to tasks first.

P6 Exports only the used codes, I presume, to save having to include every Global, EPS & Project code in the database to the XER Export file, however when importing from the XER file, the Activity Code Type then Activity Code Values are imported before the Activity Code allocation to the Individual Activities.

This is the same as Creating the new Codes in Primavera, but not allocating them immediately to an activity - the codes exist in the database without having to be assigned to Activities.

The unique ID numbers do not have to be the correct numbers that are assigned in your database, as Primavera allocates a new internal ID for each new code imported. These numbers are used for identifying the related values within the XER file that is being imported, then the next available ID number in your Database will be assigned to the code when it is added to your Database.

I have tried adding in Activity Code Types to an empty SQLite database starting from actv_code_type_id = 1, with a new Activity Code Type Description, then allocating this ID number to the new code to be imported (Empty Database, create new project then Export as XER. Modify the file & generate the new XER using the XER Parser).

If you were to create an empty database, new project then create a new activity code type / value & assign it to an activity to allow it to be exported - you will see that P6 does not normally start the ID Allocation from 1, but when Importing my test file with an ID of 1 it did still import correctly.

As long as the different ID numbers are the same within the XER File created in the XER Parser, then they should import OK.

If I sent you an XER file from my database, if you were to import this into your database then re-export & compare the 2 XER Files, you would see that all the ID's have changed depending on the next number available in your Database.

When I imported the XER into P6, the new codes were added to the Project Code List.

If importing as Project Codes, the correct Project ID should be added to the Activity Code Type & the Scope set to AS_Project.

When importing an XER file from another P6 Database, the Activity Code Types & Activity Codes Values need to be imported into your P6 Database before they are available for allocation to the individual activities, as otherwise there would be a data validation issue when importing the Task Activity Code List from the XER file.

Please note that this was imported into a separate stand alone Database for testing.

If the Activitiy Codes are not assigned to Activities, then you would not be able to re-export from this test database into a production database, as Primavera would not include these new Activity Codes in the new XER Export.

Not recommended for individuals that are unfamiliar with the XER file structure & the XER Parser tool, however from your previous posts I can see that you are familiar with both of these.

If you have a separate test database available you could give it a try, but I would not recommend going straight into a live database without testing first as it could corrupt the production database if any errors are made.

Having the ability to create as many separate SQLite databases is excellent for allowing testing of this, as I have a few different test codes that I use for data conversion / import to XER Files from different excel lists.

Regards,

Steven

Marat Vildanov
User offline. Last seen 6 years 21 weeks ago. Offline
Joined: 16 Oct 2017
Posts: 4
Groups: None

Thanks Steven for your help.

Now I made some codes by hand. And some of them I going to import by Project 2003 (I found some way).

Any way I will keep in mind your offer)

Steven Auld
User offline. Last seen 3 weeks 1 day ago. Offline
Joined: 13 Sep 2017
Posts: 126

Marat,

What type of Activity Code are you trying to import?

Project / EPS or Global?

I might be able to help for Project or Global Codes.

For EPS, you would need to import as Project Codes first then change to EPS.

Do you have a WBS Set Up for this, or would you want the WBS created to match the Activity Codes?

If you would like some assistance then send me a PM & I should be able to help.

Regards,

Steven

Zoltan Palffy
User offline. Last seen 3 days 19 hours ago. Offline
Joined: 13 Jul 2009
Posts: 3089
Groups: None

go into p6 create the codes and code values

make a new activity call it test

assign one of each code value to this activity  

export this activity to excel

use this as your template to import assign your code values to the activities

each code has a unique record number even if the code names are the same for different project the internal code number will be different so its best to set up a template.

Marat Vildanov
User offline. Last seen 6 years 21 weeks ago. Offline
Joined: 16 Oct 2017
Posts: 4
Groups: None

Thanks, but it gives me the mistake:

"ERROR: Code actv_code_wevu6l8oxnppzm3nks4_378_id: Invalid value 1. Cannot find in the code dictionary"

 

 
Zoltan Palffy
User offline. Last seen 3 days 19 hours ago. Offline
Joined: 13 Jul 2009
Posts: 3089
Groups: None

typically you need to already have the code name and code value set up in the activity code dictionary in p6. Then you can assign the code values to the activities in excel and then import them into P6. 

so in P6 set the codes up then export the activities and the code fields then populate the codes in excel then import back into P6.

If you are using version 8.4 or older you can use the primavera provided sdk (software development kit) along with the excel sdk template and import the activity code dictionary and valuse. Then assign the codes in to the activites in excel and import them into p6.