Parsing Source File

edison · Participant Joined: 17 Sep 2007 Posts: 19 Points: 118

mobashshar · Posted: Mon May 14, 2012 1:45 pm

What have you tried so far?

edison · Participant Joined: 17 Sep 2007 Posts: 19 Points: 118

Convert(Convert(‘,’,’’, col1), ‘,’, ‘’) = ‘,’ then substring the value using delimiter

Not getting upto point.

venkateshrupineni · Participant Joined: 02 May 2012 Posts: 15 Points: 99

in stage variables
write as like simle way

sv1=field(col1,'|',1) it will give filed1 of 0001,0002
sv2=field(col1,'|',2) it will give field2 of Truck,Rail Carsv3
then
sv3 = field(sv1,',',1) it will give 0001
sv4 = field(sv1,',',2) it will give 0002
sv5=field(sv2,',',1) it will give Truck
sv6 = field(sv2,',',2) it will give Rail Carsv3

then after you can concatenate as per your requirement

------------------------
Venky

Kryt0n · Participant Joined: 22 Jun 2005 Posts: 584 Points: 3356

Does it have to be done in a Server job? Parallel looping would be far more appropriate.

Not sure how much of this would work as haven't tried myself and more thought process than certainty.

As you haven't named any of it, having a pipe delimiter, the first column I will call seqno. So how many comma-separated seqno values can you get in one row?

Split your incoming data on the pipe
Define a stage variable (svSeqNo) as a comma separated sequential numerics to represent how many seqnos you can get in one row (i.e. 1,2,3,4...)
Add a new output column called seqnocount (or whatever) as
- Field(svSeqNo, ',', 1, DCount(seqno,','))
for your first row, this should give the result "1,2"
Use a row splitter with this new column (seqnocount) as your split column and needs to create a new row for each (don't ask me how, haven't tried, will be good practice for you)
This should give you an output of
1<delim>0001,0002<delim>Truck,Rail Car
2<delim>0001,0002<delim>Truck,Rail Car
etc
After row splitter, use a transform stage to output the delimited field to match your seqnocount
i.e. Field(seqno,',',seqnocount)

battaliou · Posted: Fri Jun 29, 2012 2:29 am

If you use venkateshrupineni solution then you can have two output's from your transformer into a funnel with a contraint on the 2nd output based on whether sv4<>''

ray.wurlod · Posted: Fri Jun 29, 2012 5:45 am

... except that there's no Funnel stage in server jobs. Though a hashed file can be employed to the same end, though needing a generated key.

Kryt0n · Participant Joined: 22 Jun 2005 Posts: 584 Points: 3356

battaliou wrote:

If you use venkateshrupineni solution then you can have two output's from your transformer into a funnel with a contraint on the 2nd output based on whether sv4<>''

Or just separate the two by a cr/crlf if outputting to a sequential file but what if there are three or more comma separated values?