Removing Embedded Carriage Returns

dstager · Post by **dstager** » Tue Oct 14, 2008 2:52 pm

DS Gurus,

I am having trouble formatting a sequential file that seems to be scattered with embedded Carriage Returns within the file.

I am reading from source (SQL Server) via ODBC and transferring a column (Varchar 1000) into a sequential file to be loaded into a Mainframe table.

There are some records with 'carrriage returns' that is causing the grief. I would like to REMOVE the carriage returns without affecting the structure of the file (ie - I tried to remove ^M manually - via Unix vi), but it affects the structure of the seq file.

I am creating a fixed width file with Unix delimiter.

Help is appreciated.

prasad111 · Post by **prasad111** » Tue Oct 14, 2008 3:37 pm

After writting into a file, you can try to execute
dos2unix <file_name>
that should take care of your problem.

prasad111 · Post by **prasad111** » Tue Oct 14, 2008 3:38 pm

After writting into a file, you can try to execute
dos2unix <file_name>
that should take care of your problem.

ArndW · Post by **ArndW** » Wed Oct 15, 2008 1:35 am

In your DataStage job use "CONVERT(Char(13),'',In.StringColumn)" to remove internal Control-M characters.

dstager · Post by **dstager** » Wed Oct 15, 2008 6:49 am

Thanks!

The problem is resolved - I used the convert (Char(13)) method.

Magesh_bala · Post by **Magesh_bala** » Thu Oct 16, 2008 8:37 am

Hi All,

I have the same problem, I used to create the sequential file form the DB2 database, But some times when i create the file it is created with CR/LF, SO when i try to read the file, The datastage job is aborted.

I have to check the entire file before i read it, Is there any option to check the entire file in datastage to find the carriage reture.

Your help is highly appriciated.

Thanks and Regards
N.Magesh

Magesh_bala · Post by **Magesh_bala** » Thu Oct 16, 2008 8:39 am

Hi All,

I have the same problem, I used to create the sequential file form the DB2 database, But some times when i create the file it is created with CR/LF, SO when i try to read the file, The datastage job is aborted.

I have to check the entire file before i read it, Is there any option to check the entire file in datastage to find the carriage reture.

Your help is highly appriciated.

Thanks and Regards
N.Magesh

ray.wurlod · Post by **ray.wurlod** » Thu Oct 16, 2008 2:13 pm

No.

But you can write a script to do that and invoke the script from DataStage.

Magesh_bala · Post by **Magesh_bala** » Fri Oct 17, 2008 1:21 am

ray.wurlod wrote:No.

But you can write a script to do that and invoke the script from DataStage. ...

Can you please help me in writing the script?

ArndW · Post by **ArndW** » Fri Oct 17, 2008 1:37 am

Try and google the keywords "tr command remove carriage return", that will answer your scripting questions.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 17, 2008 1:51 am

Magesh_bala wrote:Can you please help me in writing the script?

That comes under the heading of "consulting" which is how I make my living. Contact me off line if you wish to engage me professionally.