Need to convert columns into rows in server 7.5

[Jagan617]

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

[ray.wurlod]

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.

[chulett]

Have you considered using the Pivot stage for this? It converts columns to rows, after all. And the Sequential File stage can already read that as separate columns, given the proper delimiter.

[chucksmith]

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 (

Code: Select all

Convert(';"', @VM, your_input_column_name)

), 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:

Code: Select all

@ID = @ID BY @ID

. This will sort the data in the same row order as the original input file.

Again, this sacrifices performance for reliability.

[chulett]

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?