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!