Clarity

Expand all | Collapse all

Read data which of .XLS Format

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

    Posted 11-11-2019 09:25 AM
    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 11-11-2019 09:29 AM
    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 11-11-2019 09:37 AM
    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 11-11-2019 10:56 AM

    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 11-12-2019 06:21 AM
    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 11-12-2019 06:59 AM
    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 11-12-2019 07:59 AM
    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 11-12-2019 10:12 AM
    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 11-12-2019 05:45 PM
    Edited by Praneeth Desur 11-12-2019 05:48 PM
    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 11-12-2019 05:47 PM
    Edited by Praneeth Desur 11-14-2019 05:56 PM
    <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
    ------------------------------