GEL File Operations

ccppmop1591
GEL can open a file (and if it is an XML file or a comma-delimited file, parse out all of the nodes and attributes), read the file, and write to it. GEL can also perform FTP operations on files. GEL cannot, however, create a directory to put files in, move files around, or delete files after it is done with them. This limitation can be a problem when working with the Documents XOG.
Example: Create a Rate Matrix XOG File
This example creates a rate matrix XOG file. The file opens a tab-delimited text file as input, and creates a matrixRow node for each row of input data.
<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"> <gel:parameter default="niku" var="clarityUser"/> <gel:parameter default="nikuadmin" secure="true" var="clarityPassword"/> <gel:parameter default="E:\Clarity\XOG\bin" var="infolder"/> <gel:parameter default="E:\clarity\xog\bin\rateMatrixLoadFile.xml" var="XOGloadfile"/> <gel:parameter default="${infolder}\rateMatrix.tab" var="infile"/> <gel:formatDate format="yyyyMMdd" stringVar="today"/> <!-- Open up the input file --> <file:readFile fileName="${infile}" delimiter="\t" var="infileParsed" embedded="false"/> <!-- The GEL parse statement can be given the name of an XML file, or, as shown below, an XML node structure. --> <!-- Use it to create the main XML shell and add in non-repetitive sections like the columns section below --> <gel:parse var="loadContent"> <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_matrix.xsd"> <Header action="write" externalSource="NIKU" objectType="matrix" version="12.0.0.5028"/> <matrices> <matrix defaultCurrencyCode="USD" name="D&#38;B COST/RATE MATRIX" type="Cost/Rate"> <columns> <column name="entity"/> <column name="department"/> <column name="resourceClass"/> <column name="transactionClass"/> <column name="resourceRole"/> <column name="resource"/> <column name="inputTypeCode"/> </columns> <matrixRows> </matrixRows> </matrix> </matrices> </NikuDataBus> </gel:parse> <!-- Build the sections. Skip the headers on the first line --> <core:forEach items="${infileParsed.rows}" var="row" indexVar="i" begin="1" step="1"> <!-- This GEL:parse statement creates the node in memory --> <gel:parse var="matrixRowNode"> <matrixRow actualCost="${row[11]}" currencyCode="${row[12]}" entity="${row[2]}" department="${row[3]}" fromDate="${row[0]}" rate="${row[9]}" transactionClass="${row[5]}" resourceClass="${row[4]}" resourceRole="${row[6]}" inputTypeCode="${row[8]}" resource="${row[7]}" standardCost="${row[10]}" toDate="${row[1]}"/> </gel:parse> <!-- GEL:set below adds the node in memory to the main XML file we're building --> <gel:set value="${matrixRowNode}" select="$loadContent/NikuDataBus/matrices/matrix/matrixRows" insert="true"/> </core:forEach> <!-- Now write it all to a file --> <gel:serialize fileName="${XOGloadfile}" var="${loadContent}"/> </gel:script>
Example: Output Delimited Files
This example shows how to output delimited files using GEL.
<!-- Open up the output file --> <file:writeFile fileName="${doclistfile}" delimiter=",">
Example: Create a File to Write in Documents for Multiple Projects
This example creates a file to write in documents for a number of projects. This example takes a XOG file for a project as input, extracts each project ID in sequence, and creates a parent node for the Documents XOG. Consider this information:
  • The Document XOG requires the internal database ID of the project, not the UNIQUE_NAME that appears as the project ID in the input file. Use the GEL JDBC connection to retrieve the corresponding DBID for each project as the Documents XOG XML file is built.
  • The Document XOG only loads data at the folder level, which means it takes a source folder and uploads it into a target folder. If you want to upload documents for multiple projects, the documents for each project must be placed into an individual temporary folder. GEL does not currently support these steps (creating folders, copying files into them, and so on), so you must prepare the documents elsewhere.
In addition, this example illustrates another way to build the XML file using the GEL:set tag.
<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" xmlns:sql="jelly:sql"> <gel:parameter default="jdbc:microsoft:sqlserver://myserver:1433;DatabaseName=pmodev;SelectMethod=cursor" var="clarityURL"/> <gel:parameter default="niku" var="clarityUser"/> <gel:parameter default="niku" secure="true" var="clarityPassword"/> <gel:parameter default="D:\App\pmo\xog\xml" var="infolder"/> <gel:parameter default="${infolder}\prj_projectswrite.xml" var="infile"/> <gel:parameter default="D:\App\pmo\xog\xml" var="XOGlogFolder"/> <gel:parameter default="${infolder}\DocumentsXOGLoad.xml" var="docXOGloadfile"/> <gel:parameter default="${infolder}\docslist.gel" var="doclistfile"/> <gel:formatDate format="yyyyMMdd" stringVar="today"/> <!-- Get a DB Connection to Clarity --> <sql:setDataSource url="${clarityURL}" driver="com.microsoft.jdbc.sqlserver.SQLServerDriver" user="${clarityUser}" password="${clarityPassword}" var="clarityDS"/> <!-- Open up the Project Plans input file --> <gel:parse var="projectsParsed" file="${infile}"/> <!-- Open up the output file --> <file:writeFile fileName="${doclistfile}" delimiter=","> <!-- set up the document XOG shell --> <gel:parse var="docsParsed"> <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_document.xsd"> <Header action="write" externalSource="OS" objectType="document" version="12.0.0.5028"/> <Documents> </Documents> </NikuDataBus> </gel:parse> <!-- Define a variable for the Documents node --> <gel:set select="$docsParsed/NikuDataBus/Documents" var="docnode"/> <!-- set up a template Parent node --> <gel:parse var="parentNode"><Parent documentLocation="" parentObjectId="" parentObjectType="Projects"/></gel:parse> <!-- Loop thru each project ID in the input file --> <gel:forEach select="$projectsParsed/NikuDataBus/Projects/Project" var="currentPrj"> <gel:set asString="true" select="$currentPrj/@projectID" var="currentPrjID"/> <!-- reset the test flag --> <core:set value="false" var="hasDocs"/> <!-- Build the XOG file for the documents. The process is to insert a copy of the node --> <!-- currently in memory, then modify the attributes as necessary. After that, the --> <!-- node in memory is reset to the current one. Also note that the Documents XOG --> <!-- requires the DBID of the project ...so we have to connect to Clarity first. --> <core:if test="${hasDocs}"> <sql:query var="prjIDquery" dataSource="${clarityDS}"> SELECT ID FROM niku.SRM_PROJECTS WHERE UNIQUE_NAME = ? <sql:param value="${row[i]}"/> </sql:query> <!-- there should only be one result value... --> <core:forEach trim="true" items="${prjIDquery.rowsByIndex}" var="idrow"> <core:forEach var="idfield" items="${prjIDquery.columnNames}" indexVar="j"> <gel:set value="${parentNode}" select="$docsParsed/NikuDataBus/Documents" insert="true"/> <gel:set value="${infolder}\docimporttemp\${currentPrjID}" select="$docnode/Parent/@documentLocation"/> <gel:set value="${idrow[i]}" select="$docnode/Parent/@parentObjectId"/> <gel:set var="parentNode" select="$docnode/Parent"/> </core:forEach> </core:forEach> </core:if> </gel:forEach> <!-- Write the XOG file for documents --> <gel:serialize fileName="${docXOGloadfile}" var="${docsParsed}"/> <!-- Close the output file --> </file:writeFile> </gel:script>