Automate the Export of Primavera P6 Projects using Python

Primavera P6 and Python logos

With a switch from an old version of P6 (8.2) to a new one (18.8), I needed to export our old projects from the 8.2 database into XER files. After discovering that the number of projects in that old database was in the thousands, I knew I’d have to automate the process if I wanted to be at all productive for the next couple of weeks.

Oracle does provide a way for exporting projects using the command line. Once I discovered this, I knew there must be some way to script this for an indefinite number of projects.

Using Python’s cx_Oracle module, I could connect to the P6 Oracle Database to get the project names, and begin building the required XML file that would direct P6 what to do.

The script below receives six (6) parameters: the database username and password (what you would log onto Oracle with to look at the database), the database alias (which must be in TNSnames.ora or LDAP), an application username and password, and either a 0 (for multiple XER files) or a 1 (for one XER file for all projects).

Nota bene: The “one XER” option has not been fully tested – unless your database is small and/or you are running a 64-bit client of P6, you will likely get an out of memory error using this one.

#written by David Young on 2019/12/6
import cx_Oracle
import datetime
import fileinput
import os
import subprocess
import sys
from codecs import open

arguments = len(sys.argv)-1
if arguments < 6:
	print ("The script is called with %i arguments - 6 arguments are required. (DBUsername, DBpassword, DBalias, APPusername, APPpassword, OneXer)" % (arguments))
	sys.exit()
	
DBusername= sys.argv[1] 
DBpassword=sys.argv[2] 
DBalias=sys.argv[3] 
APPusername=sys.argv[4] 
APPpassword=sys.argv[5]
OneXer=sys.argv[6]

if OneXer != "0":
	OneXer = True
else:
	OneXer = False

"""
Url: https://gist.github.com/wassname/1393c4a57cfcbf03641dbc31886123b8
"""
import unicodedata
import string

valid_filename_chars = "-_.() %s%s" % (string.ascii_letters, string.digits)
char_limit = 255

def clean_filename(filename, whitelist=valid_filename_chars, replace=' '):
    # replace spaces
    for r in replace:
        filename = filename.replace(r,'_')
    
    # keep only valid ascii chars
    cleaned_filename = unicodedata.normalize('NFKD', filename).encode('ASCII', 'ignore').decode()
    
    # keep only whitelisted chars
    cleaned_filename = ''.join(c for c in cleaned_filename if c in whitelist)
    if len(cleaned_filename)>char_limit:
        print("Warning, filename truncated because it was over {}. Filenames may no longer be unique".format(char_limit))
    return cleaned_filename[:char_limit]  
"""
End of function
"""

def secure_log():
	if os.path.exists('myLog.txt'):
		with fileinput.FileInput('myLog.txt', inplace=True) as file:
			for line in file:			
				print(line.replace(APPpassword, len(APPpassword)*'X'), end='')

ospath = os.path.dirname(os.path.abspath(__file__))
pmpath = subprocess.check_output("WHERE PM.EXE", shell=True) 


def begin_process():
	secure_log()
	numtodo = 0
	proj_name_str_first = ''
	if os.path.exists("actions.xml"):
		os.rename("actions.xml", "actions" + datetime.datetime.now().strftime("%Y%m%d%H%M%S") + ".xml")	

	con = cx_Oracle.connect("{}/{}@{}".format(DBusername, DBpassword, DBalias))
	print (con.version)

	cursor = con.cursor()
	if OneXer:
		cursor.execute("""
			SELECT DISTINCT PROJ_SHORT_NAME FROM PROJECT 
			WHERE ORIG_PROJ_ID IS NULL			
			AND PROJ_SHORT_NAME NOT LIKE '%^%'
			AND PROJ_SHORT_NAME NOT LIKE '%&%'
			ORDER BY PROJ_SHORT_NAME
			""")
	else:
		cursor.execute("""
			SELECT DISTINCT PROJ_SHORT_NAME FROM PROJECT 
			WHERE ORIG_PROJ_ID IS NULL
			AND RISK_LEVEL IS NULL
			AND PROJ_SHORT_NAME NOT LIKE '%^%'
			AND PROJ_SHORT_NAME NOT LIKE '%&%'
			ORDER BY PROJ_SHORT_NAME
			""")
			
	f = open(ospath + '\\actions.xml','a')
	xml_file_begin = '<actions>\n'
	f.write(xml_file_begin)
	
	if OneXer:
		xml_file = '\t<action>\n\t\t<type>export</type>\n\t\t<Projects>'
		f.write(xml_file)
			
	for proj_name in cursor:	
		proj_name_actual = ''.join(proj_name)
		proj_name_str = clean_filename(proj_name_actual)
		print("Project:", proj_name_actual)
		if OneXer:
			xml_file = '\n\t\t\t<ProjectID>' + proj_name_actual + '</ProjectID>'
			f.write(xml_file)
		else:
			if not os.path.exists(ospath + '\\' + proj_name_str + '.xer'):
				if proj_name_str_first == '':
					proj_name_str_first = proj_name_str
				numtodo += 1
				xml_file = '\t<action>\n\t\t<type>export</type>\n\t\t<Projects>\n\t\t\t<ProjectID>' + proj_name_actual + '</ProjectID>\n\t\t</Projects>\n\t\t<outputFile>' + ospath + '\\'+ proj_name_str +'.xer</outputFile>\n\t</action>\n'	
				f.write(xml_file)	
			else:
				print("Skipped: Project "+ proj_name_actual + " already exported or errored.")
			
	if OneXer:
		xml_file = '\n\t\t</Projects>\n\t\t<outputFile>' + ospath + '\\OneFile.xer</outputFile>\n\t</action>\n'
		f.write(xml_file)	
	
	xml_file_end = '</actions>\n'
	f.write(xml_file_end)
	f.close()
	if not OneXer:
		print("Number of projects left to process:", numtodo)		
	os.system('PM.EXE /username=' + APPusername + ' /password=' + APPpassword + ' /actionScript=\"' + ospath + '\\actions.xml\" /logfile="' + ospath + '\\myLog.txt\"')
	con.close()
	secure_log()
	
	if numtodo > 0:
		if not os.path.exists(ospath + '\\' + proj_name_str_first + '.xer'):
			print("Errored on: ", proj_name_str_first)
			g = open(ospath + '\\' + proj_name_str_first + '.xer','w')
			g.write('Error!!')
			g.close()
			h = open(ospath + '\\ERROR - ' + proj_name_str_first + '.txt','w')
			h.write('Error!!')
			h.close()
		begin_process()		
		
