Filter CSV rows where specific fields are null or non-null

Question

I'm trying to filter lines in a CSV file based on two specific conditions, using awk:

1. Field 2 should be null (empty) and Field 4 should be non-null (not empty).
2. Field 2 should be non-null (not empty) and Field 4 should be null (empty).

For example, consider a CSV file with the following content:

"venBio","http://www.venbio.com","","venBio is a Investor located in United States, North America."
"zhenZhou","http://www.zhenzhou.com","",""
"Loren","","","A famous Hollywood actress from the 1950s and 1960s"

I want the awk script to first print:

"Loren","","","A famous Hollywood actress from the 1950s and 1960s"

followed by

"zhenZhou","http://www.zhenzhou.com","",""

I've tried various approaches, but I can't seem to get the filtering right for these conditions. Could someone guide me on the proper awk syntax to achieve this?

---

Approaches tried:

awk -F, '($2 == "" && $4 != "")' input.csv

awk -F, '($2 != "" && $4 == "")' input.csv

awk -F, '($2 ~ /^[[:space:]]*$/ && $4 !~ /^[[:space:]]*$/)' input.csv

awk -F, '($2 !~ /^[[:space:]]*$/ && $4 ~ /^[[:space:]]*$/)' input.csv

Answer 1

You may use this awk:

awk -F, -v nul='""' '$2 != nul && $4 == nul {s = s $0 ORS}
$2 == nul && $4 != nul; END {printf "%s", s}' file

"Loren","","","A famous Hollywood actress from the 1950s and 1960s"
"zhenZhou","http://www.zhenzhou.com","",""

Answer 2

The One True Awk supports CSV directly but XOR still has to be implemented as NOT/AND/OR:

awk --csv '(!length($2)&&length($4)) || (length($2)&&!length($4))' input.csv

giving:

"zhenZhou","http://www.zhenzhou.com","",""
"Loren","","","A famous Hollywood actress from the 1950s and 1960s"

If you really want the output in the order from the question, you can run the command twice:

awk --csv '!length($2)&&length($4)' input.csv
awk --csv 'length($2)&&!length($4)' input.csv

giving:

"Loren","","","A famous Hollywood actress from the 1950s and 1960s"
"zhenZhou","http://www.zhenzhou.com","",""