How to use Microsoft Excel to Map Data into XOG Files, then Update Directly to CA PPM.

Posted @ 07/17/2015  07:05 PM By Federico Pena


The power of Microsoft Excel is positively dynamic when working with XOG conversions for CA PPM. In case you haven’t heard, IT-ROI continually tries to make PPM simpler, whether it’s small scale to help out worker bees like us or large scale enterprise solutions that we prvide. Everyone wants to save time in their work day, so here is a way to save some of that precious time via Microsoft Excel and IT-ROI’s free-to-use XOG client.

Check out the video tutorial and the 10 steps on how to generate and build your XML files to be able to XOG them directly into CA PPM. 


 Can't view YouTube videos?

   Watch it Here

10 steps on how to automatically generate XML files via Microsoft Excel and XOG them directly into CA PPM

(from the tutorial video):

1)      Ensure that your tool bar in Excel has the Developer Tab enabled. If not, follow these steps to enabled it:

  1.        In Microsoft Excel > File > Options > Customize Ribbon (in the right menu).
  2.        Now make sure that the Developer Tag is checked, then click OK.

2)      Now that you have the Developer Tab enabled and selected, you need to select the XML file you want to work with. To do this, click on ‘Source’ in the Developer Tab. This will ask for an XML file. It is critical that you have at least two tags in the XML file, if you don’t, Excel won’t consume and display it correctly.  (XML example shown in the tutorial video). 

3)      Click on ‘XML Maps’ located on the right side at the bottom in the XML Source box. In the subsequent pop-up box, click on ‘Add’. Now navigate to the XML file you want to use, and click ‘OK’. This will build the XML schema for you on the right hand side.

4)      Now we decide what parts of the XML file we want to Map.  Don’t map any of the header because that stays static. You want to Map everything that is inside of the Project tag (as seen in the tutorial).

5)      Drag & drop all the Project data into cell A1 (notice that this will put all the data in that column).

6)      Then right click on any cell in the newly formatted column, then go to XML > Refresh XML Data > click ‘OK’ (this brings in the data that is in the XML file).

7)      Now you can create any new records that you want for CA PPM swiftly, using Excel’s fantastic click & drag and drag & drop functionality.  

8)      When your updates are complete, and you’re ready to generate your new XML file, go back to the Developer Tab and click on ‘Export”. Name your new file and save it where you please.

9)      To verify your work, you can go back to the new file and open it in Notepad or Notepad++ and you’ll see that it generated a new line for each project (in reference to the tutorial video) that was created.

10)     Once the XML files are created, you can copy them and paste them into our free-to-use XOG client (XQ) to XOG them directly into CA PPM. 

Congratulations! What lovely XOG files you just created.

CA PPM ask an expert panel

Return Top

Popular tags: XOG & Query Bridge, CA PPM Tips & Tricks