DSXchange

I'm not sure what the terminology is for this situation, so I'm not sure how to search for a solution.

I've got a data file where a record looks like this:

09/09/2009|Tracy|Red|Green|Blue|Orange

I want to insert that into my database as 4 records like this:

09/09/2009 Tracy Red
09/09/2009 Tracy Green
09/09/2009 Tracy Blue
09/09/2009 Tracy Orange

In order to do this, I was going to process the file 4 times, pulling out each color individually. Then I'd do 4 loads.

Not a big deal when there's only 4. But my file actually has 30 and I didn't want to have such repetition.

I was hoping to be able to create a simple job and then create a loop to run it 30 times using a variable to pull different fields (for instance, use COLOR_X in my Router where I would substitute X with the Loop Number).

I couldn't get this to work. Is this possible. Or is there another method that I can look into?

I see the Job Type is Server, but posting is in the Parallel Extender forum.

For your specific problem , you can use the Pivot Stage(Server), That should take care of the problem you describe.
Documentation on Pivot stage (server) is supplied in the Plug In Guides section.. Alternatively a search query on 'Pivot' or 'Horizontal Pivot'

Regards
Ramesh

Hi Tracy;

you can try this, use the field function and stage variable like this:

1. stage variable value eg StgV = [repeated part]
eReplace( Field(YourCol,'|',1,2),'|',' ') = (09/09/2009 Tracy)
2. First column = StgV:' ':Field(YourCol,'|',3,1)
3. Second column = StgV:' ':Field(YourCol,'|',4,1)
.
.
.
.
.
30. Thirty column = StgV:' ':Field(YourCol,'|',33,1)

Hope this help

Hmmm... the Pivot stage is a much simpler solution. It exists to 'twist' columns into rows.

Sorry,
i tried to help,
Graig, can i use the pivot stage, even the data to be splited contains in one column?

No worries. :D

To use the stage, you'd first need to parse the one column up into multiple columns and you could use the Field() function or the Column Import stage for that.

This worked! I should've been able to figure that out on my own. Thanks for answering my stupid question.