Gel Script: How to Generate a CSV

[Priya12]

Friends, I need an urgent help where I am writing a gel script to generate a CSV file based on the query result.
I am trying to generate this file on my local machine .

Issue:- I am getting all the system outputs as required.File is not getting generated.
kindly help me out .

 

<gel:script
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:core="jelly:core" xmlns:email="jelly:email"
    xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
    xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary"
    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: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:formatDate format="ddMMyyyy" stringVar="today"/>

  <gel:setDataSource dbId="niku"/>
       <!-- Start writing CSV file-->
    <file:writeFile delimiter="," fileName="D:\Proceeslog\2015\Clarity_Resource" >
        <gel:log category="User-defined process" level="info" message="Start " />
  <file:line>
            <file:column value="Resource_Name"/>
            <file:column value="WWID"/>
        <gel:log category="User-defined process" level="info" message="start-column defined " />
        </file:line>
        <!--User Details-->
        <sql:query escapeText="false" var="user_query">
<![CDATA[
select Resource_Name,WWID
    from
    (select srm.full_name as Resource_Name, srm.unique_name as WWID
    from srm_resources srm
    where
    srm.IS_ACTIVE=1 --and rownum<5
) temp
]]>
                                                    
  </sql:query>
        <core:forEach items="${user_query.rowsByIndex}" trim="true" var="row">
            <file:line>
  
     <gel:log category="User-defined process" level="info" message="inside file loop " />
     <file:column value="${row[0]}"/>
     <file:column value="${row[1]}"/>
  
            </file:line>
        </core:forEach>
    </file:writeFile>
<gel:log category="User-defined process" level="info" message="End of the process " />
</gel:script>

[David Morton]

If you are running this through a Clarity process, then the "D" drive is the one on the server that the BG/process engine is running on - are all 'permissions' set OK to allow the BG to write there?

[Priya12]

Hi Dave ,

 

Thanks for showing the interest .

How can I make sure that I have all the rights?

Also we use remote desktop to connect to the clarity system.

 

Thanks

[David Morton]

Hi - I only really made my comment because you said "I am trying to generate this file on my local machine" - I was just pointing out that "D:\Proceeslog\2015\Clarity_Resource" will not be on your local machine but on the Clarity BG server.  (and so I suspected that D:\Proceeslog\2015\ would not even exist on the BG server?)

 

So either change that target directory to somewhere that both the Clarity BG server and your local machine has access to (but since I have no idea about your systems I can't even guess on how you would do that), or you need to develop a method to get the file sent from the BG server to your local machine after it has been produced.

 

--

 

There are other methods of getting flat-file extracts of Clarity data (and some very recent questions raised about such) - the simplest method (in my opinion) is to build a Clarity query and then call that query over a XOG client call from the local machine - this will result in a XML file on your local machine.

[Anon Anon]

HI,

     A few other options,

           1) if you are on 14.2, is to use the Advanced Reporting feature with PMO.  This gives you the ability to run your query and then export your data to your computer.  This avoids the hassle of dealing with the XML of a XOG read request.

           2) use the Query that David suggested and use it as the basis for a portlet (if you are not expecting too many records) or a report (if you are expecting a lot of records). You can then export the results to your computer.

           3) Use SQL Developer (or another SQL client) to connect the the Clarity DB and run your query there.

           4) Use your GEL to save the file to the BG Server and then have your GEL attach it to an email and email it to you (or someone else)

 

     It really depends on your needs and whether this needs is a one time need or if you will need to extract this data multiple times or as part of a scheduled job.

 

Gabe

[Ben Rimmasch]

The backslashes in your file name should probably be escaped.  You should probably also give the file name an extension.  Here are some corrections for a working POC.

 

<gel:script

    xmlns:core="jelly:core"

    xmlns:email="jelly:email"

    xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"

    xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary"

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

 

 

  <core:catch var="gException">

 

 

    <!--This determines the directory where all output files will be saved-->

    <gel:parameter var="outputFileBaseDir" default="C:\\clarity_output\\"/>

 

 

    <core:new className="java.util.Date" var="currDate"/>

    <core:set var="currDate">

      <gel:formatDate format="yyyy-MM-dd" dateVar="currDate"/>

    </core:set>

 

 

    <core:set var="outputFile" value="${outputFileBaseDir.concat(currDate).concat('.txt')}" />

 

 

    <gel:log category="User-defined process" level="INFO" message="Running on file ${outputFile}" />

 

 

    <gel:setDataSource dbId="niku"/>

 

 

 

 

    <!-- Start writing CSV file-->

    <file:writeFile delimiter="," fileName="${outputFile}" >

 

 

      <gel:log category="User-defined process" level="INFO" message="Writing column headers " />

      <file:line>

        <file:column value="Resource_Name"/>

        <file:column value="WWID"/>

 

 

      </file:line>

      <!--User Details-->

      <sql:query escapeText="false" var="user_query">

        <![CDATA[

        select

          full_name

          ,unique_name

        from srm_resources srm

        where

        srm.IS_ACTIVE=1

        --and rownum<5

      ]]>

 

 

      </sql:query>

      <core:forEach items="${user_query.rowsByIndex}" trim="true" var="row">

        <file:line>

 

 

          <core:forEach indexVar="c" begin="0" end="1" >

            <gel:log category="User-defined process" level="info" message="inside file loop " />

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

          </core:forEach>

 

 

        </file:line>

      </core:forEach>

    </file:writeFile>

    <gel:log category="User-defined process" level="info" message="End of the process " />

  </core:catch>

  <core:if test="${!empty(gException)}">

    <gel:log>

      ${gException}

    </gel:log>

  </core:if>

</gel:script>

[David Morton]

"C:\clarity_output\" is still relative to the server that the process is running upon though ; i.e. the BG server - not the "local" machine. ;-)

[Ben Rimmasch]

Of course it's on the server.  That's not the point.  He's said he can remote to the server so he can tell if the files are being created.  The point is that without escaping the file path the file won't be created.  His script doesn't fail because of permissions.  It fails because he didn't escape the backslashes.  The answer is to escape the backslashes.  And also, so you don't freak out the operating system he should add a file extension to the file created so the operating system can know which program to associate to the file (so it can be easily opened).