Clarity PPM

Expand all | Collapse all

Gel Script: How to Generate a CSV

  • 1.  Gel Script: How to Generate a CSV

    Posted 06-11-2015 06:51 AM

    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>



  • 2.  Re: Gel Script: How to Generate a CSV

    Posted 06-11-2015 07:03 AM

    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?



  • 3.  Re: Gel Script: How to Generate a CSV

    Posted 06-11-2015 07:26 AM

    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



  • 4.  Re: Gel Script: How to Generate a CSV

    Posted 06-11-2015 07:41 AM

    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.



  • 5.  Re: Gel Script: How to Generate a CSV

    Posted 06-15-2015 03:45 PM

    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



  • 6.  Re: Gel Script: How to Generate a CSV

    Posted 07-07-2015 03:34 PM

    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>



  • 7.  Re: Gel Script: How to Generate a CSV

    Posted 07-08-2015 03:39 AM

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



  • 8.  Re: Gel Script: How to Generate a CSV

    Posted 07-08-2015 01:56 PM

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