Category Archives: API Export and Import data

jRQL ideal suited for project migration

If you need to migrate structures, assets, pages and content from existing MS projects into any other target, I can recommend using jRQL for it, if you have Java know-how. I used it myself very successfully in the relaunch of in 2009 and my daily work.

As you maybe know a typical migration is not working in an one big export – one big import scenario. From my experience it rather goes iteratively for well structured bunches of pages in addition with manual building of a new project.

I want to share 3 possible ways of migration I used jRQL successfully in the mentioned relaunch. I translated this picture from my jRQL presentation into English.

Highest possible flexibility
For all scenarios it is true, that you have the most possible flexibility, because you can customize your migration in standard Java with all possibilities you have within. This maximum of flexibility therefore matches every need you have, which is not possible with every kind of export import tool.

Handling source data inconsistencies
Everybody has experienced or can at least imagine, that the source data are often not as consistent as needed to handle it by a program. This is especially true, if authors work on this data rather than a typically well structured administrator.

From my experience I therefore first get in touch with the source data to get an impression of the quality. I log it simply into the Eclipse console and make the program as robust as needed. You can imagine this migration as an iterative process rather than a write and run once approach. Typically I handle the data exceptions within my program (or manually in old project) until the output is acceptable. jRQL supports this iterative approach very well, because it offers high level functions instead of bothering you with the bits and bytes of raw RQL.

And even you created new pages within the new project which are not correct jRQL helps you to get rid of them and start over again. Within the jRQL plug-ins you can rely on the plug-in Reset all draft pages to make your page creation/updates not happen at all. Usually I empty my Tasks within the new project before I start the creation/update in the new project (program runs with my logon guid and session key) to make this work smoothly.

After this general aspects, let me give some more details on each of the scenarios with it’s strength and weaknesses.

Scenario 1: Copy on-the-fly
This approach is best used if only less conversion of content is needed in between. I used it for copying the press releases pages from a non-language variant project into the new project which uses language variants. The migration program therefore covers the structural differences of source and target project.

In addition I fixed several errors in the HTML code within my program, because some text editor functions are not available in the new project. A migration log collects all pages where this automatic patching did not work for manually re-work.

Scenario 2: Export, correct and import
This scenario is perfect if another person is needed to correct the data before importing them in the new project. Of course there is the flexibility to collect and prepare the data from page elements in old project to be able to write them in line by line fashion into an Excel tab file format (*.tab). jRQL offers the handy class TabFileWriter for this purpose.

Of course you can use only the second half of this scenario to create a bunch of pages from the data contained within an Excel file. I use this approach often in my dayily work with the system, because it’s much faster to enter all needed page details into an Excel sheet than using the slowly MS SmartTree or SmartEdit interfaces with it’s tons of clicks needed.

Scenario 3: Migrate assets
This scenario needs some additional explanation how it works. In my example I used it to migrate vessel certificates (2 PDFs per vessel page) into the new project. But it’s of course possible to use it for migration images.

The steps needed are as following:

  1. use jRQL for every page and download the asset (jRQL supports downloading assets from media and image elements)
  2. save them locally with the unique identifier for each page, in my case I used the unique vessel name. As a positive side effect all assets are named by a unique style, what authors only seldom do unfortunately.
  3. copy the whole folder with all assets to the MS server machine
  4. use the function Import assets on the target folder and refresh attribute values by using Update asset manager too
  5. use jRQL and get the unique page identifier back from the filename, in my case simply the vessel’s name. Find or create the target page and set the filename into the image or media elements of this page

Please have in mind, that jRQL download the assets from the RedDot ImageCache. Unfortunately this is not always up-to-date, but usually this is much more comfortable than doing it manually with several mistakes either.

Accessing different CMS servers

In the article before I explained how to access 2 projects on the same CMS server. If you work with several installations, maybe per stage (production, test, and so on), you need to access different servers at the same time.

jRQL offers in general 2 possibilities to connect: via existing session data (logon GUID, session key) and by user name and password. Both cases can be extended to connect different CMS servers.

In both cases you need to adjust the client object creation with the URL of the bridge ASP who uses jRQL for communication with the CMS.

