Split Records

rafidwh · Post by **rafidwh** » Tue Sep 18, 2007 2:31 am

Hi All,

I need to take only detail record from a file which has header and trailer also and it is a fixed width file.

record format is

Header

Record Type Alphanumeric 1 1 A - header record
File Create Date and Time Alphanumeric 26 2 - 27 Date and time file created
Filler Alphanumeric 123 28 - 150 Not Use

Detail Records follows

Trailer Record

Record Type Alphanumeric 1 1 T - trailer record
Record Count Numeric 10 2 - 11 Count of detail records
Filler Alphanumeric 139 12 - 150 Not Used

I need to read only the detail.What is the best logic to reado nly detail records form this file.

Your suggestions are most helpful

Regards,
Saik.

Raghavendra · Post by **Raghavendra** » Tue Sep 18, 2007 3:05 am

You can use a shell script in before job subroutine to remove header and trailer records from the file and read only the detail records.

JoshGeorge · Post by **JoshGeorge** » Tue Sep 18, 2007 4:16 am

In the 'filter' option of your sequential file stage use the below code and try

Code: Select all


sed -e '$d' -e '1d'

rafidwh · Post by **rafidwh** » Tue Sep 18, 2007 4:58 am

Hi George can you please how it removes header and trailer or read only detail records

rafidwh · Post by **rafidwh** » Tue Sep 18, 2007 5:00 am

The header record is of length 150

JoshGeorge · Post by **JoshGeorge** » Tue Sep 18, 2007 5:03 am

Did you try? Sed is an Unix command, you will find details about that command if you search. Above code will remove the first and last line from the input stream feeding to datastage.

stefanfrost1 · Post by **stefanfrost1** » Tue Sep 18, 2007 6:27 am

I did a similar thing reading files with a header and trailer line, header was send information and trailer no of records sent information.

I read everything as one column varchar (with appropriate length to capture all data on a line). Then i passed it into a transformer where i put the first line into a stage variable, counted each record and converted them (excluding first and last record) a schema and sent them done a link. Then when the last line occured (i could spot this in the data for that line (special char)). I parsed that line for a no of records sent count and compared it to my record count in the transformer and if it wasnt the same i sent a record done a new link with constraint to fail job!

This worked out great as a way to vaildate file deliveries at the same time as converting them to a useable schema and format.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 18, 2007 4:47 pm

If the header and trailer are differently structured to detail records, they can be captured using the Sequential File stage's reject link. They can be parsed subsequently, if desired, using a Column Import stage.