Skip to main content

P6-Loader XER Conversion

Recently, a client of ours was using P6 Pro Client with P6 Pro Cloud Connect and had the caching option enabled. This user was using an older version of P6, 18.8, and in this version, problems with P6 Pro Cloud Connect were much more common than in the current version. The user would log into P6, logging in would take a few seconds, and then P6 would just crash. We checked the user configuration and disabled caching, and the user was able to login into P6 after that.

However, the user soon noticed that some of the projects he was working on were gone. What happened?
When you use P6 Pro Cloud Connect with cache enabled, P6 saves your data in a local SQLite database; however, there are some caveats to working with this database. You can try restoring your data with the most recent cache file the user is working with, but there are no guarantees that it will work, especially in this older version. Finally, you can copy the file to a different location in your file system, and with a tool like DB Browser, you can make the required schema changes in the database that will let the user login to the database as if it were a local SQLite database - fix some preferences here, reset a password there. This is what we did with the customer’s SQLite database file.

Once the fixes were in place, I e-mailed the file to the customer along with instructions on how to set it up, and the customer was able to login and export the XER files he needed. However, when importing these XER files into the target database, nothing would happen - P6 would just immediately stop importing the XER file. We troubleshot the issue across different versions of P6, and the result was always the same.

It turns out that the cache’s SQLite database has no referential integrity whatsoever! You can have resource assignments that don’t have a corresponding activity record, among several other problems. I tried fixing a few of those problems manually but to no avail. The end result was always the same: exporting to XER would create a corrupted XER file that no version of P6 could import.

Emerald’s P6-Loader has XER conversion functionality. You can either clean up XER files (shown on the right of the screenshot), or you can just convert it into an Excel file that the P6-Loader can import.

Cleaning up the files with this tool wouldn’t do the trick, as the XER files had far too many problems; however, converting the XER file to the P6-Loader format worked. The P6-Loader uses the Cloud API  to load XER and XML files, and it can convert those to Excel files that can then be loaded with the P6-Loader.

The conversion was successful. As expected, there were a few warnings about some data not being found, but the philosophy we follow for these conversions is to do as much as possible and then warn the user about the problems we encountered. We got a working Excel file, and we did some cleanup steps in Excel:
  • Assigned an EPS node in the Project sheet
  • Fixed some data columns in the Resource Assignments sheet related to a mismatch between the activity status and the value in the field
  • And that’s it.
After these changes, I imported this file into a blank Oracle database. The P6-Loader imported the file with no errors. Even if there were errors, I could have just fixed the problems and then imported again; the P6-Loader will just update a record’s data if it finds it in the database already. After this Excel file was imported, I generated a clean, uncorrupted XER file for our client.

Before shipping the file, I tested it. I was able to import the file, and so was our client. Another client saved with the P6-Loader!
No video selected.

About the Author

Ravi Wallau - Integration Specialist

Ravi started working with computers as a child, programming with BASIC language on his MSX, a platform once popular outside of North America. In 1998, Ravi started an internship where he further developed his programming skills. After one year, this internship became his first job, where he gained experience developing industrial automation systems.

Over time, Ravi has worked in many different industries and programming languages, developing banking, integration and mobile solutions in C/C++, Java, Visual Basic and C # for a variety of platforms.

From 2007 to 2011, Ravi moved from Brazil, his native country, to Canada with the desire to live and learn another culture, as well as gain experience working in diverse environments.

Since 2009, Ravi has been an important part of the team at Emerald Associates. He has participated in key integration projects with major clients, such as CL&P, Suncor and Nexen, acting as the key technical lead in all phases of the projects. Ravi is also a key contributor at Emerald Associates in the development of unique in-house products such as TAPS, EP-datawarehouse, CAPPS, P6-Auditor and P6-Loader.

Ravi is a dedicated and insightful developer, one who prides himself on the quality of his work. He truly strives to provide optimal functionality so that each piece of a complex integration system fits together to give the end-user what they really need and more.

Leave a comment

Please login to leave a comment.