Replace "^M" carriage return control character with newline

README.md

Title: Fix the pesky "^M" carriage return control character using tr Date: 2015-02-04 3:20 Slug: remove-carriage-return-control-character Author: Lee Mendelowitz Tags: python Summary: How to fix the "^M" carriage return control character using tr

When working with Microsoft Excel 2011 on Mac OS X, it will export spreadsheets to comma separated .csv files or tab delimited .txt plain text files using the old Mac OS 9 line endings of a carriage return (\r). When viewed with less or vi, these characters appear as ^M, and the text file appears as one massively long line.

These end of line characters can be handled when importing data using R or Python's pandas library, but it's still a nuisance for other common Unix utilities like GNU's head, tail, less, wc -l, etc.

There are a few ways to convert these line endings using GNU utilities. You can use tr or dos2unix.

Using tr

Mac (\r) to Unix (\n)

To replace those ^M's with Unix line endings, use the tr utility:

tr '\r' '\n' < DC_Metrorail_Ridership.mac.csv > DC_Metrorail_Ridership.unix.csv

This will replace all carriage returns with line feeds.

Windows (\r\n) to Unix (\n)

Windows line endings use carriage return followed by line feed, so to convert Microsoft text files line endings to Unix use the following:

tr -d '\r' < DC_Metrorail_Ridership.windows.csv > DC_Metrorail_Ridership.unix.csv

Using dos2unix or mac2unix

Another option is to use the dos2unix utility. To install on Mac OS X using Homebrew:

brew update
brew install dos2unix

Mac (\r) to Unix (\n)

mac2unix -n DC_Metrorail_Ridership.mac.csv DC_Metrorail_Ridership.unix.csv

Windows (\r\n) to Unix (\n)

dos2unix -n DC_Metrorail_Ridership.windows.csv DC_Metrorail_Ridership.unix.csv

I made a GitHub gist where you can test all of this out. Clone the gist with:

git clone https://gist.github.com/7509ae7cfeaf1072822a.git

DC_Metrorail_Ridership.mac.csv

DC_Metrorail_Ridership.windows.csv

run_all.sh

#!/bin/bash

###########################################################################################
# Convert CSV files with Mac OS 9 line endings (\r) to Unix line endings (\n)

# Use tr
tr '\r' '\n' < DC_Metrorail_Ridership.mac.csv > unix1.csv

# Use mac2unix
mac2unix -n DC_Metrorail_Ridership.mac.csv unix2.csv

#############################################################################
# Convert CSV files with Windows line endings (\r\n) to Unix line endings (\n)

# Use tr
tr -d '\r' < DC_Metrorail_Ridership.windows.csv > unix3.csv

# Use dos2unix
dos2unix -n DC_Metrorail_Ridership.windows.csv unix4.csv

# Check that all of the csv files are identical
md5sum unix*.csv

run_using_dos2unix.sh

#!/bin/bash

###########################################################################################
# Convert CSV files with Mac OS 9 line endings (\r) to Unix line endings (\n)

# Use mac2unix
mac2unix -n DC_Metrorail_Ridership.mac.csv unix2.csv

#############################################################################
# Convert CSV files with Windows line endings (\r\n) to Unix line endings (\n)

# Use dos2unix
dos2unix -n DC_Metrorail_Ridership.windows.csv unix4.csv

# Check that all of the csv files are identical
md5sum unix*.csv

run_using_tr.sh

#!/bin/bash

###########################################################################################
# Convert CSV files with Mac OS 9 line endings (\r) to Unix line endings (\n)

# Use tr
tr '\r' '\n' < DC_Metrorail_Ridership.mac.csv > unix1.csv

#############################################################################
# Convert CSV files with Windows line endings (\r\n) to Unix line endings (\n)

# Use tr
tr -d '\r' < DC_Metrorail_Ridership.windows.csv > unix3.csv

# Check that all of the csv files are identical
md5sum unix*.csv