Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
2  
IIRC then both head and wc count lines and need their expected end of line marker. That marker is sadly different for different systems (e.g. unix, windows and thge older apple OS (the one before OS X) used \10, \10\13 and \13 as codes (newline and return combinations). Can you check which format is in your file? –  Hennes Jan 9 at 11:59
    
Your suggestion made sense. I changed from Excel to LibreOffice, exported my file as .csv and it is working now. Thanks! –  grandeabobora Jan 9 at 12:16

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.