3.18 mdelnull - Remove rows with null values

Remove the row if values defined at f= parameter contain null value(s).

Format

mdelnull f= [k=] [u=] [-F] [-r] [-R] [i=] [o=] [bufcount=] [-nfn] [-nfno] [-x] [-q] [--help] [--version]

Parameters

`f=`	Search NULL values in specified field name(s) (Multiple fields can be specified).
`k=`	Remove records with null values based on the same key field(s) defined (multiple fields can be specified).
`u=`	Print unmatched records to the output file specified.
`-F`	AND condition for multiple fields
	Records consisting of null values in multiple fields defined at `f=` parameter will be selected and removed.
`-r`	Reverse selection
	Selected records are not removed.
`-R`	AND condition for multiple records
	Remove all records if the field with the same key field specified at the `k=` parameter contains NULL values.

Example

Example 1: Basic Example

Remove records where Quantity and Amount contain null values. Save records with null values to a separate file oth.csv.

$ more dat1.csv
Customer,Quantity,Amount
A,1,10
A,,20
B,1,15
B,3,
C,1,20
$ mdelnull f=Quantity,Amount u=oth.csv i=dat1.csv o=rsl1.csv
#END# kgdelnull f=Quantity,Amount i=dat1.csv o=rsl1.csv u=oth.csv
$ more rsl1.csv
Customer,Quantity,Amount
A,1,10
B,1,15
C,1,20
$ more oth.csv
Customer,Quantity,Amount
A,,20
B,3,

Example 2: Select rows with NULL values

Select records with NULL values by specifying -r.

$ mdelnull f=Quantity,Amount -r i=dat1.csv o=rsl2.csv
#END# kgdelnull -r f=Quantity,Amount i=dat1.csv o=rsl2.csv
$ more rsl2.csv
Customer,Quantity,Amount
A,,20
B,3,

Example 3: Remove records with the same key if any record contains NULL values

Remove based on the aggregate key specified at k=. Remove records where Quantity and Amount contain null values for each customer.

$ mdelnull k=Customer f=Quantity,Amount i=dat1.csv o=rsl3.csv
#END# kgdelnull f=Quantity,Amount i=dat1.csv k=Customer o=rsl3.csv
$ more rsl3.csv
Customer%0,Quantity,Amount
C,1,20

Example 4: AND condition between fields

Remove the record where both Quantity and Amount fields contain null values.

$ more dat2.csv
Customer,Quantity,Amount
A,1,10
A,,
B,1,15
B,3,
C,1,20
$ mdelnull f=Quantity,Amount -F i=dat2.csv o=rsl4.csv
#END# kgdelnull -F f=Quantity,Amount i=dat2.csv o=rsl4.csv
$ more rsl4.csv
Customer,Quantity,Amount
A,1,10
B,1,15
B,3,
C,1,20

Example 5: AND condition between records

Remove the Customer record if all values in Quantity is NULL.

$ mdelnull k=Customer f=Quantity -R i=dat1.csv o=rsl5.csv
#END# kgdelnull -R f=Quantity i=dat1.csv k=Customer o=rsl5.csv
$ more rsl5.csv
Customer%0,Quantity,Amount
A,1,10
A,,20
B,1,15
B,3,
C,1,20

Related Command

mnullto : NULL value(s) in records are convert to specified character strings.

Does not delete the row that contains a NULL value, it is converted to a string of digits of a NULL value.