i want to do the below scenario in Datastage.
I have source file named source.csv file like below,
100
200
300
100,200,400
Now, i want to split the records in 4th row based on the delimiter(,). The expected result should be,
100
200
300
100
200
400
I have tried this by using DCOUNT, Field datastage functions. But i couldn't get exact result which i mentioned above.
Kindly can anyone please guide or suggest some ideas to do this?.
Note: I don't know the number of delimiter before processing the file.
This is a good example for a transformer loop processing.
First determine the number of values in your column using the COUNT function to count the number of separators (commas in your case) and add 1.
In your example you have 2 commas and three values to process - this will be also the number of loops you have to do.
In the loop use the FIELD function to extract the values in the loop definition of the transformer - use @ITERATION (loop counter) to extract the values one by one.
With this solution the transformer will output more rows then it consumes and this will provide your solution.
Details and Examples see here