DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
joycerecacho
Participant



Joined: 26 Aug 2008
Posts: 296

Points: 5463

Post Posted: Tue Sep 17, 2019 1:31 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43026
Location: Denver, CO
Points: 222086

Post Posted: Tue Sep 17, 2019 10:18 pm Reply with quote    Back to top    

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

Peaches come from a can, they were put there by a man
If I had my little way, I'd eat peaches every day
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2873
Location: USA
Points: 21812

Post Posted: Wed Sep 18, 2019 5:27 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43026
Location: Denver, CO
Points: 222086

Post Posted: Wed Sep 18, 2019 6:18 am Reply with quote    Back to top    

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

Peaches come from a can, they were put there by a man
If I had my little way, I'd eat peaches every day
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours