Windows file in Unix. Careful of ^M and how to replace them

I thought many people know this but sometimes, it is taken for granted and can cause hours of misery.

If you save a text file in windows, you get ^M as new line. In unix, this can cause problems. I was running a script to dump a query into csv and couldn’t quite work out why it doesn’t work.

cat test.txt | while read x; do mysql -uroot --password= dbname -B -e "select name,suburb,address_1,address_2,city,postcode from venue_list where suburb='$x'" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' >> dump.csv; done;

the dump keeps producing nothing… I kept looking at the script…. soon I turned on mysql log and saw:

59 Connect root@localhost on dbname
59 Query select name,suburb,address_1,address_2,city,
postcode from venue_list where suburb='Mt Ommaney^M'
59 Quit
60 Connect root@localhost on dbname
60 Query select name,suburb,address_1,address_2,city,
postcode from venue_list where suburb='Glenelg^M'
60 Quit
61 Connect root@localhost on dbname
61 Query select name,suburb,address_1,address_2,city,
postcode from venue_list where suburb='Palmerston^M'
....

evil ^M!! There are a number of ways to fix this. In vim, “:%s/^M$//g” (need to type ^V^M to get ^M) or

dos2unix test.txt

now everything works.

Like it.? Share it:
Tags:

Comments are closed.