I download a csv file it has ^M for line ending, if I try to view the file with `cat` it give a wired output

Question

Here is what the file look like (you can see that it's comma separated file where the first column of each row is the 'Team' name which is followed preceded by ^M):

Team,League,Games Played,At Bats,Runs,Hits,Doubles,Triples,Home Runs,RBI,Stolen Bases,Caught Stealing,Walks,Strike Outs,Intentional Walks,Hit By Pitch,Sacrifice Hits,Sacrifice Flies,Grounded Into Double Play,Batting Average,On Base Percentage,Slugging Percentage,Salary,Position,x,y,Player Name,Plate Appearences,Singles,Total Bases,Runs Created,ExpWS,Win Shares Percentage,Win Shares Above Bat,Win Shares,Career Win Shares,OnBase + Slugging^MDetroit,AL,86,343,58,116,20,2,6,36,4,3,21,16,0,8,2,4,9,0.338,0.386,0.461,4600000,2B,2.8,4,Placido Polanco,378,88,158,57.26455026,10,0.749,8,15,96,0.846^MNY Mets,NL,87,275,36,66,13,0,11,29,0,1,32,39,7,2,2,2,12,0.24,0.322,0.407,3750000,1B,3.6,3.5,Doug Mientkiewicz,313,42,112,35.06709265,9,0.195,-3,3,66,0.729^MDetroit,AL,87,334,48,107,15,4,5,23,2,3,24,45,3,2,0,1,9,0.32,0.368,0.434,4000000,SS,1.2,4,Carlos Guillen,361,83,145,52.61772853,9,0.418,1,8,79,0.803^MWashington,NL,87,309,38,85,21,2,7,32,0,0,31,30,3,1,2,4,9,0.275,0.339,0.424,7000000,2B,2.8,4,Jose Vidro,347,55,131,43.7925072,9,0.589,4,11,130,0.763^MNY Mets,NL,87,267,31,68,9,4,3,24,6,1,14,43,1,5,5,4,2,0.255,0.3,0.352,7033333,2B,2.8,4,Kazuo Matsui,295,52,94,26.12881356,8,0.294,-1,5,19,0.652^MArizona,NL,88,161,23,37,6,1,4,20,3

how do I make it a normal file, i.e. I don't think the lines are suppose to end with ^M.

Answer 1

^M is 0x15, a.k.a. Carriage Return. You lines end with the DOS-ish CRLF pair instead just the LF used in Unix text files. This can be fixed with tr:

tr -d '\r' <dosfile >unixfile

There are also fromdos (and todos) commands in package tofrodos as well as dos2unix (and unix2dos) commands in package dos2unix. The last two are more complete than just tr, they can also convert from DOS/Windows codepages (or even from Windows UTF-16 to UTF-8).

Answer 2

The likely problem is the file was created on a windows system. To fix:

vi file

<ESC> to ensure you are in command mode

:%s/<CTRL>-V<CTRL>-M/\r/g

:wq

This replaces every instance of ^M with a newline.

P.S. I recommend making a copy of the file before editing in case of problems.