Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have a .csv file (on a mac) that has a bunch of empty lines, e.g.:

"1", "2", "lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum 

lorem ipsum ","2","3","4"
"1", "2", "lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum 

lorem ipsum ","2","3","4"

Which I want to convert to:

"1", "2", "lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum ","2","3","4"
"1", "2", "lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum  lorem ipsum ","2","3","4"

I know there must be a one liner but I don't know awk or sed. Any tips greatly appreciated!

share|improve this question
    
According to that sample you actually want to remove embedded line breaks from the fields. Is that correct? In other words, there are 6 input lines and should be 2 output lines? –  manatwork Nov 28 '12 at 17:14
    
Yes, that's exactly what I am trying to get rid of: embedded newlines inside of a quoted string. –  pitosalas Nov 28 '12 at 18:01
    
So what you need is something that removes newlines inside quotes. That's going to be a little more complicated, because you need multiline regex. –  fotomonster Nov 28 '12 at 18:12
add comment

10 Answers

You can use grep's -v (invert match) mode to do this:

grep -v '^$' old-file.csv > new-file.csv

Note that those need to be different files, because of how shell redirects work. The output file is opened (and emptied) before the input file is read. If you have moreutils (not by default on Mac OS X), you can use sponge to work around this:

grep -v '^$' file.csv | sponge file.csv

But of course, then you have a harder time going back if something goes wrong.

If you "blank lines" actually may contain spaces (it sounds like they do), then you can use this instead:

egrep -v '^[[:space:]]*$' old-file.csv > new-file.csv

That will ignore blank lines as well as lines containing only whitespace. You can of course do the same sponge transformation on it.

share|improve this answer
    
Thanks.... Didn't delete any empty lines...Maybe the ^$ is not matching? But the lines are empty to the best of my knowledge. Remember this is a cdv created by excel on a mac... Does that say anything? (Don't run away screaming because I said Excel :) –  pitosalas Nov 28 '12 at 17:16
    
@pitosalas They're probably not empty lines. Try changing it to egrep -v '^[[:space:]]*$' ... note grep -> egrep and the weird new pattern –  derobert Nov 28 '12 at 17:28
    
Didnt work. Deleted a bunch of double quotes and made a mess... –  pitosalas Nov 28 '12 at 17:55
    
@pitosalas I'm unsure how it'd delete double quotes. It should only be able to delete whitespace. And indeed, that's what it does when I test it on the example data you posted... –  derobert Nov 28 '12 at 17:59
    
@pitosalas could you check if either of these commands spits out something that looks reasonable (as opposed to gibberish): iconv -f utf16le file.csv | head or iconv -f utf16be file.csv | head –  derobert Nov 28 '12 at 18:01
show 2 more comments

The easiest option is just grep .. Here, the dot means "match anything", so if the line is empty, it is not matched. Otherwhise it prints the whole line as is.

share|improve this answer
add comment

Here's a Perl one-liner for it:

perl -pi -e 's/^\s*\n//' yourfile

EDIT: Improved code based on ruakh's comments below.