begin_process()

This script does have some error handling such that if an error is encountered in the export process – which is almost guaranteed – that the process should begin again without trying to re-export projects that were already done or caused errors.

Maintaining Consistent Primavera P6 Client Configuration Files Across Multiple Users Using PowerShell – Part 2

PowerShell 5.0 icon

A refactored and updated version of the P6config.cmd script below allows for multiple profiles, both Local and Roaming. 

 

@echo off
setlocal enabledelayedexpansion
for %%A in (Local Roaming) do (
	set profpath=C:\Users\%username%\AppData\%%A\Oracle\Primavera P6\P6 Professional\18.8.0
	if exist !profpath! goto subroutine
	rem echo !profpath! does not exist.
	:return
	rem echo return
)
goto eof

:subroutine
copy "C:\Program Files\Oracle\Primavera P6\P6 Professional\18.8.0\Data\PrmBootStrapV2.xml" "!profpath!" /y 
powershell (Get-ChildItem '!profpath!\PrmBootStrapV2.xml').CreationTime = $(Get-Date) 
powershell (Get-ChildItem '!profpath!\PrmBootStrapV2.xml').LastAccessTime = $(Get-Date) 
powershell (Get-ChildItem '!profpath!\PrmBootStrapV2.xml').LastWritetime = $(Get-Date)
goto return

:eof
endlocal

Maintaining Consistent Primavera P6 Client Configuration Files Across Multiple Users Using PowerShell

PowerShell 5.0 icon

In setting up Oracle’s Primavera P6 EPPM version 18.8, I discovered that users who connected to the client via Citrix were not picking up changes that were made to the default configuration file.

Normally, if that file is changed and is newer than the user’s own file, it will be overwritten with the default. In this case, users were not seeing the newer file, and when I looked, they often had no local profile on the Citrix server.

As it turned out, the user profiles were set up as roaming profiles that were deleted upon logoff. This wouldn’t necessarily be a problem, except that the their copy of the config file was always listed as newer than the default.

A workaround was to be logged into the server via RDP, and to have the user log in via Citrix. At that time, the config file could be manually copied, overwriting the user’s old file, and then (most importantly!) manually opening and saving the user’s file, so that it would remain in place in their roaming profile even after logoff.

This is clearly not a feasible practice in a production environment. However, an automated script that does this could solve this problem! Save the following as a .cmd file and make sure it is run prior to running the P6 client executable.

copy "C:\Program Files\Oracle\Primavera P6\P6 Professional\18.8.0\Data\PrmBootStrapV2.xml" "C:\Users\%username%\AppData\Roaming\Oracle\Primavera P6\P6 Professional\18.8.0" /y

powershell (Get-ChildItem 'C:\Users\%username%\AppData\Roaming\Oracle\Primavera P6\P6 Professional\18.8.0\PrmBootStrapV2.xml').CreationTime = $(Get-Date)
powershell (Get-ChildItem 'C:\Users\%username%\AppData\Roaming\Oracle\Primavera P6\P6 Professional\18.8.0\PrmBootStrapV2.xml').LastAccessTime = $(Get-Date)
powershell (Get-ChildItem 'C:\Users\%username%\AppData\Roaming\Oracle\Primavera P6\P6 Professional\18.8.0\PrmBootStrapV2.xml').LastWritetime = $(Get-Date)

As long as the Citrix server has PowerShell installed, this should work.

Update: Part 2 has a script that will allow for both Local and Roaming profiles.

Blockchain: Where to Begin?

Blockchain symbolic representation

Blockchain is a technology that I hear about almost every day now. 

And yet I haven’t found the best place to learn about it – both its concepts and its implementation. With so many courses and books, where to begin?

Those of you who have learned about it and use it regularly – what resources did you use? What, if any, certifications are legitimate?

You may leave your thoughts in the comments, or tweet me: @deepinthecode.