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.
