DSXchange

Hi,

I am having some data as follows:

Eg:( for one record)
5
3
4
5;5;6;7
8
9
10

My ouput should be as follows:

5
3
4
5
5
6
7
8
9
10

What is the best way of achieving this output?

I have started looking at data split stage from DS manual.. Is there any other way of doing this?

Appreciate your help

Thanks
Sue

Write them out with linefeeds in place of the semi-colons so they end up as separate records.

Hi,

Thanks for the quick reply.

Could you please provide me an example?

Thanks again

Sue

That will write out "5;5;6;7" as four records at once since CHAR(10) is a LineFeed a.ka. UNIX record delimiter.

Or, since you're on Windows, use Ereplace() to replace each";" with carriage return and line feed.

Windows? I could have sworn when I looked at it before replying it said UNIX. Trickery!

Yes, of course, Windows will need the pair of CR/LF characters as the record delimiter.

Hi,

Could you please explain me how it works when i need to load that data into a table?

If i include the line feeds, DataStage Job is taking the data next to ";" as a new record record and aborting.

Thanks

Sue

Write it to a file as noted, then read it back before loading to a table. All in one job. In your example, you would write 7 'records' but read back 10.

Hi,

The job flow is as follows:

Input ---> Transformer ---> File---> Table

In Transformer, Suppose if a column address needs to be divided into address1 and address2, the derivation for that column(address) is given as Ereplace(FromProgressICSP.addr, ";", Char(13):Char(10), -1).

Please help me how to populate address1 and address2 using the above derivation.

Thanks again for all the help!!

Sue

How can we know? Give us an example (a real example) of this address data and what your business rule to 'split' it is.

paranoid wrote:Hi,

The job flow is as follows:

Input ---> Transformer ---> File---> Table

In Transformer, Suppose if a column address needs to be divided into address1 and address2, the derivation for that column(address) is given as Ereplace(FromProgressICSP.addr, ";", Char(13):Char(10), -1).

Please help me how to populate address1 and address2 using the above derivation.

Thanks again for all the help!!

Sue

That's a totally different question, and would be resolved with the Field() function - you are now after separate columns rather than separate rows. This question really deserved a new thread.

Hi,

I have data as follows:


Input record:
---------------
CompanyNumber:11
Name:XXXX
Address: 111 STREET NAME;P O BOX 1111
Code:0;0;41;0
City:XXXX
State:XXXX

I need to split the data to:

Output Record:
-----------------
CompanyNumber:11
Name:XXXX
Address1: 111 STREET NAME
Address2:P O BOX 1111
Code1:0
Code2:0
Code3:41
Code4:0
City:XXXX
State:XXX


Thanks

Sue

As noted, a completely different question. Next time, new topic please. And as noted, the Field() function can be used to parse a delimited string into component pieces, and your address and code fields are ';' delimited strings. Check the online help.

Hi,

Thank you very much. Field function solved my problem.

Sue :D

U shud be able to do it even using pivot stage in DS