Home / server / Questions / 37988
Accepted
Michael Ellick Ang
Asked: 2009-07-09 13:48:21 +0800 CST

Filter fields with space character from csv file on command line

  • 772

A csv file with multiple records is delimited by |.

field1|field2|field3|field4|field5

I want to check if field3 is blank or contains "space" characters only. If it is blank or space, the whole line should show up.

unix command-line-interface csv

5 Answers

  1. Best Answer
    $ echo "1|2||4" | awk  -F'|' '$3 ~ /^[ \t]*$/   {print $0}'

1|2||4

$ echo "1|2|  |4" | awk  -F'|' '$3 ~ /^[ \t]*$/   {print $0}'

1|2|  |4

$ echo "1|2|  3|4" | awk  -F'|' '$3 ~ /^[ \t]*$/   {print $0}'
    • 10

  2. You can also use the cut command to pull out the third field and then test the value:

    $ echo "field1|field2|field3|field4|field5" | cut -d '|' -f 3
field3
    • 3

  3. My random attempt using grep would be:

    grep -E '^[^|]*\|[^|]*\| *[^| ]+ *\|' file
    • 1

  4. I'm not certain about unix, but in linux you would want to use the sed command.

    sed 's/||/\n/g' will make it so that if there are any blank fields it will add a new line. not certain how to get it to only check the 3rd field. sed 's/| |/\n/g' should work for only spaces.

    • 0

  5. Using Perl:

    perl -F'\|' -lane 'print if $F[2] !~ /\S/' file

    -a turns on autosplit mode, which splits the fields into array @F
    -F'\|' sets the field delimiter to |
    $F[2] is the 3rd field
    !~ /\S/ tests for non-space characters (or empty)

    • 0