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.

Exporting All global codes from P6 to Excel

8 replies [Last post]
Luke Barford
User offline. Last seen 4 years 10 weeks ago. Offline
Joined: 19 May 2016
Posts: 8
Groups: None

Hi ,

Is there a way of exporting all global codes from P6 to excel or similar? 

I need to review all codes in system for a clear out.

I have run a report in p6, it does give me what i need but will not in the fomrat i need .

Any body have any experieince of using Gateway for this?is it possible?

Replies

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

here is another sql query to run to see what codes are not being used

SELECT PCATVAL.proj_catg_id AS CodeValue_id, PCATVAL.proj_catg_short_name AS Project_Code_Value_Name,PCATTYPE.proj_catg_type_id AS Code_id, PCATTYPE.proj_catg_type AS Project_Code_Name
FROM PCATVAL JOIN PCATTYPE ON ( PCATVAL.proj_catg_type_id =PCATTYPE.proj_catg_type_id)
WHERE PCATVAL.proj_catg_id NOT IN (SELECT DISTINCT PROJPCAT.proj_catg_id FROM PROJPCAT);

Joel Roberts
User offline. Last seen 10 weeks 6 days ago. Offline
Joined: 17 Mar 2017
Posts: 37
Groups: None

Hi Luke,

Glad to see you worked it out. Hope the help from the fellow forum members helped. I just wanted to comment on and let you know of two apps that might help your process after the export. 

  • ScheduleReader - can help you preview the XER (or XML, XLS) file and easily see which cata is contained within. 
  • ScheduleCleaner - can help you remove (clean) categories of project data. It also has a convert Global to Project data option for calendars and activity codes.

Both apps offer a 15-day free trial period.

Regards

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

then just run the 1st part each part explains what it is doing

Luke Barford
User offline. Last seen 4 years 10 weeks ago. Offline
Joined: 19 May 2016
Posts: 8
Groups: None

Thanks all,

 

I have found in P6 EPPm  Under the Administration>Enterprise Data there is an Import/Export Enterprise Data

This gives a deatioled report into excel for everything.

 

 

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

Zoltan,

How does that SQL Code handle Activity Codes that are held in a hierarchy style list?

If a parent code value is not directly assigned then it will not appear in the TASKACTV table & the SQL script will then delete the parent even if the child codes are used in some projects. 

Would you not also need to check that it does not appear in the parent_actv_code_id field of the ACTVCODE table?

Regards,

Steve

Santosh Bhat
User offline. Last seen 51 weeks 1 day ago. Offline
Joined: 15 Apr 2005
Posts: 381

__Duplicate Post__

Santosh Bhat
User offline. Last seen 51 weeks 1 day ago. Offline
Joined: 15 Apr 2005
Posts: 381

You can click on the activity code value list, press CTRL+A to seelct all, then copy the full list from P6 and paste them into excel. But you'll need to do this one code at a time.

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

1st from the report group I would run report CV03 Activity Code Value

then is that is not what you want you can see of the global codes but do not know which project is using any of them 

However there is a sql script to run to delte unused global activity codes

 Log into your P6 database as privileged user (ex: privuser):

b. Run below script

Please take a backup of the database before running delete scripts.

 

--------------------------------------------------------------GLOBAL ACTIVITY CODES--------------------------------
-Before deleting unused global actvitiy child code
SELECT count(*)
FROM actvcode
WHERE actv_code_type_id IN
  (SELECT actv_code_type_id FROM actvtype WHERE actv_code_type_scope='AS_Global'
  )
AND actv_code_id NOT IN
  (SELECT actv_code_id FROM taskactv
  );
  /

--Delete the unused global actvitiy child code
DELETE
FROM actvcode
WHERE actv_code_type_id IN
  (SELECT actv_code_type_id FROM actvtype WHERE actv_code_type_scope='AS_Global'
  )
AND actv_code_id NOT IN
  (SELECT actv_code_id FROM taskactv
  );
  /
 
  commit;
  /
 
--Display the count of unused global actvitiy child code after deletion
SELECT count(*)
FROM actvcode
WHERE actv_code_type_id IN
  (SELECT actv_code_type_id FROM actvtype WHERE actv_code_type_scope='AS_Global'
  )
AND actv_code_id NOT IN
  (SELECT actv_code_id FROM taskactv
  );
  /