removing ^M from a csv file

Question

I have a problem when I try to remove ^M from a csv

When I type vim or vi file.csv, I get

A, TK,2015-04-06,14.4^M,14.7,10.0,0.0,54.0^M,13.3^M,135.0^M,33.8
B, NV,2015-04-06,14.4^M,14.7,5.4,0.0,47.0^M,14.8^M,97.0^M,31.3

I have tried with

tr -d '^M' < file.csv > file2.csv

But it doesn't remove, also with sed.

How did you enter ^M ? Did you use char ^ followed by char M (wrong), or did you use CTRL-V CTRL-M (correct) ? — Walter A, Apr 06 '15 at 15:25
Here is the answer which you are looking for http://stackoverflow.com/questions/811193/how-to-convert-the-m-linebreak-to-normal-linebreak-in-a-file-opened-in-vim — pkacprzak, Apr 06 '15 at 16:07

Idriss Neumann · Answer 1 · 2015-04-06T15:14:21.007

13

You could use dos2unix command which is provided to do that.

Using GNU/sed just for fun :

sed -i -e "s/\r//g" file

Using tr :

tr -d '\r' <file1 >file2

edited Apr 06 '15 at 15:14

answered Apr 06 '15 at 15:00

Idriss Neumann

3,760
2
23
32

score 4 · Answer 2 · answered Apr 06 '15 at 14:57

4

You can try:

perl -pE 's/(\^M|\r)//g' < file >file2

should remove

the literal ^M - sequence of two characters ^ and M
and the ^M as \r character.

answered Apr 06 '15 at 14:57

clt60

62,119
17
107
194

I used `perl -pE 's/(\^M|\r)//g' /var/tmp/screwed_csv_file.csv` and then i have a correct CSV file. – Feb 21 '16 at 16:03
After doing that, i can use http://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html#option_mysqlimport_columns and `LOAD DATA`. Does it not mean MySQL has BUG? they should document it or should be treated as BUG? it took 4 hours for me to find out. – Feb 21 '16 at 16:04

score 2 · Answer 3 · answered Apr 06 '15 at 16:48

2

Where did you get that file from? It looks like an old System X file from a Mac. The old pre-OSX OS used <CR> as line endings. Unix uses <LF>, and Windows/DOS uses <CRLF>.

Do you have dos2unix. This program can convert line endings from Unix/Linux, DOS/Windows, or System X Macs to any of the formats you want. In your file, I take it you need to convert the ^M which are Control Ms and not a Caret-M to NL characters.

answered Apr 06 '15 at 16:48

David W.

105,218
39
216
337

1

By the way, to do this you need to put `dos2unix` into Mac conversion mode: `dos2unix -c mac ` – jonahb Feb 15 '17 at 23:58

removing ^M from a csv file

3 Answers3

Linked