0

I have a CSV file with multiple columns, comma ", " separated and numbers of lines.
some lines have empty one or two fields "Columns". How I can identify in a separate file and/or remove those line with one or more empty columns with Awk command.

example

aaaa,bbbb,cccc,dddd,
,bbbb,cccc,dddd,
aaaa,,cccc,dddd,
aaaa,,,dddd,
,,,dddd,

The ", ," means empty columns. I tried this command but it did not work!

awk -F, '$1,4~/^$/' filename 

The expected output should be only :

aaaa, bbbb, cccc, dddd,

2
  • 2
    can you add expected output for the sample given? can you particularly clarify is trailing comma in a line mean empty field?
    – Sundeep
    Commented Nov 24, 2016 at 4:19
  • 2
    Seems like the last field in all those lines is empty. Is an empty line to be considered as a line with no field or a line with one empty field? Commented Aug 12, 2023 at 17:15

5 Answers 5

1
awk -F, '{for(i=1;i<=NF;i++)if($i==""){next}}1' inputfile
1

Using Miller (mlr) and its filter sub-command to discard each record that contains at least one empty field:

mlr --csv -N filter 'for (k,v in $*) { is_empty(v) { false; break } true }' file.csv

This reads the data as header-less CSV. The for loop runs for each record, and the filter operation discards the record immediately if is_empty(v) returns true.

Given the test data in the question, the command given here would output nothing as each record contains at least one empty field.

Would you only want to examine the first four fields, use a test on k to make sue you only test those four first fields for emptiness:

mlr --csv -N filter 'for (k,v in $*) { k <= 4 && is_empty(v) { false; break } true }' file.csv
0
1

Assuming that's a simple CSV file (no quoting, no header, no multiline field):

perl -F, -e 'print unless grep {$_ eq ""} @F[0..3]' your-file

Would remove the lines where any of the first to fourth fields is empty (as your awk attempt suggests you want to do).

While compact, that code redundantly checks all the 4 fields even though it could stop checking as soon as it finds one empty.

awk -F, '$1 != "" && $2 != "" && $3 != "" && $4 != ""' file.csv

Even if less compact wouldn't have the problem.

Note that both remove lines that have fewer than four fields.

0

Can you just drop any line with two commas that are adjacent or separated only by whitespace, or that are first on the line? Something like:

 [dynamic<1>butlet]tmp $ cat data2
aaaa,bbbb,cccc,dddd,
,bbbb,cccc,dddd,
aaaa,,cccc,dddd,
aaaa,,,dddd,
,,,dddd,

 $ egrep -v ',[ ^I]*,|^[ ^I]*,' data2
aaaa,bbbb,cccc,dddd,
0

Using Raku (formerly known as Perl_6)

~$ raku -ne '.put unless grep {.chars == 0}, .split(",")[0..*-2]'  file

OR:

~$ raku -ne 'given .split(",", :skip-empty) {.join(",").put if .elems > 3}'  file

The first answer above is very similar to the Perl answer given by @StéphaneChazelas (Thank you, Stéphane!). Since the lines end with a comma (which can be interpreted as a trailing blank column), the index [0..*-2] is used which drops this last (empty) element. This code retains rows with all filled columns (excepting the final empty column) and will drop lines if blank columns are found even after the first 4 fields. Use .split(",")[0..3] if you want to restrict the requirement to the first four fields (elements).

Note, *-1 is the last element of an array-like structure in Raku, while something like [0..*] also works (means 'give me everything'). Raku has "Zen slices" as well, so .split(",")[] is valid syntax.

The second answer uses the fact that Raku's split routine has a :skip-empty parameter (i.e. "adverb") which can take the place of grepping for .chars == 0. So you are left with deciding how many columns are required in the output. Here .elems > 3 asks for a minimum of 4 columns.

Technically, the second answer is the correct answer, because the first answer leaves the trailing comma in place, while the second answer removes it.

Sample Input:

aaaa,bbbb,cccc,dddd,
,bbbb,cccc,dddd,
aaaa,,cccc,dddd,
aaaa,,,dddd,
,,,dddd,

Sample Output (first answer):

aaaa,bbbb,cccc,dddd,

Sample Output (second answer):

aaaa,bbbb,cccc,dddd

https://docs.raku.org
https://raku.org

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .