[Complex Flat File] Reading Complex Delimited File

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
joycerecacho
Participant
Posts: 298
Joined: Tue Aug 26, 2008 12:17 pm

[Complex Flat File] Reading Complex Delimited File

Post by joycerecacho »

Hi everybody.

I need to find a solution to read a complex file, delimited by ';'.

Imagine the following layout:

Document;QuantityPhone;PhoneNumber;PhoneNumberArea;QuantityEmail;Email;EmailStatus
111111;1;8888-8888;11;1;test@test.com;Valid
222222;2;7777-7777;11;6666-6666;11;1;test@test.com;Valid
333333;3;6666-6666;11;5555-5555;11;4444-4444;11;2;test@test.com;Valid;test2@test.com;Valid

In other words, when fields value "QuantityPhone" and "QuantityEmail" are greater than 1, the group of fields related to "Phone" and "Email" are presented as new columns.
So, each line/register can have different number of columns. It is a dynamic delimited file.

I thought we could use CFF object (Complex Flat File) however I guess it is appropriated to positional columns instead of delimited ones.

Could anybody give me some help or any tip about how reading this complex file?

Thanks In advance.

Best regards.
Joyce A. Recacho
São Paulo/SP
Brazil
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Yeah, that stage is expecting to process positional mainframe COBOL files but what you've got there is basically a couple of "OCCURS DEPENDING ON" clause situations. What do you want to do with the dynamic elements of the data, turn each one into a new record with the same common/leading data fields? We'd be talking about some kind of a horizontal pivot then of columns to rows, it would seem. Not exactly sure how you'd serve that dish quite yet but let's set the table first. What's the goal / requirement here? Can you detail the desired output?
-craig

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

It could be read using the regular Sequential File stage into a single varchar column, and then parsed according to your rules by using the looping feature in Transformer stages.
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I would think so too but want to see exactly what kind / how many targets they need to turn that into. Joyce, do you know if there is a maximum occurrence value?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply