| External tables Fixed Format Data [message #354130] |
Thu, 16 October 2008 08:59  |
Jaffa6
Messages: 1
Registered: October 2008
Location: norfolk, england
|
Junior Member |
|
|
I need to create an external table from a flat fixed format data file, the data is not comma delimited or anything like that. Is this possibel and if so how do I do it?
Also there are three records types in the flat file (one header record, one footer record and multiple transaction records). Is it possible to define all three record types in a single external table rather than three external tables?
Bytes 1-2 define the record type, bytes 6-25 hold the record key in the transaction records.
Example file
012820080229
10MPY58001044 98255 1IX20080229
10MPY58002006 90689 1IX20080229
10MPY58002009 90689 1IX20080229
10MPY58006409 90969 1IX20080229
10MPY58006410 90969 1IX20080229
10MPY58006442 90922 1IX20080229
10MPY58006443 90922 1IX20080229
10MPY58006445 90922 1IX20080229
10MPY58006446 90922 1IX20080229
10MPY58006449 90922 1IX20080229
99000000010
|
|
|
|
| Re: External tables Fixed Format Data [message #354154 is a reply to message #354130] |
Thu, 16 October 2008 11:40  |
 |
Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
| Quote: | I need to create an external table from a flat fixed format data file, the data is not comma delimited or anything like that. Is this possibel and if so how do I do it?
|
Yes, use POSITION.
| Quote: | Also there are three records types in the flat file (one header record, one footer record and multiple transaction records). Is it possible to define all three record types in a single external table rather than three external tables?
Bytes 1-2 define the record type, bytes 6-25 hold the record key in the transaction records.
|
You can use the WHEN clause to load only certain rows based on the first two bytes.
Syntax and examples for external tables are available in the online documentation.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