See first re-using existing session data in the following lines of code.

String logonGuidOld="36AE9002974742C48B5F38AD889A51A2";
String sessionKeyOld="72EF63F6333B4C01AD4B568974B95424";
String projectGuidOld="06BE79A1D9F549388F06F6B649E27152";

CmsClient clientOld = new CmsClient(logonGuidOld, new URL(“”));
Project projectOld = clientOld.getProject(sessionKeyOld, projectGuidOld);

String logonGuid=”7DDFD5482B57475A9004584FC6C1F207″;
String sessionKey=”1A7CF419554244B9B737DFC3D23D13A5″;
String projectGuid=”73671509FA5C43ED8FC4171AD0298AD2″;

CmsClient client = new CmsClient(logonGuid, new URL(“http://kswfrd02/cms/hlclRemoteRql.asp”));
Project project = client.getProject(sessionKey, projectGuid);

// by old page id
Page sourcePg = projectOld.getPageById(“127290”);

// by new page id
Page targetPg = project.getPageById(“2”);

The important point here is the creation of client objects clientOld and client. You see, that they access a CMS installation on and kswfrd02. Don’t forget to copy the hlclRemoteRql.asp file into \CMS\ASP directory.

If you need to access several projects on different CMS servers by user name and password, use the style below. The only difference is the creation of the client1 and client2 objects, where the helper class PasswordAuthentication is used to hold user name and password. The rest is similar.

CmsClient client1 = new CmsClient(new PasswordAuthentication("lejafr", "pw"), new URL(""));
Project project1 = client1.getProjectByName("");

CmsClient client2 = new CmsClient(new PasswordAuthentication(“lejafradm”, “pw”), new URL(“http://kswfrd02/cms/hlclRemoteRql.asp”));
Project project2 = client2.getProjectByName(“hlag_wm2008”);

Page source1Pg = project1.getPageById(“127290”);

// by new page id
Page target2Pg = project2.getPageById(“2”);


In this example it is important to logoff both users from the CMS with the disconnect() methods. In practice I recommend to use it within a finally block.

You find the source code within the respective sample class in the jRQL download.

Accessing two projects at the same time – copy data

In this article I want to show you a way how to access two projects (on the same RedDot CMS server) at the same time. This is very useful to copy data from one project directly to an other. I used it one year ago for a relaunch of the Hapag-Lloyd’s site

Here I want to show you the way to reuse the login information while you are connected with one user per project. But it is also possible to login via user name and password.

String logonGuidOld="EF103F2989C94BCEBBC189427F9E6729";
String sessionKeyOld="6F4929F495D04A779F07587E1B294A6D";
String projectGuidOld="5256C671655D4CE696F663C73CE3E526";

CmsClient clientOld = new CmsClient(logonGuidOld);
Project projectOld = clientOld.getProject(sessionKeyOld, projectGuidOld);

String logonGuid=”D714CF042F3749DFA13298E489552468″;
String sessionKey=”C463987D1AAA4F1E9C7B394CD485B7AE”;
String projectGuid=”73671509FA5C43ED8FC4171AD0298AD2″;

CmsClient client = new CmsClient(logonGuid);
Project project = client.getProject(sessionKey, projectGuid);

Page sourcePg = projectOld.getPageById(“old page id”);

Page targetPg = project.getPageById(“new page id”);

As you can see it’s quite simple to program an access to several projects at the same time. Using the available methods for getting and setting data you can satisfy almost every migration task you receive (as long as jRQL offers it).

I used exactly this approach to directly create all of our office pages in the new project and fill all contact data from old into the new table. It takes one morning to write the program and streamline the old data before writing it again. Within some hours the program does the job. This is one of my personal success stories.

You can even migrate images and media files following these steps:

  1. programatically download the files from the old project under a new filename (if you need)
  2. manually copying all files to the server machine and import it into the new project’s folder
  3. use another jRQL program to set all new filenames in the new project’s pages

For further details on downloading images, please refer to the article about image editing.

On such a migration task you usually need to access a big amount of pages, therfore you should have a look how jRQL helps you to handle a list of pages.