Trying to skip reading of header and trailer

JPalatianos · Post by **JPalatianos** » Tue Sep 16, 2008 2:41 pm

Hi,
One of our jobs is receiving an input file from the mainframe(fixed with) and always has a header and trailer. In the constraint I have coded:
@INROWNUM > 1 and inSeqPlanfund_hldngs.PlanNumber <> "990000" to bypass reading them....

When I run I get the following error:
LoadBPASFile..seqPlanfund_hldngs.inSeqPlanfund_hldngs: nls_read_fixedwidth() - row 1, column fundid, column unexpectedly ended by EOR

A sample of the file is below. I'm not sure what I'm doing wrong.

AANQFinRpt20080731
06757539664222 879664100 TLAB Tellabs Restricted Stock Fund Tellabs Stk 0000000858413.692008-07-31000000137000000041
06757539664354 P007914XV$02178 Guaranteed Income Fund Guar Income Fnd0000001675281.512008-07-31000000137000000044
06757539664489 779562107 PRNHXT Rowe Price New Horizons T Rwe Pr Nw Hor0000003125262.732008-07-31000000137000000103
06757539664491 316071109 FCNTXFidelity Contrafund Fidelity Contra0000004513817.692008-07-31000000137000000109
06757539664493 922018304 Vanguard Windsor II Fd Admiral Vgrd Wdsr II Fd0000003093910.642008-07-31000000137000000090
06757539664496 693390726 PTRAXPimco Total Return Admin Fund A Pimco Tl Rtn A 0000001871737.542008-07-31000000137000000084
06757539664497 298706839 RERFXAmerican Funds Euro Pacific Gr R5 Am Fds EroPacR50000003466083.312008-07-31000000137000000108
06757539664686 922040100 VINIXVanguard Institutional Index Vangrd Instit 0000001213584.402008-07-31000000137000000079
9900000000010

chulett · Post by **chulett** » Tue Sep 16, 2008 3:08 pm

This doesn't look fixed-width, even after taking the header and trailer out of the picture. Each record needs to be the exact same number of bytes to be successfully read as a fixed-width file. And that includes the header and trailer records.

If you are sure about the fixed-width-ness of it, try reading it as one long string and then parsing / substringing out the individual fields you need to check or process.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 16, 2008 3:15 pm

I think we're seeing wraparound here Craig. Apart from the first record they do appear to be the same size allowing for the wraparound.

However, that said, for fixed-width to work, then EVERY line must have the same size and structure. Your alternative is to read the file as a single VarChar, and parse it in a Transformer stage.

This may also allow you to identify the header and trailer records based either on their structure (length) or on their data.

JPalatianos · Post by **JPalatianos** » Tue Sep 16, 2008 3:24 pm

They are wrapping(and really are fixed width)....I actually tried doing what you suggested Ray, by defining the whole record as VarChar and was erroring out....just realized I needed to uncheck the fixed-width for it to work. I will give it a shot and report back with the results.
Thanks for all the suggestions!! John

chulett · Post by **chulett** » Tue Sep 16, 2008 3:29 pm

I had maxed my browser window so there was no wrapping, so I guess it's more of a proportional font issue.

JPalatianos · Post by **JPalatianos** » Wed Sep 17, 2008 7:24 am

Worked like a charm.....defined teh file originally as a single long varchar and parsed it up from there. This method easily allowed me to weed out the header and trailer in my constraint.
Thanks for all the help!!