GoAnywhere Forum

Posted: **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 ?

Posted: **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">
			<data>
				<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" />
			</data>
		</readFixedWidth>


		<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>
			</query>

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

			<!-- 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>
			<query label="clear dbodh">
				<statement>delete from gatest.dbodh</statement>
			</query>
			<query label="write hdg records" inputRowSetVariable="${hdgData}">
				<statement>insert into gatest.DBODH values(?,?)</statement>
			</query>

			<!-- 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>
			<query label="clear dbodd">
				<statement>delete from gatest.dbodd</statement>
			</query>
			<query label="write dtl records" inputRowSetVariable="${dtlData}">
				<statement>insert into GATEST.DBODD values(?,?,?)</statement>
			</query>

			<!-- 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>
			<query label="clear dbodt">
				<statement>delete from gatest.dbodt</statement>
			</query>
			<query label="write trl records" inputRowSetVariable="${trlData}">
				<statement>insert into GATEST.DBODT values(?)</statement>
			</query>
		</sql>

	</module>

</project>

GoAnywhere Forum

Conditionals

Conditionals

Re: Conditionals