Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.


Postby DavidKing » Mon May 04, 2009 11:57 am

Can I run insert / update commands based on the value of the 1st three chars in a text file ?
Verified User
Posts: 2
Joined: Mon May 04, 2009 11:43 am

Re: Conditionals

Postby Support_Duane » Fri May 08, 2009 12:20 pm

Yes. Here is an example of a fixed width file with three record types - HDR, DTL and TRL - each of which needs to go into a different database file. In this example, those files will be iSeries files, but this will work with any database. Both the Fixed Width file and the Project file are attached.

Fixed Width File – dbod.txt

Code: Select all
HDGHeading line        Stuff after heading                                     x
DTLDetail line one     More detail data    more detail data for line one       x
DTLDetail line two     More detail data    more detail data for line two       x
DTLDetail line three   More detail data    more detail data for line three     x
TRLTrailer line                                                                x

Heading database file DDS

Code: Select all
     A          R DBODHR                    TEXT('Header Records')
     A            DATA1         20A         COLHDG('Data' 'One') 
     A            DATA2         20A         COLHDG('Data' 'Two') 

Detail database file DDS

Code: Select all
     A          R DBODDR                    TEXT('Detail Records') 
     A            DATA1         20A         COLHDG('Data' 'One')   
     A            DATA2         20A         COLHDG('Data' 'Two')   
     A            DATA3         37A         COLHDG('Data' 'Three') 

Trailer database file DDS

Code: Select all
     A          R DBODTR                    TEXT('Trailer Record')
     A            DATA1         20A         COLHDG('Data' 'One')   

The project contains two tasks. The first will read the Fixed Width file, and create a rowset. The second task will create a temporary database file (in QTEMP on the iSeries, any other system will require another step to remove the file) from which the records can then be selectively processed.

Code: Select all
<project name="process FW file" mainModule="Main" version="1.0">

   <module name="Main" logLevel="debug">

      <!-- Read the fixed width file and create a four column rowset -->
      <readFixedWidth label="read dbod.txt" inputFile="/gademo/dbod.txt" outputRowSetVariable="data" skipFirstRow="false" recordDelimiter="CRLF">
            <column index="1" name="RecordType" type="CHAR" size="3" />
            <column index="2" name="data1" type="CHAR" size="20" />
            <column index="3" name="data2" type="CHAR" size="20" />
            <column index="4" name="data3" type="CHAR" size="37" />

      <sql label="Write to DBOD4" resourceId="Dev61">
         <!-- Create a temporary file -->
         <query label="create dbod4">
            <statement>create table qtemp.dbod4
   (rectyp CHAR(3),
    data1  CHAR(20),
    data2  CHAR(20),
    data3  CHAR(37))</statement>

         <!-- Fill the temporary file with data from the Fixed Width file -->
         <query label="fill dbod4" inputRowSetVariable="${data}">
            <statement>insert into qtemp.dbod4 values(?, ?, ?, ?)</statement>

         <!-- Select and process the Header records -->
         <query label="get hdg records" outputVariable="hdgData">
            <statement>SELECT DATA1, DATA2 FROM qtemp.DBOD4 WHERE RECTYP = &apos;HDG&apos; </statement>
         <query label="clear dbodh">
            <statement>delete from gatest.dbodh</statement>
         <query label="write hdg records" inputRowSetVariable="${hdgData}">
            <statement>insert into gatest.DBODH values(?,?)</statement>

         <!-- Select and process the Detail records -->
         <query label="get dtl records" outputVariable="dtlData">
            <statement>SELECT DATA1, DATA2, DATA3 FROM qtemp.DBOD4 WHERE RECTYP = &apos;DTL&apos; </statement>
         <query label="clear dbodd">
            <statement>delete from gatest.dbodd</statement>
         <query label="write dtl records" inputRowSetVariable="${dtlData}">
            <statement>insert into GATEST.DBODD values(?,?,?)</statement>

         <!-- Select and process the Trailer records -->
         <query label="get trl records" outputVariable="trlData">
            <statement>SELECT DATA1 FROM qtemp.DBOD4 WHERE RECTYP = &apos;TRL&apos; </statement>
         <query label="clear dbodt">
            <statement>delete from gatest.dbodt</statement>
         <query label="write trl records" inputRowSetVariable="${trlData}">
            <statement>insert into GATEST.DBODT values(?)</statement>


process FW file.xml
Project file
(2.15 KiB) Downloaded 92 times
fixed width file
(410 Bytes) Downloaded 80 times
Support Specialist
Posts: 66
Joined: Thu Mar 05, 2009 3:49 pm

Return to Community Forum

Who is online

Users browsing this forum: Google [Bot] and 1 guest