Clarity PPM

Expand all | Collapse all

GEL: Read email address from excel and update in the table

  • 1.  GEL: Read email address from excel and update in the table

    Posted 11-26-2018 12:50 PM

    Is there a way that I can read the email address from the excel for the list of resources and update it in the resource table via GEL Script ? It will be really helpful if anything is available immediately. 



  • 2.  Re: GEL: Read email address from excel and update in the table

    Posted 11-27-2018 04:44 AM

    Yes this is possible but not sure that you will get anyone to provide a ready-made solution for you.

    You can search on this community to find discussions on reading XLS files via GEL and also regarding updates to the application from GEL (i.e. via XOG called by GEL)

     

    You should consider whether your design is the right solution though - for example you can write VBA in Excel to call XOG, that might be easier to build (but would probably involve some manual intervention).



  • 3.  Re: GEL: Read email address from excel and update in the table

    Posted 11-28-2018 05:47 AM

    Hi ArunAngusamy27 ,

     

    You can use the below GEL scripts which is fetching email from the excel file and further you can add the resource profile update XOG scripts.

     

    <?xml version="1.0" encoding="UTF-8"?>
    <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">

    <core:catch var="SuperException">
    <gel:log> File Location </gel:log>
    <core:new className="java.io.File" var="myFile">
    <!--chnage your file path -->
    <core:arg value="C:\Users\test\Desktop\test.xlsx"/>
    </core:new>
    <gel:log> File Size ${myFile.length()} </gel:log>
    <core:new className="java.io.FileInputStream" var="fis">
    <core:arg type="java.io.File" value="${myFile}"/>
    </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="${fis}"/>
    </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()}">
    <gel:log>Get Cell Type ${cell.getCellType()} </gel:log>
    <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: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>



  • 4.  Re: GEL: Read email address from excel and update in the table

    Posted 12-12-2018 01:00 PM

    Hi Shubham, 

     

    When I execute the above code, it goes exception without reading the file 

     



  • 5.  Re: GEL: Read email address from excel and update in the table

    Posted 12-16-2018 07:49 PM

    Just a guess, as I would never had stored the XLS file in the XOG folder.  You are trying to read from a folder "CA Clarity XOG", which DOS may not like the spaces in the folder name.  Look at the 'File Size' shown in you log file, it is shown as ZERO for the file size, so either it is truly ZERO, or, the GEL script didn't locate it.



  • 6.  Re: GEL: Read email address from excel and update in the table

    Posted 12-16-2018 07:54 PM

    I tried placing the files in different folder but it is still not reading the file from the folder.



  • 7.  Re: GEL: Read email address from excel and update in the table

    Posted 12-16-2018 07:59 PM

    Can you please consider posting your logfile for your last run?



  • 8.  Re: GEL: Read email address from excel and update in the table

    Posted 12-16-2018 08:10 PM

    How can I get the log ?



  • 9.  Re: GEL: Read email address from excel and update in the table

    Posted 12-16-2018 08:23 PM

    You posted a screen shot of it on 12-Dec-2018.  

     

    Also, taking a stab, are you running the GEL script from PPM?  Given you screen shot on 12-Dec-2018, I think the answer is Yes.  If so, can you also confirm where the XLSX file is, my guess, it will be your local PC.  



  • 10.  Re: GEL: Read email address from excel and update in the table

    Posted 12-16-2018 08:26 PM

    I am running it from PPM and hence I have incorporated the Log snapshot. I have initially placed the file under "Desktop" and try running the GEL Script through PPM.



  • 11.  Re: GEL: Read email address from excel and update in the table

    Posted 12-16-2018 08:39 PM

    The GEL script is running on your PPM server.  You have placed the excel document on your local PC.  It is not surprising that the GEL script can't read the file, it won't be there.

     

    You will need to contact CA Support for FTP access to your PPM server, to that you can upload the file, and then modify your GEL script to point to it's location (CA Support should also be able to provide the correct path).

     

    Alternative, run the GEL script from your local desktop.  There are other Forum Topics raised last week on how to achieve this.



  • 12.  Re: GEL: Read email address from excel and update in the table

    Posted 12-11-2018 08:24 PM

    Are you SaaS with SSO enabled?  If so, you will also need to update the 'user name' on the Resource, as well as the user record stored in CA Portal.  And, prior to you asking, there is no GEL script to undertake this.



  • 13.  Re: GEL: Read email address from excel and update in the table

    Posted 12-12-2018 08:49 AM

    Yes I am on SaaS with SSO Enabled. I am not talking about the user name. I am discussing about the email address and SMS_Email out of the box fields.



  • 14.  Re: GEL: Read email address from excel and update in the table

    Posted 12-16-2018 07:52 PM

    I don't know your full functional requirements in why you are changing these email addresses.

     

    I can only comment that when you are SaaS and SSO enabled, the email address is used as the 'user name', as well as it is stored in ondemand.ca.com.



  • 15.  Re: GEL: Read email address from excel and update in the table

    Posted 12-16-2018 07:53 PM

    I tried putting the files in different folder but it is not reading the files