2

My CSV file will look like this (pipe separated):

apple|banana|pear||grapefruit
lemon|lime|damson|jackfruit
|tangerine|nectarine|plum
apricot|orange|pineapple|coconut|

(Assume that any sort of whitespace or special characters may be in the individual fields.)

I have to check if any value in any column is null, including the first and last columns.

Expected output:

apple|banana|pear||grapefruit
|tangerine|nectarine|plum
apricot|orange|pineapple|coconut|

How can I do this?

  • 1
    try grep '||' – Archemar Nov 10 '16 at 9:12
  • grep '||' wont catch the line starts with pipe symbol. ( means.. first field is empty ) – Kamaraj Nov 10 '16 at 9:13
2

As mentioned in a comment, the easy and obvious answer would be grep '||', but this would fail to catch cases where the first or last column is null.

So the correct answer (still using grep) is:

grep '^|\|||\||$' file.txt

The | character is not special to Grep unless escaped with a backslash, or unless the -E option is specified. Still, this is a little unclear, so let's see what it would look like if the delimiter in the file were a comma:

grep '^,\|,,\|,$' file.txt

If you want to omit lines where only the last field is null, but print lines where any other field is null, just omit the last case:

grep '^|\|||' file.txt

Actually, it's easier to see what's going on if you put the individual cases in separate arguments, like so:

grep -e '^|' -e '||' -e '|$' file.txt 
0

This will find null fields including the first and last field.

awk -F\| '{for(i=1;i<=NF;i++) if($i=="") { print $0; next } }' file.csv > out.txt
  • This won't find lines where the last field is empty. – ewatt Nov 10 '16 at 9:36
  • 1
    yes.. i see all the given lines having last field as empty.. that's why i put NF-1. @SinghChan has to clarify on this – Kamaraj Nov 10 '16 at 9:37
  • It can ignore last field – SinghChan Nov 10 '16 at 9:40
  • Start with i=0 and use i<=NF as the condition to catch first and last fields – ewatt Nov 10 '16 at 9:44
  • Thanks for the help kamaraj ,one more thing is there any way ican specify which column have to be checked for null value , For example suppose user want to check column no. 1 , column no. 4 column no. 6 and column no 9 – SinghChan Nov 10 '16 at 10:20
0

You can try this with awk. It will print the line number of a match.

$ awk  '/^\|/ || /\|\|/ || /\|$/{print $0}' input_test 
apple|banana|pear||grapefruit
|tangerine|nectarine|plum
apricot|orange|pineapple|coconut|

Based on your revisions to the original question I have reworked the awk command.

Explanation: Awk will match one of three conditions

  • /\|\|/ will match any null field that is not the first or last field.
  • /^\|/ will match any null field at the start of a line- the "^".
  • /\|$/ will match any null field at the end of a line- the "$".

Since awk recognizes | as an operator, when we try to match an "|" we need to use a "\" in front of it.

Now, these 3 conditions are strung together with a "||" which awk interprets as "or." Then when one of those 3 conditions is matched, the whole line will be printed.

To match a specific column(s) as null you can use

awk -F"|" '!length($1) || !length($4) {print $0} ' input_test
  • this wont catch the lines having the first field as empty – Kamaraj Nov 10 '16 at 9:19
  • $ awk -V GNU Awk 4.1.3, API: 1.1 (GNU MPFR 3.1.4, GNU MP 6.1.0) Copyright (C) 1989, 1991-2015 Free Software Foundation. and I tested with ||hyrhyf|123|ma|fhyr|@#$%| – rcjohnson Nov 10 '16 at 9:27
  • you should test with |hyrhyf|123|test|fhyr|@#$%| – Kamaraj Nov 10 '16 at 9:30
  • 1
    because, in your example, first and second field is empty. so your awk command is working. – Kamaraj Nov 10 '16 at 9:31
  • @Kamarj you were correct so I corrected my response – rcjohnson Nov 10 '16 at 9:34
0

Using Miller:

$ cat pipe.dat
apple|banana||pear||grapefruit
lemon|lime|damson|jackfruit
|tangerine|nectarine|plum
apricot|orange|pineapple|coconut|

$ cat has-blanks.mlr
@has_empty = false;
for (k,v in $*) {
    if (v == "") {
        @has_empty = true;
        break;
    }
}
filter @has_empty;

$ cat pipe.dat | mlr --nidx --fs pipe put -f has-blanks.mlr
apple|banana||pear||grapefruit
|tangerine|nectarine|plum
apricot|orange|pineapple|coconut|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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