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

[Ferreira PPM]

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.

[Deactivated User]

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.

[Aurora_Gaimon]

You can also use XML spears.

[SHUBHAM BHATIA]

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

[Legacy User]

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

[Ferreira PPM]

 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.

[SHUBHAM BHATIA]

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

[Ferreira PPM]

SHUBHAM BHATIA, Do not know where to start could provide an example?

[SHUBHAM BHATIA]

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 

[Will Lloyd]

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.

[Legacy User]

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.