How to handle Control M Character

kool78 · Post by **kool78** » Mon Jan 29, 2007 1:06 pm

HI Guys,

Did anyone come across handling Control M charaters .

I have a .csv file and when i look into it, it has a CntrlM(^M) character at the end of each record..

i tried different delimiters for cntl m but it didnt work out....is ther any work around for this issue..

thanks
shisheer

chulett · Post by **chulett** » Mon Jan 29, 2007 1:08 pm

^M is a <CR> and indicates DOS style record terminators.

pavankvk · Post by **pavankvk** » Mon Jan 29, 2007 1:16 pm

kool78 wrote:HI Guys,

Did anyone come across handling Control M charaters .

I have a .csv file and when i look into it, it has a CntrlM(^M) character at the end of each record..

i tried different delimiters for cntl m but it didnt work out....is ther any work around for this issue..

thanks
shisheer

u can clean the file before processing using sed command.

narasimha · Post by **narasimha** » Mon Jan 29, 2007 1:20 pm

Or vi the file and use substitution to remove all the ^M's

Code: Select all

:%s/^M//g

ray.wurlod · Post by **ray.wurlod** » Mon Jan 29, 2007 4:04 pm

... or set the Final Delimiter property to \R

... or create a dummy Char(1) column and set it's "drop on import" property to True

There's always more than one way to do something in DataStage.

kool78 · Post by **kool78** » Mon Jan 29, 2007 4:30 pm

Hi all,

Thanks for the replies...

I dont want to process the file before...

but i was trying the options mentioned by Ray...i used \R but still im unable to view the data....its throwing the error as delimiter for hte filed(last filed) is not found..

it would be grt if can you throw more light on this ray...

thanks

ray.wurlod · Post by **ray.wurlod** » Mon Jan 29, 2007 4:42 pm

Which of the methods did you try? The second (create an extra column) will fail with the message you got, because there is no actual delimiter character between the last column and the \r character.

What is the data type of the last field in your data record?

Did you try setting the final delimiter property to \r ? If it won't take more than one printable character, try using 013 (the decimal value of the ASCII code point).

kool78 · Post by **kool78** » Mon Jan 29, 2007 4:47 pm

ray.wurlod wrote:Which of the methods did you try? The second (create an extra column) will fail with the message you got, because there is no actual delimiter character between the last column and the \r character.
...

Hi ray

I tried the first method..

have set the Final Delimiter property to \R but didnt work out..

one morething i observed is ..when i used record delimiter strins as : DOS Format..it was working fine..

but im not sure whether if the source file is ftp'd from windows to unix..does it have unix newline or dos format...

thanks

ray.wurlod · Post by **ray.wurlod** » Mon Jan 29, 2007 4:54 pm

Last time I looked (7.5.1), "DOS format" was not available for record delimiter when running on UNIX platform. Otherwise I would have cited that as the appropriate technique.

The only way to know what's there is to look at the actual characters. If you have a hex editor, such as UltraEdit, use that. Otherwise you can use the UNIX od command.

Code: Select all

head -3 filename | od -xc

chulett · Post by **chulett** » Mon Jan 29, 2007 5:26 pm

ray.wurlod wrote:Last time I looked (7.5.1), "DOS format" was not available for record delimiter when running on UNIX platform. Otherwise I would have cited that as the appropriate technique.

Wasn't that what I quoted from the Developer's Guide?

Th3Und34D- · Post by **Th3Und34D-** » Mon Apr 02, 2007 4:26 am

Hi ray

I tried the first method..

have set the Final Delimiter property to \R but didnt work out..

one morething i observed is ..when i used record delimiter strins as : DOS Format..it was working fine..

but im not sure whether if the source file is ftp'd from windows to unix..does it have unix newline or dos format...

I too faced similar problem.. I think it is due to the file FTP from windows to unix server.

While ftp'ing the file use command "ascii" (by default it is "bin" - binary), and then ftp the file... Use SSH Clients and ftp the file in ASCII format.. This should solve the problem..

Regards,
Asish

chulett · Post by **chulett** » Mon Apr 02, 2007 6:29 am

Th3Und34D- wrote:I too faced similar problem.. I think it is due to the file FTP from windows to unix server.

Typically - yes. Or sometimes people map a UNIX drive in Windows and just drag and drop files.

ps. That won't "solve" the problem, per se. To solve it one would need to be able to read and handle DOS files properly. An 'ascii' ftp transfer would eliminate the need to solve the problem.

smrutiranjannayak · Post by **smrutiranjannayak** » Mon Apr 02, 2007 8:17 am

Hi ,

you can try the following option :

While saving the file in Unix ( i guess u r using this platform) just type the below command

%s/^M//

Cheers

ray.wurlod · Post by **ray.wurlod** » Mon Apr 02, 2007 9:11 am

Investigate the Record Delimiter String property (rather than the Record Delimiter property). Specify as \r\n

Th3Und34D- · Post by **Th3Und34D-** » Mon Apr 02, 2007 11:26 pm

I got a solution. It solved my purpose of removing ^M.

Use Iconv("Column where ^M is present", "MCP")

With MCP option ur problem will b solved ... :D

Regards,
Asish Koyya

DSXchange

How to handle Control M Character

How to handle Control M Character

Re: How to handle Control M Character

You can try this

Haaa... Finally i got a resolution for this.