share|improve this answer
1  
Or perl -ni -e '/./ and print' yourfile –  derobert Nov 28 '12 at 17:13
1  
@peterph $ is an anchor (i.e. zero-width) so it excludes the newline. As to the superfluous space, it's the reason I added the /x I didn't want Perl to try interpolating `$\` into the regex –  Joseph R. Nov 28 '12 at 17:37
1  
You don't need the $, given that you have the \n. (Alternatively -- you don't need the \n, given that you have the \s* and the $; but I think s/^\s*\n// makes it clearer that the newline is removed.) You also don't need the /m; it has no effect on this command. And once you get rid of the $ and the space, you won't need the /x. –  ruakh Nov 28 '12 at 21:20
1  
@JosephR.: The \n itself can be removed; what you can't do is remove both the $ and the \n. So s/^\s*// would have the problem you describe, but s/^\s*$// would be fine, because of the \s* and the $. (Do you see what I mean?) –  ruakh Nov 28 '12 at 23:34
1  
@JosephR.: What happens is, $ can match before a newline (provided that either the /m flag is enabled, or the newline is the very last character of the string, or both), but it can also match the end of the string. For example, "abc" =~ m/^abc$/ is true. In the case of \s*$, the \s* is greedy enough to eat up the newline, and then the $ matches the end-of-string. (But I think s/^\s*\n// is clearer, anyway, so your answer is just fine as it is now.) –  ruakh Nov 28 '12 at 23:53
show 9 more comments

To remove empty lines, in place, with ksh93:

sed '/./!d' file 1<>; file

The <>; redirection operator is specific to ksh93 and is the same as the standard <> operator except that ksh truncates the file after the command has terminated.

sed '/./!d' is a convoluted way to write grep ., but unfortunately GNU grep at least complains if its stdout points to the same file as its stdin. You'd say one could write:

grep . file | cat 1<>; file

But unfortunately, there's a bug in ksh93 (at least my version (93u+)), in that the file seems to be truncated to zero length in that case.

grep . file | { cat; } 1<>; file

Seems to work around that bug, but now, it's far more convoluted than the sed command.

share|improve this answer
    
Please combine your answers into one well formatted entry with a quick guide for when each solution should be employed. The different approaches to different problems all jumbled together in floating answers has make this question a bit of a disaster to read. –  Caleb Dec 5 '12 at 10:21
    
@Caleb, It all boils down to the question being very unclear, so all of everybody's answers are for different interpretations of the question. For each answer, I tried to say which question it tries to answer. –  Stephane Chazelas Dec 5 '12 at 11:00
    
Just FYI: Tried awk '/./' file 1<>; file which worked. To me, that's even clearer than sed '/./!d' –  grebneke Mar 6 at 17:53
add comment
up vote 2 down vote accepted

I know this would have been easier if I gave the file, but unfortunately it contained confidential info that I couldn't share. In the meanwhile I wrote me a ruby script that seemed to do the trick:

require 'csv'
c = CSV.open("outfile1.csv", "w")
CSV.foreach("data.csv", :encoding => 'windows-1251:utf-8') do |row|
  row = row.map { |a| a.class == String ? a.gsub(/\r/, '') : a}
  c << row
end
c.close

Thanks everyone for helping!

share|improve this answer
add comment

Based on the clarification in the comments to your question, something like:

awk -v RS= -v ORS= 1

may do what you want.

An empty record separator is a special case that tells awk that records are to be paragraphs (separated by sequences of empty lines). Setting the output record separator to the empty string as well means that the content of those paragraphs (without the separators) are to be concatenated. 1 is just a true condition to print every record.

That would however omit the trailing newline, so you could do:

awk -v RS= -v ORS= '1;END{if (NR) printf "\n"}'
share|improve this answer
add comment
awk '
    length == 0 {next} 
    /^[^"]/ && /"$/ {print; next} 
    {printf("%s", $0)}
' filename

produces

"1", "2", "lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum ","2","3","4"
"1", "2", "lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum lorem ipsum ","2","3","4"
share|improve this answer
add comment

I found an idea for a possible solution on stackoverflow.

sed -i ':a;N;$!ba;s/[^"]\n\s*\n/ /g' file.csv

You should probably backup your csv file before testing it, but at least for the example you provided it works flawlessly.

A good explanation about the inner workings of this expression is offered at the answer, I just edited it to look for lines that do not end with a " ([^"]\n).

share|improve this answer
add comment

It looks like in effect that you want more than removing empty lines, but remove every sequence of 2 or more newline characters.

Which you could do with perl:

perl -0777 -pe 's/\n{2,}//gs' file

You could also use use perl's -i flag to edit the files in place.

perl -0777 -pi -e 's/\n{2,}//gs' file1 file2...
share|improve this answer
add comment

If, from your own response, you want to remove newline characters contained inside quoted strings, you could do:

 perl -0777 -pe 's/".*?"/$_=$&;s:\n::g;$_/gse'

You could also use use perl's -i flag to edit the files in place.

 perl -0777 -pe 's/".*?"/$_=$&;s:\n::g;$_/gse' file1 file2...

Or with GNU awk:

 awk -v RS=\" 'NR%2==0 {gsub("\n","")}; {printf "%s", $0 RT}'

or:

 awk -vRS=\" '1-NR%2{gsub("\n","")}{ORS=RT}1'

(if you're competing for the shortest one)

Note that those assume that there are no escaped double quote characters in the input.

share|improve this answer
add comment

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.