What's a good way to filter a text file to remove empty lines?

Question

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!

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. — tongpu, Nov 28 '12 at 18:12

derobert · Accepted Answer · 2012-11-28 17:54:40Z

11

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.

edited Nov 28 '12 at 17:54

answered Nov 28 '12 at 17:10

derobert

81.1k9 gold badges183 silver badges237 bronze badges

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

Onturenio · Accepted Answer · 2012-11-28 22:34:48Z

8

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.

answered Nov 28 '12 at 22:34

Onturenio

3353 silver badges10 bronze badges

add a comment |

Stéphane Chazelas · Accepted Answer · 2012-11-29 07:25:00Z

6

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.

edited Nov 29 '12 at 7:25

answered Nov 28 '12 at 18:08

Stéphane Chazelas

338k58 gold badges661 silver badges1040 bronze badges

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. – Stéphane 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 '14 at 17:53

add a comment |

Joseph R. · Accepted Answer · 2012-11-28 23:43:08Z

5

Here's a Perl one-liner for it:

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

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

edited Nov 28 '12 at 23:43

answered Nov 28 '12 at 17:10

Joseph R.

30.1k3 gold badges78 silver badges121 bronze badges

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

Stéphane Chazelas · Accepted Answer · 2012-11-29 07:18:01Z

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"}'

pitosalas · Accepted Answer · 2012-11-28 20:05:32Z

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!

glenn jackman · Accepted Answer · 2012-11-28 19:47:42Z

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"

Community · Accepted Answer · 2017-05-23 12:40:02Z

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

Stéphane Chazelas · Accepted Answer · 2012-11-29 10:15:59Z

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.

Stéphane Chazelas · Accepted Answer · 2012-11-29 07:20:27Z

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

Marcelo Augusto · Accepted Answer · 2016-03-05 01:25:47Z

There is an ever shorter way of removing empty lines in AWK:

awk 'NF' file

But to get the output you want, all is needed is a simple one liner:

awk 'NF {printf("%s ", $0); i++;} !(i % 2) {printf("\n");}' file

Explanation

In AWK, an empty line means the row/record has no fields, that is, the NF (Number of Fields) variable is zero. The one liner above will only execute when NF > 0, printing all lines, but the empty ones.

The i++ is the non-empty lines counter.

The !(i % 2) is used in order to print two consecutive non-empty lines in the way of your desired output, that is, every time a multiple of 2 is found, the modulo statement !(i % 2) yields 1, what terminates the concatenation of two non-empty lines.

My bad! Sorry. I didn't read his whole question and the desired output. The reply is fixed now. Thanks. :-) — Marcelo Augusto, Mar 5 '16 at 1:27

Steven Penny · Accepted Answer · 2016-04-13 02:13:55Z

0

You can use Vim in Ex mode:

ex -sc v/./d -cx b.csv

v/./ find empty lines
d delete
x save and close

edited Apr 13 '16 at 2:13

answered Apr 11 '16 at 1:08

Steven Penny

1

add a comment |

Stack Exchange Network

current community

your communities

more stack exchange communities

What's a good way to filter a text file to remove empty lines?

12 Answers 12

Your Answer

Not the answer you're looking for? Browse other questions tagged shell text-processing sed awk or ask your own question.

Linked

Hot Network Questions

What's a good way to filter a text file to remove empty lines?

12 Answers 12

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged shell text-processing sed awk or ask your own question.

Linked

Related

Hot Network Questions