Clarity PPM1

Expand all | Collapse all

Read data which of .XLS Format

Jump to Best Answer
  • 1.  Read data which of .XLS Format

    Posted 28 days ago
    Hi,

    Need a Script to read data which is in .XLS Format and push data into PPM(Only .XLS Format)


    Thank You


  • 2.  RE: Read data which of .XLS Format

    Posted 28 days ago
    Have a look at this discussion as I think this will be helpful for you.
    https://community.broadcom.com/communities/community-home/digestviewer/viewthread?MID=728218#bm9fa7dd07-77e4-4d80-991a-ce30fc577422

    ------------------------------
    Abhisek Dhar
    Senior Engineer
    Clarity PPM Support
    ------------------------------



  • 3.  RE: Read data which of .XLS Format

    Posted 28 days ago
    Thank's for your reply Abhisek,

    But in that it is to copy the data from one file to another file but my requirment is to read the original XLS file


  • 4.  RE: Read data which of .XLS Format

    Posted 28 days ago

    Does this help? 

    https://community.broadcom.com/communities/community-home/digestviewer/viewthread?MID=712836



    ------------------------------
    Abhisek Dhar
    Senior Engineer
    Clarity PPM Support
    ------------------------------



  • 5.  RE: Read data which of .XLS Format

    Posted 27 days ago
    I did this a while back.  Not sure it will still work but should point you in the right direction.  I normally do this in either Java or C# as it is easier that Jelly.

    V/r,
    Gene


    <gel:script 
    xmlns:core="jelly:core" 
    xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" 
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" 
    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" 
    xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" 
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
    xmlns:sql="jelly:sql" 
    xmlns:util="jelly:util" 
    xmlns:xog="http://www.niku.com/xog" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
    
    <!--Input File Start-->
    <core:catch var = "fileExp">
    <core:new className = "java.io.File" var = "varFileInstance">
    <core:arg type = "java.lang.String" value = "c:/temp/test.xls"/>
    </core:new>
    </core:catch>
    
    <core:if test = "${fileExp!=null}">
    <gel:log>Encountered File exception: ${fileExp}</gel:log>
    </core:if>
    
    <!--Read Inputstream of the File-->
    <core:catch var = "FISExp">
    <core:new className = "java.io.FileInputStream" var = "varFileisInstance">
    <core:arg type = "java.io.File" value = "${varFileInstance}"/>
    </core:new>
    </core:catch>
    <core:if test = "${FISExp!=null}">
    <gel:log>Encountered exception in Inputstream: ${FISExp}</gel:log>
    </core:if>
    
    <!--Representation of a Input Spreadsheet workbook -->
    <core:catch var = "xssfexp">
    <core:new className = "org.apache.poi.hssf.usermodel.HSSFWorkbook" var = "varxssfwb">
    <core:arg type = "java.io.FileInputStream" value = "${varFileisInstance}"/>
    </core:new>
    </core:catch>
    <core:if test = "${xssfexp!=null}">
    <gel:log>Encountered exception in workbook: ${xssfexp}</gel:log>
    </core:if>
    
    <!--Input file read end-->
    
    
    <!--Output File Start-->
    
    
    <!--To get First row in Input file-->
    <core:invoke method = "getSheetAt" on = "${varxssfwb}" var = "varSheet">
    <core:arg type = "java.lang.Integer" value = "0"/>
    </core:invoke>
    
    
    
    
    <core:set value = "${varSheet.getPhysicalNumberOfRows()}" var = "rowCount"/>
    <gel:log>Number of Rows in Sheet: ${rowCount}</gel:log>
    
    <file:writeFile embedded="false" fileName="c:/temp/load_incidents_insert.csv" delimiter="|">
     
    <file:line>
    <file:column value="incident_no"/> 
    <file:column value="status"/> 
    <file:column value="creation_dt"/> 
    </file:line>
     
    
    
    <!-- FILE DATA LOOP Start -->
    <core:set var="j" value="1" />
    <core:while test = "${j &lt; rowCount}">
    <!--Read 'j'th row in the input file-->
    <core:invoke method = "getRow" on = "${varSheet}" var = "cur_row">
    <core:arg type = "java.lang.Integer" value = "${j}"/>
    </core:invoke>
    
    <core:if test = "${cur_row!=null}">
    
    <!--Read 1st Cell-->
    <core:invoke method = "getCell" on = "${cur_row}" var = "incident_no">
    <core:arg type = "java.lang.Integer" value = "0"/>
    </core:invoke>
    
    <!--Read 2nd Cell-->
    <core:invoke method = "getCell" on = "${cur_row}" var = "status">
    <core:arg type = "java.lang.Integer" value = "1"/>
    </core:invoke>
    
    
    <!--Read 3rd Cell-->
    <core:invoke method = "getCell" on = "${cur_row}" var = "creation_dt">
    <core:arg type = "java.lang.Integer" value = "2"/>
    </core:invoke>
    <gel:log>Creation Date: ${creation_dt.toString()}</gel:log>
    
    
    <core:set value="${incident_no.toString().trim().replaceAll('\n', ' ').replaceAll('\r', ' ').replaceAll('\x27', '').replaceAll(',', ' ')}" var="incident_no"/>
    <core:set value="${status.toString().trim().replaceAll('\n', ' ').replaceAll('\r', ' ').replaceAll('\x27', '').replaceAll(',', ' ')}" var="status"/>
    
    
    
    <!--
    <core:set value="${creation_dt.toString().trim().replaceAll('\n', ' ').replaceAll('\r', ' ').replaceAll('\x27', '').replaceAll(',', ' ')}" var="creation_dt"/>
    -->
    
    
    <core:set var="entries" value="${context.getVariables().entrySet().toArray()}" />
    <core:forEach var="entry" items="${entries}">
    		<core:if test="${!entry.getKey().equalsIgnoreCase('systemScope')}" >
    			<gel:log> ${entry.getKey()} | ${entry.getValue()} |  ${entry.getValue().getClass().getName()}</gel:log>
    		</core:if>
    </core:forEach>
    
    <core:new var="ExcelDateNumeric" className="java.lang.Integer"> <!-- Excel int for 30-Aug-2018 -->
    <core:arg type="java.lang.String" value="${creation_dt}" />
    </core:new>
    <gel:log>here</gel:log>
    <gel:log>Creation Date: ${creation_dt.toString()}</gel:log>
    
    
    
    <core:if test = "${j != 0}">
    
    
    
    <gel:log>Row_NUM: ${j}, Incident: ${incident_no}, Creation Date: ${creation_dt}</gel:log> 
    
    <file:line>
    
    <core:choose>
    <core:when test="${incident_no == null}">
    <file:column value=""/>
    </core:when>
    <core:otherwise>
    <file:column value="${incident_no}"/>
    </core:otherwise>
    </core:choose> 
    
    <core:choose>
    <core:when test="${status == null}">
    <file:column value=""/>
    </core:when>
    <core:otherwise>
    <file:column value="${status}"/>
    </core:otherwise>
    </core:choose>
    <core:choose>
    <core:when test="${creation_dt == null}">
    <file:column value=""/>
    </core:when>
    <core:otherwise>
    <file:column value="${creation_dt}"/>
    </core:otherwise>
    </core:choose>
    
    </file:line> 
    
    
    
    </core:if>
    
    
    </core:if>
    
    
    <core:set value="${j+1}" var="j"/>
    </core:while>
    <!-- FILE DATA LOOP END -->
    </file:writeFile>
    
    </gel:script>



  • 6.  RE: Read data which of .XLS Format

    Posted 27 days ago
    Thanks for your reply,

    Can I know why you use write file and filename as CSV and what is load_incidents_insert(is this Dynamic or static)


  • 7.  RE: Read data which of .XLS Format

    Posted 27 days ago
    Hello KMR,

    Examples in attachments (system RHEL, Oracle) :

    • excel_read.xml : Parse an Excel file on the server and upload it as full text in a clob column
    • excel_write.xml : Parse a frame with tabs, execute each portlet / query dynamically, export one tab Excel for one HTML tab, write all data in Excel file on server. It's trickier for graph portlets : you can upload an Excel template on server with graphs already in it and then just fill the data.

    Hope this helps.
    David

    ------------------------------
    Consultant
    CoPrime
    ------------------------------

    Attachment(s)

    xml
    excel_read.xml   6K 1 version
    xml
    excel_write.xml   22K 1 version


  • 8.  RE: Read data which of .XLS Format

    Posted 27 days ago
    This was just a sample for how to read an excel file.  I just used the cvs as a place to write the value in order to test the read.  I don't remember why I chose load_incidents_insert as the file name.

    V/r,
    Gene


  • 9.  RE: Read data which of .XLS Format

    Posted 27 days ago
    Edited by Praneeth Desur 27 days ago
    Hello!

    Below script should help you to read an xlsx file from the configured path. I had code constants custom object to configure XOG URL, etc. That may not be required in your case unless you are XOG'in data to Clarity from the file.

    Regards,


  • 10.  RE: Read data which of .XLS Format
    Best Answer

    Posted 27 days ago
    Edited by Praneeth Desur 25 days ago
    <gel:script xmlns:core="jelly:core" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql" xmlns:util="jelly:util" xmlns:xog="http://www.niku.com/xog" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <!--
    ********************************************************************************
    * Script Name:  Read XLSX File
    * Author:       Praneeth Desur
    * History:    	30-MAY-2017 	CREATED
    ********************************************************************************
    -->
    	<core:catch var="Outerexcep">
    		<!--Set Debug_flag Start-->
    		<gel:parameter default="1" var="DEBUG_FLAG"/>
    		<gel:parameter default="10" var="XOG_BATCH_SIZE"/>
    		<!--Initialize Error/Log variables -->
    		<core:set value="false" var="v_errorFlag"/>
    		<core:set value="" var="v_error_status"/>
    		<core:set value="" var="v_error_message"/>
    		<!--Instance ID is retrieved from the Process -->
    		<core:set value="${gel_processInstanceId}" var="v_processID"/>
    		<!--Set Database ID START -->
    		<gel:setDataSource dbId="niku"/>
    		<!--Set Database ID END -->
    		<!--##Initialize parameters END ##-->
    		<!--### Check DB Connection and raise exception if connection is not established START ###-->
    		<core:catch var="DbConnectionException">
    			<sql:query escapeText="true" var="TestDB"><![CDATA[
    			SELECT 1 FROM DUAL
    			]]></sql:query>
    		</core:catch>
    		<!--### Check DB Connection and raise exception if connection is not established END ###-->
    		<!--Set Error Flag if any Exception in connecting to DB START -->
    		<core:if test="${DbConnectionException != null}">
    			<gel:log category="SQL" level="WARNING">ERROR IN CONNECTING TO DATABASE.</gel:log>
    			<core:set value="true" var="v_errorFlag"/>
    			<core:set value="Failed" var="v_error_status"/>
    			<core:set value="Error in Connecting to Database" var="v_error_message"/>
    		</core:if>
    		<!--Proceed if no exceptions START -->
    		<core:if test="${v_errorFlag==false}">
    			<!--Query to get info from Code Constants Object START -->
    			<core:catch var="v_exception">
    				<sql:query escapeText="false" var="config"><![CDATA[
    
         SELECT 
         (SELECT CONFIG_VALUE FROM ODF_CA_CODE_CONSTANTS WHERE CONFIG_NAME = 'XOGURL') XOGURL,
         (SELECT CONFIG_VALUE FROM ODF_CA_CODE_CONSTANTS WHERE CONFIG_NAME = 'XOGUSER') XOGUSER,
         (SELECT CONFIG_VALUE FROM ODF_CA_CODE_CONSTANTS WHERE CONFIG_NAME = 'FILE_PATH') FILEPATH
         FROM DUAL
    
         ]]></sql:query>
    				<core:forEach items="${config.rowsByIndex}" var="configdata">
    					<core:set value="${configdata[0]}" var="v_XOGURL"/>
    					<core:set value="${configdata[1]}" var="v_XOGUSER"/>
    					<core:set value="${configdata[2]}" var="sourcePath"/>
    				</core:forEach>
    			</core:catch>
    			<!--Query to get info from Code Constants END -->
    			<!--Generate Session ID -->
    			<core:invokeStatic className="com.niku.union.config.ConfigurationManager" method="getInstance" var="config"/>
    			<core:new className="com.niku.union.security.DefaultSecurityIdentifier" var="secId"/>
    			<core:invokeStatic className="com.niku.union.security.UserSessionControllerFactory" method="getInstance" var="userSessionCtrl"/>
    			<core:set value="${userSessionCtrl.init(v_XOGUSER, secId)}" var="secId1"/>
    			<core:set value="${secId1.getSessionId()}" var="v_SESSIONID"/>
    			<!--Clarity Data Version start -->
    			<core:set value="${config.getVersionProperty('version')}" var="clarity_version"/>
    			<!--Clarity Data Version end -->
    			<core:if test="${DEBUG_FLAG != 0}">
    				<gel:log category="CONFIGURATION" level="INFO"> XOG URL :: ${v_XOGURL}</gel:log>
    				<gel:log category="CONFIGURATION" level="INFO"> XOGUSER :: ${v_XOGUSER}</gel:log>
    				<gel:log category="CONFIGURATION" level="INFO"> FILE PATH :: ${sourcePath}</gel:log>
    				<gel:log category="CONFIGURATION" level="INFO"> ${v_XOGUSER} :: ${v_SESSIONID} :: ${clarity_version}</gel:log>
    			</core:if>
    			<gel:persist scope="INSTANCE" value="${v_session_s}" var="v_session_st"/>
    			<!--Validate whether a Session ID is returned or not -->
    			<core:choose>
    				<!--Set Error Flag and Log warning if session Id is not returned START-->
    				<core:when test="${v_SESSIONID == null}">
    					<core:if test="${DEBUG_FLAG != 0}">
    						<gel:formatDate format="yyyy'-'MM'-'dd'T'HH':'mm':'ss" stringVar="v_SYSDATE"/>
    						<gel:log category="XOG" level="WARNING">${v_SYSDATE} Couldn't Log in to Clarity XOG. Check the URL and Credentials.</gel:log>
    					</core:if>
    					<core:set value="true" var="v_errorFlag"/>
    					<core:set value="Failed" var="v_error_status"/>
    					<core:set value="Couldn't Log in to Clarity. Check the XOG URL and XOG User Credentials." var="v_error_message"/>
    				</core:when>
    				<!--Set Error Flag and Log warning if session Id is not returned END-->
    				<core:otherwise>
    					<core:if test="${DEBUG_FLAG != 0}">
    						<gel:log category="XOG" level="INFO">Log in successful ${v_SESSIONID}; Processing..</gel:log>
    					</core:if>
    				</core:otherwise>
    			</core:choose>
    			<!--Proceed if Error Flag is false START -->
    			<core:if test="${v_errorFlag==false}">
    				<core:new className="java.io.File" var="varDir">
    					<core:arg type="java.lang.String" value="${sourcePath}"/>
    				</core:new>
    				<!--Set Error Message in case of Invalid Source Directory START -->
    				<core:if test="${!varDir.isDirectory()}">
    					<core:set value="Failed" var="v_error_status"/>
    					<core:set value="Invalid Source Directory ${varDir}" var="error_message"/>
    				</core:if>
    				<core:choose>
    					<core:when test="${!empty error_message}">
    						<core:set var="v_error_message">${error_message+' Please check the Source Location for a valid file.'}</core:set>
    					</core:when>
    				</core:choose>
    				<!--Set Error Message in case of Invalid Source/Destination Directory END -->
    				<core:choose>
    					<!--Check for the Valid Directory and continue the process,if valid START -->
    					<core:when test="${varDir.isDirectory()}">
    						<!--Read from Sourcepath -->
    						<gel:filelist dir="${sourcePath}" pattern=".*\.xlsx" recurse="false" var="files"/>
    						<core:choose>
    							<!--Check for file existence & Set Error Message & Log if file does not exists START-->
    							<core:when test="${files.size()==0}">
    								<core:set value="Failed" var="v_error_status"/>
    								<core:set value="true" var="v_blnkfile_error_status"/>
    								<core:set value="No file found to process in ${sourcePath}" var="v_error_message"/>
    								<core:set value="No file found to process in ${sourcePath}" var="var_file_status"/>
    								<gel:log level="WARN">No file found to process in ${sourcePath}</gel:log>
    							</core:when>
    							<!--Check for file existence & Set Error Message & Log if file does not exists END-->
    							<!--Proceed If File Exists START -->
    							<core:when test="${files.size() gt 0}">
    								<core:forEach items="${files}" var="thisFilename">
    									<core:set value="${XOG_BATCH_SIZE}" var="batchSize"/>
    									<gel:log>File to be processed:: ${thisFilename}</gel:log>
    									<core:new className="java.io.File" var="varFileTEMP">
    										<core:arg type="java.lang.String" value="${sourcePath}/${thisFilename}"/>
    									</core:new>
    									<!--Check If Mentioned File is a file START-->
    									<core:if test="${varFileTEMP.isFile()}">
    										<core:new className="java.io.FileInputStream" var="varFileisInstance">
    											<core:arg type="java.io.File" value="${varFileTEMP}"/>
    										</core:new>
    										<core:new className="org.apache.poi.xssf.usermodel.XSSFWorkbook" var="varxssfwb">
    											<core:arg type="java.io.FileInputStream" value="${varFileisInstance}"/>
    										</core:new>
    										<core:invoke method="getSheet" on="${varxssfwb}" var="varSheet">
    											<core:arg type="java.lang.String" value="Data Entry"/>
    										</core:invoke>
    										<core:set value="0" var="v_filerow_cnt"/>
    										<core:set value="false" var="countflag"/>
    										<core:set value="${varSheet.iterator()}" var="rowiterator"/>
    										<core:while test="${rowiterator.hasNext()}">
    											<core:set value="${rowiterator.next()}" var="crow"/>
    											<core:if test="${crow.getRowNum() > 0}">
    												<core:set value="${crow.cellIterator()}" var="celliterator"/>
    												<core:if test="${countflag == false}">
    													<core:set value="" var="vrdata"/>
    													<core:while test="${celliterator.hasNext()}">
    														<core:set value="${celliterator.next()}" var="ccell"/>
    														<core:if test="${ccell !=null }">
    															<core:if test="${ccell.getColumnIndex()>0}">
    																<core:set value="${vrdata}${ccell.toString()}" var="vrdata"/>
    															</core:if>
    														</core:if>
    													</core:while>
    												</core:if>
    												<core:if test="${vrdata.isEmpty()}">
    													<core:set value="true" var="countflag"/>
    												</core:if>
    												<core:if test="${!vrdata.isEmpty()}">
    													<core:set value="${v_filerow_cnt+1}" var="v_filerow_cnt"/>
    												</core:if>
    											</core:if>
    											<gel:log>v_filerow_cnt :: ${v_filerow_cnt}</gel:log>
    										</core:while>
    									</core:if>
    								</core:forEach>
    							</core:when>
    							<!--Proceed If File Exists END -->
    						</core:choose>
    					</core:when>
    				</core:choose>
    			</core:if>
    		</core:if>
    	</core:catch>
    	<!--Exception Block for the complete script END-->
    	<!--Raise Exception if any START -->
    	<core:if test="${!empty Outerexcep}">
    		<gel:log>Exception in Staging Data: ${Outerexcep}</gel:log>
    	</core:if>
    	<!--Raise Exception if any END -->
    </gel:script>


    ------------------------------
    Praneeth Desur
    Clarity PPM Specialist
    Toyota Finance Australia
    ------------------------------