Parsing Source File

[edison]

Hi,

Src file Sequential, Pipe Delimeter, DataTYpe Varchar(10)

0001,0002|Truck,Rail Car
0001|Truck
0002,0001|Rail Car,Truck

Output needed

0001|Truck
0002|Rail Car
0001|Truck
0002|Rail Car
0001|Truck

[mobashshar]

What have you tried so far?

[venkateshrupineni]

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]

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]

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]

... 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]

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?