Eliminating Carriage return Line feed characters

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
jpderycker
Premium Member
Premium Member
Posts: 3
Joined: Tue Aug 13, 2013 11:44 am
Location: Jacksonville

Eliminating Carriage return Line feed characters

Post by jpderycker »

Hello all DSXchange members,

Need help with removing CR LR characters in a sequential file causing our production job to fail. I have looked at the many posts in the forum and used all the different solutions proposed ... but they all failed.

I have created a sequential file from an ODBC extract. One of the description fields may contain one or more CR LF characters. When I use this sequential fil and try to remove those characters in the transform stage, the job fails.

I have tried all the following alternatives with no success:

Convert(CHAR(13):CHAR(10),"",SRC01_TO_TFM01.OLADES)
Convert(CHAR(13):CHAR(10),' ',SRC01_TO_TFM01.OLADES)

Ereplace(Ereplace(SRC01_TO_TFM01.OLADES,Char(13),""),Char(10),"")

TRIM(TRIM(SRC01_TO_TFM01.OLADES,CHAR(13),"A"),CHAR(10),"A")

What seems the be problem? Anyone had already this issue? Thanks for your help.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I for one would be curious what "all the different solutions" are that you tried and also what you mean by "the job fails"... specifically what about it fails, what error messages are you seeing?

If the issue is reading the file, it's too late to try to handle them in the job and the typical Server solution would be to set the 'Contains Terminators' property for that column in the Sequential File stage. Is that one of the solutions you tried? May also need more details about the file itself but we can wait on that.
-craig

"You can never have too many knives" -- Logan Nine Fingers
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

Also - are you sure that those are the actual Hex values of the characters causing the problem? What shows up when you look at the file with a Hex editor?
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You say that the job fails in production. Are you implying that the same job does not fail in dev or test? If so, what is different about the production environment?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
jpderycker
Premium Member
Premium Member
Posts: 3
Joined: Tue Aug 13, 2013 11:44 am
Location: Jacksonville

Post by jpderycker »

Thank you Craig, Andy, rav.wurlod for your replies.

About the production statement, yes it is also happening in DEV and QA. Sorry for the confusion.

About all the solutions, I meant to say the different 'functions' I tried with no success. Forgive my frenglish! ;-)

About the error message, this is what I am getting:
TFM01_RO_ORDRL..SRC01_ROORDRL.SRC01_TO_TFM01: read_delimited() - invalid quotes, row 35583 column OLADES = "INCLUDES A SUR3 AT 9CPM"

About the HEX values, I confirm that CR LF are showing up multiple times in the description field as well as at the end of each row.

I believe the problem is what you are mentioning Craig ...
" If the issue is reading the file, it's too late to try to handle them in the job and the typical Server solution would be to set the 'Contains Terminators' property for that column in the Sequential File stage."

I need to handle this at the extract job.

About "Is that one of the solutions you tried?", NO! I will do so today and provide you with the result.

About "May also need more details about the file itself but we can wait on that." ... sounds good.

Thank again for your help and I will keep you posted.

JP

PS: I use a lot DSXchange but never posted anything. This is a first! Thanks!
jpderycker
Premium Member
Premium Member
Posts: 3
Joined: Tue Aug 13, 2013 11:44 am
Location: Jacksonville

Post by jpderycker »

Hello,

I handled the description field at the extract job level by adding the convert function in the transform stage Convert(CHAR(13):CHAR(10),"",SRC01_TO_TFM01.OLADES).

It worked like a charm eliminating CR LF characters.

Will be marking this post as resolved.

Thank you again for everyone's help!

Much appreciated.

JP
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

:idea: Just make sure you understand it is removing all individual CR and LF characters in the field - not just where they are there as a pair of characters. While this may be perfectly fine, for the record to handle eliminating pairs of them, use the EReplace function rather than Convert.
-craig

"You can never have too many knives" -- Logan Nine Fingers
jpderycker
Premium Member
Premium Member
Posts: 3
Joined: Tue Aug 13, 2013 11:44 am
Location: Jacksonville

Post by jpderycker »

Thank you Craig for your advice. This makes sense. Much appreciated.
Post Reply