Example of a gel script used to read an excel file

[Anon Anon]

This might have already been discussed:
is there an example of a gel script to read an excel file?

Sankhadeep

[Legacy User]

try this

<gel:script


xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"


xmlns:cats="jelly:com.clarity.CATS.customtag.CATSTagLibrary"


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:soapenv="http://schemas.xmlsoap.org/soap/envelope/"


xmlns:sql="jelly:sql"


xmlns:u="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">

<!-- *************************************************************
-->

<!-- Program: Update Resource Vendor Relationships in Oracle

-->

<!-- This reads the records from clarity and puts them as a CSV file in the specified folder-->
<!-- Modification history: Version 1






-->

<!-- Dependencies: NONE YET







-->

<!-- ************************************************************
-->
<gel:setDataSource dbId="OracleFinancial"/>


<core:set value="0" var="totalAdds"/>
<core:set value="0" var="totalupdates"/>
<core:catch var="filerr">
<file:readFile commentIndicator="#" delimiter="," embedded="false" fileName="${filePath}\${run_date}${fileName}" var="input"/>
</core:catch>

<core:choose>
<core:when test="${filerr != null}">
<gel:log level="ERROR">Error: ${fileerr}. Check input File. "${filePath}\${run_date}${fileName}"</gel:log>
</core:when>
<core:otherwise>

<core:forEach
begin="0"
indexVar="i"
items="${input.rows}"
step="1" var="row">



<core:set
value="${row[0]}" var="firstname"/>
<core:set
value="${row[1]}" var="lastname"/>
<core:set
value="${row[2]}" var="username"/>
<core:set
value="${row[3]}" var="vendorcode"/>
<!--<core:set
value="${row[4]}" var="vendorname"/> -->









<core:set
value="${row[4]}" var="resid"/>









<core:set
value="${row[5]}" var="vendid"/>

[Legacy User]

[color=#121E9C][size=5][font=Arial]Does this work with an xls file??
[font][size][color]

[Anon Anon]

^ Indeed! What exactly do we mean by "excel" file?

A real .xls file is some cryptic Microsoft specific file format isn't it? Might be easier with one of the XML based xls files?

But I suspect that REALLY what we mean is a simple flat text comma-seperated variable format file (.csv) - in which case the script above will work!

--

(Of course if its really some nasty complex xls file; then you can go down the "VB calling a webservice / XOG" route, but I am starting to over-engineer the solution a little bit now?)

[Anon Anon]

Thanks Dave. I was able to convince the business to use a csv file instead of a xls file.

[Adersh Ram]

Hi Dave,

 

Using Clarity 14.2 (cloud environment).

 

I have seen lot of threads discussing on this topics.

 

I have a customer requirement for reading excel data from a ftp location and xog the OBS into Clarity. As per my understanding we cannot read excel file data directly from GEL script, we can only read text data. Just wanted to know is there any best method to achieve this from GEL script.

 

Please share your thoughts.

 

Thanks,

Ram

[David Morton]

Well there are many ways to solve what I think your problem is, however "reading an Excel file from GEL" probably isn't the best one for all the reasons above.

 

Why are you fixated on GEL - what has GEL got to do with the business problem you are trying to solve? Take a step back from the solution that you think you have started with (i.e. GEL) and look at your real problem ; "putting data into Clarity from an Excel workbook" - you can do that in a number of different ways (Java, VBA, *anything* that can read the excel file and call a web-service). There are also commercial products on the market that might make all this relatively simple for you (ITROI's stuff springs to mind).

[Adersh Ram]

Thanks for your suggestion Dave.

 

Yes, I am thinking alternative way to do this.

 

Thanks,

Ram