Clarity

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. 

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).

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>

Hi Shubham, 

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

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.

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

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

How can I get the log ?

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.  

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.

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.

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.

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.

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.

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