Page 1 of 1
Parsing Source File
Posted: Mon May 14, 2012 11:51 am
by 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
Posted: Mon May 14, 2012 1:45 pm
by mobashshar
What have you tried so far?
try with fild function
Posted: Fri Jun 29, 2012 12:08 am
by 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
Posted: Fri Jun 29, 2012 12:49 am
by 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)
Posted: Fri Jun 29, 2012 2:29 am
by 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<>''
Posted: Fri Jun 29, 2012 5:45 am
by 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.
Posted: Sun Jul 01, 2012 4:39 pm
by Kryt0n
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?