Fixed-Width file to Database
Fixed Width file being imported:
Code: Select all1111111111aaaaaaaaaaaaaaaaaaaaaaaaa 11111111.11
2222222222bbbbbbbbbbbbbbbbbbbbbbbbb-22222222.22
3333333333ccccccccccccccccccccccccc 33333333.33
Field layout of database table receiving data:
Code: Select allPARTNBR Character 10
DESCRIPTION Character 25
PRICE Packed Dec 10,2
This project will get a file from an FTP server, read the Fixed-Width file and then insert the data into a database table. Please note that on the SQL INSERT statement, you need to enter one question mark for each field you are inserting into. In this example three fields are being inserted into. That insert statement is:
INSERT INTO DEMO.TEST3 (PARTNBR, DESCRIPTION, PRICE) VALUES(?, ?, ?)
When inserting into 2 fields you would have two questions marks in the insert statement i.e.:
INSERT INTO LIB.FILE VALUES(?,?)
Code: Select all<project name="Fixed-width to database" mainModule="Main" version="1.0" logLevel="debug">
<module name="Main">
<createWorkspace />
<ftp label="Connect to FTP server" resourceId="Dev 54">
<get label="Get Fixed-width File" sourceFile="/Demo/TEST1.TXT" destinationDir="${system.job.workspace}" type="binary" destinationFilesVariable="fixedwidthFile" />
</ftp>
<readFixedWidth label="Read Fixedwidth File" inputFile="${fixedwidthFile}" outputRowSetVariable="fixedwidthData" skipFirstRow="false" recordDelimiter="CRLF">
<data>
<column index="1" name="PARTNBR" type="CHAR" size="10" alignment="left" />
<column index="2" name="DESCRIPTION" type="CHAR" size="25" alignment="left" />
<column index="3" name="PRICE" size="12" alignment="right" />
</data>
</readFixedWidth>
<sql label="Connect to DB" resourceId="DEV54">
<query label="clear out file">
<statement>DELETE
FROM
DEMO.TEST3
</statement>
</query>
<query label="Import Data" inputRowSetVariable="${fixedwidthData}">
<statement>INSERT INTO DEMO.TEST3
(PARTNBR, DESCRIPTION, PRICE)
VALUES
(?, ?, ?)</statement>
</query>
</sql>
<deleteWorkspace />
</module>
<description>FTP Get Fixed-width file and insert into Database</description>
</project>
* When doing an FTP GET of a Fixed width file, change the transfer type to BINARY to maintain the End of Record marker of CRLF.
