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.
One Reply to “Automate the Export of Primavera P6 Projects using Python”
You must log in to post a comment.