Trimming 'Carriage Return' from data in a column

[yaminids]

Hello friends,

I am trying to process 'Supplier' data in a Server job. The job is aborted after processing some rows with the following error:

SUPP_DATA.. nls_read_delimited() - invalid quotes, row 5879 column SUPP_WR_HOUSE_ADDR = "17233 County Rd 6
"

After carefully looking through the data, I found out that there is a 'Carriage Return' at the end of the column which is throwing rest of the data into subsequent rows.

Can anyone help me with using the 'Trim' function to remove the 'Carriage Return' from the data?

Thanks a lot in advance
-Yamini

[ray.wurlod]

Are you SURE it's a Carriage Return?
Could it be Line Feed?
Could it be a Windows line terminator (both)?
You can represent these by Char(13) and Char(10) in an expression such as

Code: Select all

Convert(Char(13):Char(10),"",InLink.TheString)

[ray.wurlod]

Slightly more efficient, so that you're not evaluating the Char functions for every row, would be to initialize two stage variables, say svChar10 and svChar13. The expression then becomes

Code: Select all

Convert(svChar10:svChar13,"",InLink.TheString)

[chulett]

But... at this point... isn't it 'too late' for corrections of this nature? These kind of things need to be done before writing the file out. Right now the OP can't even read the file in properly.

First course of action (in my mind) would be to get whomever supplied you with this file to correct it - build something that is properly quoted. If you are building it in a previous step then perhaps you can do something along the lines of what Ray suggested.

Otherwise, a Filter command in the Sequential File stage to do this on the fly as it comes in? In UNIX a tr might be used, not sure what you would do in a Windows environment.

[yaminids]

Ray,

I am sure that it is 'Carriage Return'.
Thank you for your help
-Yamini

[vinaymanchinila]

Hi,
I had a similar issue with a cloumn in the source data which had "," in the description field and it was throwing all the consecutive records into other columns. I used trim(Change(ToXfm.MACHINE_DESC,","," ")) and it worked.
Hope this helps.
vinay