Actually i have got the data like this as mentioned below in the input file and i need to separate them into rows considering ; as delimiter. Can you please assist me in this asap.
I/P
"AGAR";"AILR";"ASIR";"BIRD STRIKE";"CCAR";"CROSS";"DGOR - FRT";
O/P
AGAR
AILR
BIRD STRIKE
CCAR
CROSS
DGOR-FRT
Need to convert columns into rows in server 7.5
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Parse into separate columns (Transformer stage) then use a Pivot stage.
DSXchange is an all volunteer site whose members post as and when they can. It is not a substitute for official support and, particularly, nothing here is urgent. We regard terms like "asap" to be offensive.
If you want urgent help, sign up with your support provider for priority support, and learn just how expensive "urgent" really is.
DSXchange is an all volunteer site whose members post as and when they can. It is not a substitute for official support and, particularly, nothing here is urgent. We regard terms like "asap" to be offensive.
If you want urgent help, sign up with your support provider for priority support, and learn just how expensive "urgent" really is.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
If the number of potential output rows is variable, then you may have to take an I/O intensive approach. Convert the double quotes to nothing, and convert the semicolons to @VM (), then write this output column to a hashed file as a data column. The primary key of the hashed file could be @INROWNUM. On the output side of the hashed file, set the data column's Type to MV, and normalize based upon the data column's association. The data coming out of the hashed file will be in the order you expect.
Note: On the selection sub-tab of the output tab of the hashed file stage, you may want this:. This will sort the data in the same row order as the original input file.
Again, this sacrifices performance for reliability.
Code: Select all
Convert(';"', @VM, your_input_column_name)
Note: On the selection sub-tab of the output tab of the hashed file stage, you may want this:
Code: Select all
@ID = @ID BY @ID
Again, this sacrifices performance for reliability.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
Right, that's another approach - something akin to what is detailed here in the FAQ forum. As noted, can depend on your input column count - is it constant or a variable?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers