Clarity PPM

Expand all | Collapse all

Read Date Column from .xls in Gel

Jump to Best Answer
  • 1.  Read Date Column from .xls in Gel

    Posted 10-02-2018 04:20 PM

    Hi Team

     

    I am trying to read the date column from excel (.xls). But the timestamp has truncated and shows the data "03-Aug-2018".

     

    It would be great, how can read date column from excel in gel script

     

    Data from Input file:

     

    03/08/2018 03:13:21

     

    Code:

     

    <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: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>

     

     

    <core:invoke method = "getCell" on = "${cur_row}" var = "creation_dt">
    <core:arg type = "java.lang.Integer" value = "${0}"/>
    </core:invoke>

     

     

    <core:set value="${creation_dt.toString()}" var="creation_dt"/>



  • 2.  Re: Read Date Column from .xls in Gel

    Posted 10-03-2018 01:24 AM

    Hi Senthil,

     

    Did you try using getDateCellValue() method, which returns java.util.Date() when you read from excel. Here is snippet of java code to verify if the cell is date formatted and retrieves date cell value. (I did not test this for timestamp)

     

    if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
        if (DateUtil.isCellDateFormatted(cell)) {
         System.out.println("date value: "+cell.getDateCellValue());
        }
    }



  • 3.  Re: Read Date Column from .xls in Gel

    Posted 10-04-2018 03:18 AM

    Senthil, you can try getDateValue(cell) function. Additionally you can use the SimpleDateFormat class to change the dateformat of the field that you have read as you need.



  • 4.  Re: Read Date Column from .xls in Gel

    Posted 10-04-2018 02:04 PM

    In case you have issues with POI and getting the date (I have in the past with early POI Jars), you can convert the numeric value to a Java Calendar - then to a Date if you need that type.

     

    <gel:script
      xmlns:core="jelly:core"
      xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary">


         <core:new var="ExcelDateNumeric" className="java.lang.Integer"> <!-- Excel int for 30-Aug-2018 -->
              <core:arg type="java.lang.String" value="43315" />
         </core:new>

         <core:invokeStatic var="BaseDate" className="java.util.Calendar" method="getInstance" />
         <core:expr value="${BaseDate.set(1899,11,31)}" /> <!-- Excel (by default) uses the 1900 date system -->
         <gel:log>BaseDate = ${BaseDate.getTime()}</gel:log>

         <core:if test="${ExcelDateNumeric > 59}">
              <core:set var="ExcelDateNumeric" value="${ExcelDateNumeric -1}" /> <!-- Leap Year bug -->
         </core:if>

         <core:invoke var="ExcelDate" on="${BaseDate}" method="add">
              <core:arg type="java.lang.Integer" value="5" /> <!-- Calendar.DATE == 5 -->
              <core:arg type="java.lang.Integer" value="${ExcelDateNumeric}" />
         </core:invoke>

         <gel:log>BaseDate = ${BaseDate.getTime()}</gel:log>

    </gel:script>

     

     

    Not as easy as getDateCellValue but just another way,

     

    Gene



  • 5.  Re: Read Date Column from .xls in Gel

    Posted 10-07-2018 12:21 PM

    Hi Greiff

     

    I am getting an error message "Can't convert Opened to class java.lang.String at org.apache.commons.jelly.tags.core.ArgTag.conver." after implemented the below logic.

     

     

    <core:new var="ExcelDateNumeric" className="java.lang.Integer"> 
    <core:arg type="java.lang.String" value="${creation_dt}" />
    </core:new>

     

     

    Can you please advice how can I fix the problem in the code?

     

    Excel:

     

    NumberStateOpened
    INC_0190526Resolved21/09/2018 04:02:43
    INC_0190531Resolved21/09/2018 05:54:14
    INC_0190535Resolved21/09/2018 07:31:13
    INC_0190540Closed21/09/2018 07:57:37

     

    Code:

     

    <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">

    <gel:setDataSource dbId="niku"/>
    <gel:parameter default="C:\Temp" var="P_INCIDENT_TEMP_PATH"/>
    <core:set value = "${P_INCIDENT_TEMP_PATH}\test.xls" var = "SourceFile"/>


    <!--Input File Start-->

    <core:catch var = "fileExp">
    <core:new className = "java.io.File" var = "varFileInstance">
    <core:arg type = "java.lang.String" value = "${SourceFile}"/>
    </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="${P_INCIDENT_TEMP_PATH}/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: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>


    <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: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>Creation Date: ${creation_dt}</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>

     

    Thanks

    Senthil



  • 6.  Re: Read Date Column from .xls in Gel
    Best Answer

    Posted 10-08-2018 12:08 AM

    lakse01, The error you were getting was because of the header column: "Opened".

     

    I tried using getDateCellValue() as below to read date and writeen same data to a csv file.

     

        <!--Read 3rd Cell-->
        <core:invoke method="getCell" on="${cur_row}" var="creation_dt">
         <core:arg type="java.lang.Integer" value="${2}"/>
        </core:invoke>
        <core:invoke method="getDateCellValue" on="${creation_dt}" var="crea_dt"/>

     

    Output generated:

    incident_no|status|creation_dt
    INC_0190526|Resolved|Fri Sep 21 04:02:43 AEST 2018
    INC_0190531|Resolved|Fri Sep 21 05:54:14 AEST 2018
    INC_0190535|Resolved|Fri Sep 21 07:31:13 AEST 2018
    INC_0190540|Closed|Fri Sep 21 07:57:37 AEST 2018

     

    You may have to change the output date format whichever way you want, and I tried testing without the header column*.

     

    <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">
         <gel:setDataSource dbId="niku"/>
         <gel:parameter default="C:\Temp" var="P_INCIDENT_TEMP_PATH"/>
         <core:set value="${P_INCIDENT_TEMP_PATH}/test.xls" var="SourceFile"/>
         <!--Input File Start-->
         <core:catch var="fileExp">
              <core:new className="java.io.File" var="varFileInstance">
                   <core:arg type="java.lang.String" value="${SourceFile}"/>
              </core:new>
         </core:catch>
         <core:if test="${fileExp!=null}">
              <gel:log>Encountered File exception: ${fileExp}</gel:log>
         </core:if>
         <gel:log>File: ${SourceFile}</gel:log>
         <!--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:new className="org.apache.poi.hssf.usermodel.HSSFWorkbook" var="varxssfwb">
                   <core:arg type="java.io.FileInputStream" value="${varFileisInstance}"/>
         </core:new>
         <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="${P_INCIDENT_TEMP_PATH}/load_incidents_insert.csv" delimiter="|">
              <file:line>
                   <file:column value="incident_no"/>
                   <file:column value="status"/>
                   <file:column value="creation_dt"/>
              </file:line>
              <core:set value="0" var="j"/>
              <core:while test="${j &lt; rowCount}">
                   <gel:log>Value: ${j}</gel:log>
                   <!--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>
                        <core:invoke method="getDateCellValue" on="${creation_dt}" var="crea_dt"/>
                        
                        <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"/>
                        <gel:log>Creation Date: ${crea_dt}</gel:log>
                        <core:if test="${j != 0}">
                             <gel:log>Row_NUM: ${j}, Incident: ${incident_no}, Creation Date: ${crea_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="${crea_dt == null}">
                                            <file:column value=""/>
                                       </core:when>
                                       <core:otherwise>
                                            <file:column value="${crea_dt}"/>
                                       </core:otherwise>
                                  </core:choose>
                             </file:line>
                        </core:if>
                   </core:if>
                   <core:set value="${j+1}" var="j"/>
              </core:while>
         </file:writeFile>
    </gel:script>


  • 7.  Re: Read Date Column from .xls in Gel

    Posted 10-08-2018 12:46 AM

    In your script creation_dt is a HSSFCell instead of a string which is returned for incident_no and status. 

     

    If you want a java.util.Date from the HSSFCell, Pdesur calling getDateCellValue is the way to go.  Just remember getDateCellValue will return a java.util.Date and not a string.  If you need a string, use SimpleDateFormat to get it into the desired format.

     

    If you want convert it from a numeric, you would just the getNumericCellValue method on the HSSFCell object which should work for the code sample I provided.

     

    V/r,

    Gene

     



  • 8.  Re: Read Date Column from .xls in Gel

    Posted 10-08-2018 01:48 PM

    Thanks for your help and valuable time. I finally implemented the logic without any issue



  • 9.  Re: Read Date Column from .xls in Gel

    Posted 10-04-2018 02:07 PM

    Thanks Greiff. I will implement the logic in my process and provide the update