Excel saves tab delimited files without newline (UNIX/Mac os X)

Question

This is a common issue I have and my solution is a bit brash. So I'm looking for a quick fix and explanation of the problem.

The problem is that when I decide to save a spreadsheet in excel (mac 2011) as a tab delimited file it seems to do it perfectly fine. Until I try to parse the file line by line using Perl. For some reason it slurps the whole document in one line.

My brutish solution is to open the file in a web browser and copy and paste the information into the tab delimited file in TextEdit (I never use rich text format). I tried introducing a newline in the end of the file before doing this fix and it does not resolve the issue.

What's going on here? An explanation would be appreciated.

~Thanks!~

Answer 1

The problem is the actual character codes that define new lines on different systems. Windows systems commonly use a CarriageReturn+LineFeed (CRLF) and *NIX systems use only a LineFeed (LF).

These characters can be represented in RegEx as \r\n or \n (respectively).

Sometimes, to hash through a text file, you need to parse New Line characters. Try this for DOS-to-UNIX in perl:

perl -pi -e 's/\r\n/\n/g' input.file

or, for UNIX-to-DOS using sed:

$ sed 's/$'"/`echo \\\r`/" input.txt > output.txt

or, for DOS-to-UNIX using sed:

$ sed 's/^M$//' input.txt > output.txt

Answer 2

Found a pretty simple solution to this. Copy data from Excel to clipboard, paste it into a google spreadsheet. Download google spreadsheet file as a 'tab-separated values .tsv'. This gets around the problem and you have tab delimiters with an end of line for each line.