1

Can anyone help to remove the line in file based on delimeter(Comma), Incase if line contain less number of columns or Bad records , Need to delete those.

Input File:

a,b,c,d
a,b,d,f
c,d
a,v,b,h
d,e,v,n

In the above file if the delimeter is less than 4 than i have to delete the line from the file.

Output File :

a,b,c,d
a,b,d,f
a,v,b,h
d,e,v,n

The below command gives me number of delimeter in a line , How can i delete if that not equal to 4,

egrep -iv '"' file.csv | awk -F',' '{print NF}' 

Thanks.

1
  • No Empty records are fine , Which one in the below are faster in case huge files , Also is there a way we can edit inline instead of redirecting to new file ?
    – William R
    Apr 6, 2015 at 11:08

4 Answers 4

6

Another perl: print a line if there are 3 commas.

perl -i.bak -ne 'print if tr/,/,/==3' file

The tr operator returns the number of characters transliterated.

3

With perl:

$ perl -F, -i.bak -ane 'print if @F > 3' file

With perl > 5.20, you can use -F without -a and -n (-F implies -a and -a implies -n).

Or you can use sed:

$ sed -i.bak -e '/\([^,]*,\)\{3,\}/!d' file
4
  • the -i option you used for sed is an non portable extension.
    – llua
    Apr 6, 2015 at 13:46
  • @llua: where is it non-portable? AFAIK, it matters only when -i without extension. In BSD and Mac, you need -i ''.
    – cuonglm
    Apr 6, 2015 at 13:52
  • POSIX doesn't require the option, so OSs like openbsd, don't have it. pubs.opengroup.org/onlinepubs/9699919799/utilities/…
    – llua
    Apr 6, 2015 at 14:50
  • @llua: My bad, I don't know why I remember POSIX specify -i. Updated my answer.
    – cuonglm
    Apr 6, 2015 at 15:03
3

You can use awk:

awk -F',' 'NF==4' file

If you can use gawk version >= 4.1.0 you can use inplace, more info. So it could be:

gawk -i inplace -v INPLACE_SUFFIX=.bak -F',' 'NF==4' file
4
  • Is there a way we can do it inline instead of redirecting ?
    – William R
    Apr 6, 2015 at 11:16
  • I added more to the answer.
    – taliezin
    Apr 6, 2015 at 11:27
  • @talizin We have gawk < 3.0 version
    – William R
    Apr 6, 2015 at 13:08
  • Then I think you can not do it with awk except creating temporary file or with a risk losing your data..., but the -i option in sed or perl is not magic, it also creates a temporary file, they just handle it for you.
    – taliezin
    Apr 6, 2015 at 14:19
2

With GNU sed:

sed -nE '/^.+,.+,.+,.+$/p' file > output_file

You must log in to answer this question.

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