GEL to output a txt file

[Anon Anon]

Hi everyone,

 

has anyone on here ever used GEL to output a txt file?

I am looking to create a process that reads from the database (simple SQL read) and puts the results in a fixed length text file.

 

i'd be really grateful to see any examples of code that people may have to do this sort of thing.

 

We are on Clarity 12.1.3 on Oracle

 

Thanks in advance,

Matt

[Anon Anon]

You have a couple of options - there are a few discussions on these boards of these I think, but..

 

• gel:serialize will dump entire XML 'documents' to file-system files in a single command
• for more procedural (open file, write line, close file) type processing then there is the jelly file methods ( Jelly - Tag Documentation )
• and the writeFile / line methods
• for simple debug gel:out is another way.

 

A thread with some code examples is https://communities.ca.com/message/101472636#101472636 to get you started, then just search around the boards for other threads containing similar syntax?

[Anon Anon]

Hi Dave,

 

thank you for this.

We certainly don't want an XML out put here - just a fixed length text file with a filename derived at runtime containing a line for each row of a query.

So I'm guessing your 2nd or 3rd options would be the ones here.

 

We WILL use gel:out to write some informative messages for the log but our main required output is the .txt file

 

I'll go through your links and examples and hopefully come up with something that works.

 

Regards,

Matt

[Eugene Greiff]

If you need to write a fix formatted file (each column has a fixed width) you might want to use org.apache.commons.lang.StringUtils.

 

<?xml version="1.0" encoding="utf-8"?>
<gel:script
    xmlns:core="jelly:core"
    xmlns:xog="http://www.niku.com/xog"
    xmlns:x="jelly:org.apache.commons.jelly.tags.xml.XMLTagLibrary"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:sql="jelly:sql"
    xmlns:util="jelly:util"
    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:nikuq="http://www.niku.com/xog/Query"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <gel:parameter var="username" />
    <gel:parameter var="password" />
    <core:set var="username">admin</core:set>
    <core:set var="password">password</core:set>

    <!-- Database Source -->
    <gel:setDataSource dbId="niku"/>
    <sql:query var="queryResults" escapeText="false">
        <![CDATA[    
select pp.prid, iv.code, iv.name, pp.prstart, pp.prfinish
from prj_projects pp
inner join inv_investments iv on iv.id = pp.prid
where ROWNUM <= 10
]]>
    </sql:query>

<!-- Set our fixed format for each field -->
    <core:set var="fieldsSize" value="12,15,60,22,22"/>
    <core:set var="fieldsSizeArray" value='${fieldsSize.split(",")}' />

<!-- Write out our header record -->
    <core:set var="rowValue" value=""/>
    <core:forEach var="columnName" items="${queryResults.getColumnNames()}" indexVar="i">
        <core:invokeStatic className="java.lang.Integer" method="parseInt" var="intId">
            <core:arg value="${fieldsSizeArray[i]}" />
        </core:invokeStatic>
        <core:invokeStatic className="org.apache.commons.lang.StringUtils" method="center" var="paddedValue">
            <core:arg type="java.lang.String" value="${columnName}" />
            <core:arg type="int" value="${intId}" />
        </core:invokeStatic>
        <core:set var="rowValue" value="${rowValue + paddedValue}" />
    </core:forEach>
    <gel:out>${rowValue}</gel:out>

<!-- Write out each record into our fixed format -->
    <core:forEach trim="true" items="${queryResults.rowsByIndex}" var="row">
        <core:set var="rowValue" value=""/>
        <core:forEach var="columnName" items="${queryResults.getColumnNames()}" indexVar="i">
            <core:invokeStatic className="java.lang.Integer" method="parseInt" var="intId">
                <core:arg value="${fieldsSizeArray[i]}" />
            </core:invokeStatic>
            <core:invokeStatic className="org.apache.commons.lang.StringUtils" method="leftPad" var="paddedValue">
                <core:arg type="java.lang.String" value="${row[i].toString()}" />
                <core:arg type="int" value="${intId}" />
            </core:invokeStatic>
            <core:invokeStatic className="org.apache.commons.lang.StringUtils" method="right" var="fieldValue">
                <core:arg type="java.lang.String" value="${paddedValue}" />
                <core:arg type="int" value="${intId}" />
            </core:invokeStatic>
            <core:set var="rowValue" value="${rowValue}${fieldValue}" />
        </core:forEach>
        <gel:out>${rowValue}</gel:out>
    </core:forEach>
</gel:script>

 

Fixed format lines (for giggles I centered the header fields and right justified the data columns):

 

 

V/r,

Gene

[Anon Anon]

Thanks Gene,

 

that looks like just the job.

i'm going to ask what is probably a very stupid question, but to use org.apache.commons.lang.StringUtils is it simply a case of invoking it just like you have done - or does something actually need to be physically installed on the server.

As you can probably guess - i dont do a lot of GEL and do not have a background in programming

Thanks in advance,

Matt

[Eugene Greiff]

I ran this on 13.3 system and inside the lib directory there is commons-lang.jar which contains the methods that I used.

 

Just change the gel:out to gel:log and run it via a process to see if it works for you inside your instance.

 

V/r,

Gene

[Anon Anon]

one further stupid question, so I can progress as 'informed' as possible, is the commons-lang.jar file a generally available file or something you would have customised for your particular GEL requirements?

I guess i'm just wondering if I can get hold of the latest version of that file floating out there in the internet and chuck it on the server.

 

best regards,

Matt

[Eugene Greiff]

In my 13.3 system, it is supplied via the installation of Clarity.  We are working in OnDemand and it is available on that system also.

 

V/r,

Gene

[Doug Kalkowski]

I have used the <file:writeFile> tags to do a delimited file output in the past and it worked well.  this is an example of  a || delimited file.

you do a standard select query and then cycle through the results.  This output puts a header row on the text file, then displays the results.

 

 

<file:writeFile fileName="D:\Share\Survey\file${gel_objectInstanceId}.txt" delimiter="||" embedded="false">

<file:comment value="Project ID || First Name || Last Name || ..... || Additional Comments"/>

   <core:forEach items="${results.rowsByIndex}" var="row">

      <core:forEach var="columnName" items="${results.columnNames}" indexVar="i" step ="37">

  <file:line>

  <file:column value="${row[0]}"/>

  <file:column value="${row[1]}"/>

  <file:column value="${row[2]}"/>

  <file:column value="${row[3]}"/>

  .

  .

  .

  .

   <file:column value="${row[36]}"/>

         </file:line>

</core:forEach>

     </core:forEach>

</file:writeFile>