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.

  • 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 '15 at 11:08
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
  • the -i option you used for sed is an non portable extension. – llua Apr 6 '15 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 '15 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 '15 at 14:50
  • @llua: My bad, I don't know why I remember POSIX specify -i. Updated my answer. – cuonglm Apr 6 '15 at 15:03
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

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
  • Is there a way we can do it inline instead of redirecting ? – William R Apr 6 '15 at 11:16
  • I added more to the answer. – taliezin Apr 6 '15 at 11:27
  • @talizin We have gawk < 3.0 version – William R Apr 6 '15 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 '15 at 14:19
2

With GNU sed:

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

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.