head and cat don't recognize Microsoft Excel text files

Question

I have a large .xls file with 53760 rows and 19 columns. I saved it as Tab Delimited Text in order to pre-process it through command line tools and load it into R later. However, when I run

head myfile.txt

my terminal shows all lines at once, like the

cat myfile.txt

command is supposed to do. But if I run

cat myfile.txt | wc -l

the answer I get is 0. More interestingly, R can read my data, without the aid of any extra package, and identify all 53760 rows and 19 columns. I suppose it is an Excel problem, but I can't figure out what is it.

I'm using Mac OS 10.9.1, Microsoft Excel for Mac 2011, bash 4.2 and iTerm 2 1.0.0.20130622

Answer 1

On Linux and Mac OS, go into vi, perform the following substitution, and save out the file again, and that should work for you,

$ vi myfile.txt
:%s/^V^M/^V^M/g
:wq

where ^V^M means type Ctrl+V, then Ctrl+M, and terminate each line with a return.

This should put normal UNIX line terminator characters at the end of the Excel comma separated value lines. You could even create a vi script to automate it if you do it a lot, then call that vi script from a bash script, and hide all this system incongruity away, and call it dos2unix. :-)

To create the vim script in dos2unix.vim, do the following:

1. Create the script by

   $ vi -w dos2unix.vim myfile.txt

2. Carefully type in the 3 line sequence as mentioned above.

3. Now apply that scrip to any file with

   $ vi -s dos2unix.vim anyfile.txt

And wrapping that all up in a bash script or even an alias, as in, is up to you

alias dos2unix='vi -s ~/.vimscripts/dos2unix.vim '

To be able to do it like it was builtin, because it now will be - just create a folder called, ~/.vimscripts, and put the above created script file there.