Page 1 of 1
Insert a delimiter using sort.
Posted: Wed Oct 15, 2014 5:01 pm
by Manish Arora
Hi All,
I need to insert a delimiter(|) between two columns, how can I do that:
File1:
Code: Select all
NO# NAME ID
123 AAAA 1
456 BB 10
789 CCC 2
This should be converted into
Code: Select all
NO# NAME ID
123|AAAA|1
456|BB |10
789|CCC |2
Re: Insert a delimiter using sort.
Posted: Wed Oct 15, 2014 5:23 pm
by zprogrammer
Are the fields of fixed length and do you know in which position you wanted to place the delimiter (|) ?
Re: Insert a delimiter using sort.
Posted: Wed Oct 15, 2014 6:41 pm
by Manish Arora
Hi Pandora-Box,
No, the fields length can vary. After every word (identified by at least once space in between) I need the delimiter.
Re: Insert a delimiter using sort.
Posted: Thu Oct 16, 2014 2:19 am
by William Collins
Do you want to replace spaces which indicate a break of field, or retain them? Do you have any field with an embedded space, like a name and surname in the same field?
Can you explain, and show some representative input and expected output for that?
Re: Insert a delimiter using sort.
Posted: Thu Oct 16, 2014 3:46 pm
by Manish Arora
Hi William,
I want to replace the spaces which indicate a break of field. There is no space between name and surname. For example, I've the same input as I showed in previous post:
Code: Select all
NO# NAME ID
123 AAAA 1
456 BB 10
789 CCC 2
expected output is:
Code: Select all
NO# NAME ID
123|AAAA|1
456|BB |10
789|CCC |2
Re: Insert a delimiter using sort.
Posted: Thu Oct 16, 2014 3:47 pm
by Manish Arora
"NO# NAME ID " are jut indicative, they are not part of input.
Re: Insert a delimiter using sort.
Posted: Thu Oct 16, 2014 4:14 pm
by zprogrammer
Just trying to understand better Should it be like this
Code: Select all
NO# NAME ID
123|AAAA|1
456|BB |10
789|CCC |2
or like this
Code: Select all
NO# NAME ID
123|AAAA|1
456|BB|10
789|CCC|2
Re: Insert a delimiter using sort.
Posted: Thu Oct 16, 2014 7:25 pm
by Manish Arora
Sorry I could not compherhend your question before. Yes, this will work.
Re: Insert a delimiter using sort.
Posted: Thu Oct 16, 2014 10:14 pm
by William Collins
Have a look at SQZ with MID=C'|', you should get it all in one shot.
Code: Select all
INREC BUILD=(1,80,SQZ=(SHIFT=LEFT,MID=C’|’))
That will take the data from position 1, length 80, shift all the data left by removing multiple spaces, and replacing them with a single space, then all single spaces, original or new, will be replaced by |.
Re: Insert a delimiter using sort.
Posted: Thu Oct 16, 2014 10:36 pm
by Manish Arora
Thanks. This worked.
Just reading your previous reply. In case I've to preserve the space between, say, first and surname. Can that be possible with a sort card like this, what consideration should i take. Please suggest.
Re: Insert a delimiter using sort.
Posted: Fri Oct 17, 2014 12:13 am
by William Collins
Yes, you can "protect" a field (or as many as you need) by bounding it with quotes or apostrophes, tell the SQZ that you have done that, then take them off afterwards, with FINDREP.
Re: Insert a delimiter using sort.
Posted: Sat Mar 19, 2016 11:57 am
by Manish Arora
FINDREP is a revolutionary argument! Used it, liked it.