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 = 'HDG' </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 = 'DTL' </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 = 'TRL' </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>
