Import Fixed-Width file into a Database

Looking for an example project to get you started?

Import Fixed-Width file into a Database

Postby Support_Julie » Tue Jun 16, 2009 10:29 am

Fixed-Width file to Database

Fixed Width file being imported:
Code: Select all
1111111111aaaaaaaaaaaaaaaaaaaaaaaaa 11111111.11
2222222222bbbbbbbbbbbbbbbbbbbbbbbbb-22222222.22
3333333333ccccccccccccccccccccccccc 33333333.33


Field layout of database table receiving data:
Code: Select all
PARTNBR             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.
Julie
Software Support
Linoma Software
Support_Julie
Support Specialist
 
Posts: 78
Joined: Thu Mar 05, 2009 3:49 pm
Location: Ashland, NE USA

Return to Example Projects

Who is online

Users browsing this forum: No registered users and 1 guest