Clarity

  • 1.  GEL Script to take the result into Excel

    Posted Oct 19, 2012 07:28 AM
    i have some big sql queries which i need to run it manually and paste the data into excel and upload it into shared folder.
    I want to write the GEL script which automate it.
    Does anyon have any idea on this kind of script?

    Thanks,
    Umesh


  • 2.  RE: GEL Script to take the result into Excel
    Best Answer

    Posted Oct 19, 2012 08:42 AM
    Hi - a GEL / Excel (CSV file) solution isn't the only solution to your use-case I think?

    But you might find some good clues in these threads (or SEARCH for some others);
    Querying data from a SQL Server DB for writing that data into a csv file - note that the GEL script in that thread is faulty, you would need to fix that bug

    How to fetch records from clarity tables as CSV files?

    Exporting Portlet Results to Comma-Separated Values File using GEL Script

    GEL script to do a XOG and FTP that file using GEL Script

    GEL : Accessing files on remote shares

    How to access a shared network path from GEL ?


  • 3.  RE: GEL Script to take the result into Excel

    Posted Oct 22, 2012 01:41 PM
    A non GEL option if you are running on MSSQL would be SSIS. This has all the feature sets needed including building native Excel files.


  • 4.  RE: GEL Script to take the result into Excel

    Posted Oct 23, 2012 06:43 AM
    The below script i have written to take the data into csv file and made the process for it. But it is not giving me any output. is anything wrong in the script? I am new to GEL script so i am hoping there is something missing which is causing to fail. Can someone help me to correct this?

    <gel:script xmlns:core="jelly:core"
    xmlns:f="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:soap-env="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:sql="jelly:sql" xmlns:util="jelly:util"
    xmlns:xog="http://www.niku.com/xog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <gel:formatDate format="ddMMyyyy" stringVar="dateToday"/>
    <gel:parameter
    default="devhonts5908SQL.homeoffice.wal-mart.com:1433;databaseName=Clarity_isd" var="strJdbc"/>
    <gel:parameter
    default="com.microsoft.jdbc.sqlserver.SQLServerDriver" var="strDriver"/>
    <gel:parameter default="\\File path" var="filePath1"/>
    <gel:parameter default="file name" var="fileName1"/>
    <gel:formatDate format="dd-MM-yyyy-HH-mm" stringVar="run_date"/>
    <core:set value="0" var="totalrecords"/>
    <gel:parameter default="niku" var="extUser"/>
    <gel:parameter default="pwd" secure="true" var="extPassword"/>
    <gel:log level="INFO">Report Script Started</gel:log>
    <!-- Verify a connection with the database -->
    <core:catch var="errorVar">
    <gel:setDataSource dbId="niku"/>
    </core:catch>
    <core:catch var="errorVar">
    <sql:setDataSource driver="${strDriver}"
    password="${extPassword}"
    url="jdbc:microsoft:sqlserver://${strJdbc}"
    user="${extUser}" var="oldNiku"/>
    </core:catch>
    <!--<gel:log level="WARN">Error: ${errorText}</gel:log>-->
    <core:if test="${errorVar != null}">
    <core:set
    value="Database Connection Failed. Error Messge: ${errorVar}" var="errorText"/>
    <gel:log level="WARN">${errorText}</gel:log>
    </core:if>
    <core:choose>
    <core:when test="${errorVar == null}">
    <core:catch var="errorVar">
    <sql:query var="dual">select 1 from dual</sql:query>
    <gel:log level="INFO">Database connection established</gel:log>
    </core:catch>
    <core:if test="${errorVar != null}">
    <core:set value="Connection unavailable" var="errorText"/>
    </core:if>
    </core:when>
    <core:otherwise>
    <core:set value="Database connection NOT established" var="errorText"/>
    </core:otherwise>
    </core:choose>
    <core:if test="${errorText != null}">
    <gel:log level="ERROR">errorText</gel:log>
    </core:if>
    <sql:query var="result">
    SELECT
    NAME PROJECT,
    UNIQUE_NAME ID_PROJECT,
    CREATED_DATE DATE_CREATION
    FROM
    niku.SRM_PROJECTS
    WHERE
    NAME LIKE '%test%'
    ORDER BY NAME
    </sql:query>

    <core:forEach indexVar="j" items="${result.rows}" var="row">
    <core:set value="${row.get('projet')}" var="project"/>
    <core:set value="${row.get('id_project')}" var="id_project"/>
    <core:set value="${row.get('date_creation')}" var="date_creation"/>
    <core:catch var="filerrcreate">
    <f:writeFile delimiter=";" embedded="false" fileName="${filePath1}\${run_date}${fileName1}">
    <f:line>
    <f:column value="${project}"/>
    <f:column value="${id_project}"/>
    <f:column value="${date_creation}"/>
    </f:line>
    </f:writeFile>
    </core:catch>
    <core:set value="${totalrecords + 1}" var="totalrecords"/>
    </core:forEach>
    </gel:script>


  • 5.  RE: GEL Script to take the result into Excel

    Posted Oct 24, 2012 08:09 AM
    I'm no expert but try something like this after the end of your sql query block (note I've set my code to be tab-delimited).
    <core:forEach trim="true" items="${result.rows}" var="row">
    
    <core:set var="thisRow" value="${row.name},${row.data}" />
    
    <gel:out>${thisRow}</gel:out>
    </core:forEach>
    
    <core:set var="thisFileName" value="${filePath1}\${run_date}${fileName1}" />
    
    <f:writeFile fileName="${thisFileName}" delimiter="&#0009;" embedded="false">
    
    <f:line>
    
    <core:forEach trim="true" items="${results.rows}" var="row">
    
    
    <f:column value="${row.name}" />
    
    
    <f:column value="${row.data}${line.separator}" />
    
    </core:forEach>
    
    </f:line>
    </f:writeFile>
    I also noticed your code is looking for a column in your result set called "projet" when you've specified it as "project" in the sql block.


  • 6.  RE: GEL Script to take the result into Excel

    Posted Oct 25, 2012 03:51 PM
    Thanks for the Reply Owen.

    I will check and let you know if that works for me.