we are getting input values encapsulated in quoation marks in fixed length CSV file.
SAMPLE:
002,"ADM","ADDITIONAL,MANAGEMENT<SP><SP><SP><SP>", "ADMN"
002: begining of the record body
First value: "ADM"
Second value: "ADDITIONAL, MANAGEMENT<SP><SP><SP><SP><SP>"
Third value: "ADMN"
One of the input value contrains extra comma (part of the data), and have some blank spaces before the quotation ends. To take care of extra data, they added quotation around all the three fields, which is working well. but it doesnt remove blank spaces I have tried TRIMLEFT() and TRIMB() but it did not work, and XML contains spaces after value: MANAGEMENT (SEE THE SECOND INPUT VALUE).
Please let me know if there is any function in TX which would take care of spaces in between quotation marks.
please provide example.
thanks!
blank space within quotation
where do I use substitute function: in a TYPE TREE or in a MAPPED FIELD of Map Designer?janhess wrote:use SUBSTITUTE(" ","")
I assum in a MAP Designer, correct me if i am wrong. In a map designer, I mapped a field to output card, field is: Country_Name Field:In1 (every single value of this field contains spaces, and i want to get rid of space). how to use SUSBSTITUTE with above field?
please provide example.
thankS!
try this if your field has other spaces in it
="<ANYQUOTE>" + trimright(substitute("""United State's of America ""","<QUOTE>","")) + "<ANYQUOTE>"
In your case replace the text with your field.
="<ANYQUOTE>" + trimright(substitute("""United State's of America ""","<QUOTE>","")) + "<ANYQUOTE>"
In your case replace the text with your field.
Last edited by janhess on Fri Sep 28, 2007 2:43 am, edited 1 time in total.
You could always rate his post high and set the thread to "resolved" and give him a boost in points. Of course, this will put his farther ahead of me, but I like the competition.zaino22 wrote:Big thanks to janhess for a qwick reply.janhess wrote:in map rule.
=substitute(Country_Name Field:In1," ","")
THANKS!!
ps: If there were any spaces in the field that you wanted, like let's say it was "ADDITIONAL MANAGEMENT", and you wanted the space between additional and management, then jahness's last post using the "TRIMRIGHT" function is what you want. It'll trim extra characters off the right side of the field, just as TRIMLEFT will trim...oh, I'm sure you get it.
thanks, it worked
Thanks, it worked. I will give you some pionts.
thanks again!
thanks again!
rep wrote:You could always rate his post high and set the thread to "resolved" and give him a boost in points. Of course, this will put his farther ahead of me, but I like the competition.zaino22 wrote:Big thanks to janhess for a qwick reply.janhess wrote:in map rule.
=substitute(Country_Name Field:In1," ","")
THANKS!!
ps: If there were any spaces in the field that you wanted, like let's say it was "ADDITIONAL MANAGEMENT", and you wanted the space between additional and management, then jahness's last post using the "TRIMRIGHT" function is what you want. It'll trim extra characters off the right side of the field, just as TRIMLEFT will trim...oh, I'm sure you get it.