DSXchange

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.

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.

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?

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?

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!

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

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.

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