how to remove header and trailer from records.

[chulett]

We know.

What's "not working" about it? Show us examples of what a detail (non-header-trailer) record or two look like.

[asorrell]

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.

[chulett]

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.

[asorrell]

Yah - that's why I'm trying to ask for clarification - because the description doesn't match the example.

Is HDR and TRL section on every row? Can you supply a better example with more data?

[kaushal.kumar@igate.com]

<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'?

Hi Chulett,

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.

[Sainath.Srinivasan]

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

Code: Select all

awk RS="|" FS="," '{if ($1 == "DTL") {print $0 } } ' yourFileName

[chulett]

Can I humbly suggest that we wait for the requirements to be clarified before we throw out any more suggestions? Especially UNIX based one when this may be easily accomplished 'in tool' once we know. Thanks.

[chulett]

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.

[asorrell]

Craig? Are you getting too old to chase tail?

[chulett]

Craig? Are you getting too old to chase tail?

My own tail, yes.

[kaushal.kumar@igate.com]

Craig? Are you getting too old to chase tail?

My own tail, yes.

i need all this 9 DTL records in single column

[chulett]

Use a combination of DCount() and Field(), the former to let you know how many 'fields' there are and the latter to pull out everything from the 2nd field to the 'next to last' one.

[asorrell]

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|