how to remove header and trailer from records.
Moderators: chulett, rschirm, roy
Ah - ok - sorry - I think I understand now. Let me restate to make sure I understand.
You have Detail records that have a set number of columns. The first record has the text "HDR" pre-pended to the front of the record. The last record has "TRL" pre-pended to the front of the record. You still need all the records (including the first and last) but need the prefixes stripped off..
Correct?
By the way - If this is true, then whoever is setting your records up is an idiot - this is NOT the way to do header and trailer records. They should NEVER change the format of a detail record. Always request separate header / trailer records with NO data on those records. Then my first suggestion will work.
You have Detail records that have a set number of columns. The first record has the text "HDR" pre-pended to the front of the record. The last record has "TRL" pre-pended to the front of the record. You still need all the records (including the first and last) but need the prefixes stripped off..
Correct?
By the way - If this is true, then whoever is setting your records up is an idiot - this is NOT the way to do header and trailer records. They should NEVER change the format of a detail record. Always request separate header / trailer records with NO data on those records. Then my first suggestion will work.
Oh my... we're both wrong. I just went back and reread the original post. As in "read read" and looked at the example. Missed this key fact that "all records are in a single record".
There is no header record, followed by X detail records, followed by a trailer record. There is a single record where there is a HDR section, followed by a DTL section and ended with a TRL section. Sheesh.
I don't recall the example being there earlier, perhaps that's just me or a little bit of revisionist history on the OP's part.
There is no header record, followed by X detail records, followed by a trailer record. There is a single record where there is a HDR section, followed by a DTL section and ended with a TRL section. Sheesh.
I don't recall the example being there earlier, perhaps that's just me or a little bit of revisionist history on the OP's part.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 77
- Joined: Tue Apr 14, 2009 4:03 am
Hi Chulett,chulett wrote:<bzzzztttt> Sorry Andy, thanks for playing.
D'oh... where the heck did Andy go? Silly Wabbit.
Define 'not working', that looks perfectly fine to me. Are you sure you are checking the right 'column name'?
its fixed length file ,and header ,details and trailer is there in one column only.if i use above constraint,it filter all the deatils of that particular column.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Why not read the filename with pipe as record delimiter and take only records which has 'DTL' as first field ?
Alternatively you can use Unix awk or sed etc.
Below example is not tested. So you may have to tweak it
Alternatively you can use Unix awk or sed etc.
Below example is not tested. So you may have to tweak it
Code: Select all
awk RS="|" FS="," '{if ($1 == "DTL") {print $0 } } ' yourFileName
OK, so finally a much more representative example gets posted. Let's keep going...
What needs to happen to this record? I get the 'ignore/remove the HDR and TRL information' (right?) but there are 9 DTL pieces stuffed in there. Where do they go? Do you need to parse out them and summarize them in some fashion? Turn that one record into nine records output records? Something else entirely? Do you know if there is a maximum number of DTL occurances? Who designs things like this?
Please be as complete as possible so there's no more tail chasing.
What needs to happen to this record? I get the 'ignore/remove the HDR and TRL information' (right?) but there are 9 DTL pieces stuffed in there. Where do they go? Do you need to parse out them and summarize them in some fashion? Turn that one record into nine records output records? Something else entirely? Do you know if there is a maximum number of DTL occurances? Who designs things like this?
Please be as complete as possible so there's no more tail chasing.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 77
- Joined: Tue Apr 14, 2009 4:03 am
Or as an alternative try this:
1) On your sequential file input stage, Format tab, Record Level, Set Record type=implicit.
2) on Field defaults set delimiter to the "thorn" character in your example (I can't generate it with my keyboard).
3) Also on Field Defaults set Quote to "None".
4) For columns, set up one Varchar field big enough to hold a detail record.
5) Then in a transformer stage use the constraint I specified very early in this dialogue to eliminate records that start with "HDR" or "TRL".
6) On output sequential stage use same settings (implicit, thorn, none).
This is exactly what an implicit setting is for - reading or writing data as a "stream".
I just wrote a quick test (using a pipe instead of the thorn) and it worked for me...
Input:
HDR|A|B|C|D|E|F|G|H|I|TRL
Output:
A|B|C|D|E|F|G|H|I|
1) On your sequential file input stage, Format tab, Record Level, Set Record type=implicit.
2) on Field defaults set delimiter to the "thorn" character in your example (I can't generate it with my keyboard).
3) Also on Field Defaults set Quote to "None".
4) For columns, set up one Varchar field big enough to hold a detail record.
5) Then in a transformer stage use the constraint I specified very early in this dialogue to eliminate records that start with "HDR" or "TRL".
6) On output sequential stage use same settings (implicit, thorn, none).
This is exactly what an implicit setting is for - reading or writing data as a "stream".
I just wrote a quick test (using a pipe instead of the thorn) and it worked for me...
Input:
HDR|A|B|C|D|E|F|G|H|I|TRL
Output:
A|B|C|D|E|F|G|H|I|