Clarity

Expand all | Collapse all

XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.

Jump to Best Answer
  • 1.  XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.

    Posted 01-23-2018 06:23 AM

    Dear Friends,

     

    How to perform data loading through a spreadsheet for the CA PPM tool. I know I need to transform into XML. But I have difficulties to change the structure. How to proceed in this case to correctly use XOG?

     

     

    Warmest Regards,
    Douglas Ferreira.


  • 2.  Re: XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.

    Posted 01-23-2018 08:21 AM

    You don't say what kind of data you have.

    See

    XML Source Missing Fields in Excel 

    and the referenced threads.

    If it is simple non hierarchical you can do as  Federiko show on the video.

    If it is not you can still do it either eg. with having data on one tab and formula on another and combining them to a third tab.



  • 3.  Re: XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.

    Posted 01-23-2018 09:39 AM

    You can also use XML spears.



  • 4.  Re: XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.

    Posted 01-24-2018 01:24 AM

    Hi DouglasFerreiradaSilva82305948,

     

    Mail Merge is the good way to create the XOG from spreadsheet. You can create a XOG template and using mail merge create the final XOG by picking data from the spreadsheet.

     

    The other option is reading excel file from GEL and loads into clarity tool directly by creating a process.

    Depends on the data for loading you can choose the option.

     

    Regards

    /Shubham



  • 5.  Re: XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.

    Posted 01-24-2018 07:51 AM

     I agree with Shubham, we can use mail- merger of MS word. Also, we can use excel to XML conversion of MS Excel.



  • 6.  Re: XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.

    Posted 01-24-2018 08:07 AM

     Unfortunately I could not convert the worksheet to all columns. I'm selecting the columns gradually, then changed manually in the write.xml file. That way I'm getting success. As they said above in a hierarchical way is still not possible.



  • 7.  Re: XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.
    Best Answer

    Posted 01-24-2018 08:22 AM

    Yes agree with you.

    Then you can follow the second approach which is- reading excel file from GEL and loads into clarity tool directly by creating a process.

     

    /Shubham



  • 8.  Re: XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.

    Posted 01-24-2018 08:46 AM
    SHUBHAM BHATIA, Do not know where to start could provide an example?


  • 9.  Re: XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.

    Posted 01-24-2018 10:58 AM

    You can start with the below GEL which reads the XSLX file:

     

    <gel:script xmlns:core="jelly:core"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:util="jelly:util">

    <!-- ************************************************************************************** -->
    <!-- Program: GEL Script for reading XLSX excel file -->
    <!-- Action: -->
    <!-- Author: Shubham Bhatia -->
    <!-- Version: 1.0.0 -->
    <!-- Dependencies: Yes, Dependent on the parameter file Location and File Name -->
    <!-- ************************************************************************************ -->

    <core:catch var="SuperException">

    <gel:parameter default="" var="fileLocationOnServer"/> <!-- example: (G:\Apps\Clarity\Clarity)-->
    <gel:parameter default="" var="fileName"/><!-- example: (test.xlsx)-->

    <core:set var="fullFilePath"><![CDATA[blank]]></core:set>

    <gel:log> File Location (${fileLocationOnServer}) </gel:log>
    <gel:log> File Name (${fileName}) </gel:log>
    <core:set var="fullFilePath"><![CDATA[${fileLocationOnServer}/${fileName}]]></core:set>

    <core:new className="java.io.File" var="ExcelFileToRead">
    <core:arg value="${fullFilePath}"/>
    </core:new>

    <gel:log> File Size ${ExcelFileToRead.length()} </gel:log>
    <core:new className="java.io.FileInputStream" var="fileInput">
    <core:arg type="java.io.File" value="${ExcelFileToRead}"/>
    </core:new>

    <gel:log> Return first sheet from the XLSX workbook </gel:log>
    <core:new className="org.apache.poi.xssf.usermodel.XSSFWorkbook" var="myWorkBook">
    <core:arg type="java.io.FileInputStream" value="${fileInput}"/>
    </core:new>

    <gel:log> Get iterator to all the rows in current sheet </gel:log>
    <core:invoke method="getSheetAt" on="${myWorkBook}" var="mySheet">
    <core:arg value="${0}"/>
    </core:invoke>

    <gel:log> Traversing over each row of XLSX file </gel:log>
    <core:invoke method="iterator" on="${mySheet}" var="rowIterator"/>
    <gel:log>____________________________________________________________</gel:log>
    <core:while test="${rowIterator.hasNext()}">

    <gel:log> For each row, iterate through each columns </gel:log>
    <core:invoke method="next" on="${rowIterator}" var="row"/>
    <core:invoke method="cellIterator" on="${row}" var="cellIterator"/>

    <core:while test="${cellIterator.hasNext()}">
    <core:invoke method="next" on="${cellIterator}" var="cell"/>
    <core:switch on="${cell.getCellType()}">
    <core:case value="${1}">
    <gel:log>${cell.getStringCellValue()} </gel:log>

    </core:case>
    <core:case value="${0}">
    <gel:log>${cell.getNumericCellValue()} </gel:log>

    </core:case>
    <core:case value="Cell.CELL_TYPE_BOOLEAN">
    <gel:log>${cell.getBooleanCellValue()} </gel:log>

    </core:case>
    <core:default/>
    </core:switch>
    </core:while>
    </core:while>

    </core:catch>

    <core:if test="${SuperException!=null}">
    <gel:log>Super Exception -- ${SuperException}</gel:log>
    </core:if>

    </gel:script>

     

    Let me know if it works for you, else we can find other way or try to modify the GEL.

     

    /Shubham 



  • 10.  Re: XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.

    Posted 01-25-2018 01:14 AM

    I found that if I bring the XML file read from PPM directly into Excel, the schema created is much more complex and complicated to comfortably edit. Therefore, I usually manually edit the XML file to include only records that will appear nicely as a table; for example with the matricies, include only the <matrixrows> records. I then reapply the XML wrappers that should appear before and after the records to be inserted or updated, copying the content from the sample XML _write files from the xog/xml directory in the Program Files directory.

    Every XML file is laid out differently, so experimentation with each is necessary.



  • 11.  Re: XOG--> How to perform data loading through a spreadsheet for the CA PPM tool.

    Posted 01-25-2018 08:00 AM

    have you tried the data loader in https://xogbridge.itroisolutions.com/   

    it has a GUI for mapping Excel files to XOG files.  it comes with templates for most objects as well.