Keep only the lines containing exact number of delimiters

Question

I have a huge csv file with 10 fields separated by commas. Unfortunately, some lines are malformed and do not contain exactly 10 commas (what causes some problems when I want to read the file into R). How can I filter out only the lines that contain exactly 10 commas?

Answer 1

Another POSIX one:

awk -F , 'NF == 11' <file

If the line has 10 commas, then there will be 11 fields in this line. So we simply make awk use , as the field delimiter. If the number of fields is 11, the condition NF == 11 is true, awk then performs the default action print $0.

Answer 2

Using egrep (or grep -E in POSIX):

egrep "^([^,]*,){10}[^,]*$" file.csv

This filters out anything not containing 10 commas: it matches full lines (^ at the start and $ at the end), containing exactly ten repetitions ({10}) of the sequence "any number of characters except ',', followed by a single ','" (([^,]*,)), followed again by any number of characters except ',' ([^,]*).

You can also use the -x parameter to drop the anchors:

grep -xE "([^,]*,){10}[^,]*" file.csv

This is less efficient than cuonglm's awk solution though; the latter is typically six times faster on my system for lines with around 10 commas. Longer lines will cause huge slowdowns.

Answer 3

The simplest grep code that will work:

grep -xE '([^,]*,){10}[^,]*'

Explanation:

-x ensures that the pattern must match the entire line, rather than just part of it. This is important so you don't match lines with more than 10 commas.

-E means "extended regex", which makes for less backslash-escaping in your regex.

Parentheses are used for grouping, and the {10} afterwards means there must be exactly ten matches in a row of the pattern within the parantheses.

[^,] is a character class—for instance, [c-f] would match any single character that is a c, a d, an e or an f, and [^A-Z] would match any single character that is NOT an uppercase letter. So [^,] matches any single character except a comma.

The * after the character class means "zero or more of these."

So the regex part ([^,]*,) means "Any character except a comma any number of times (including zero times), followed by a comma" and the {10} specifies 10 of these. Then [^,]* to match the rest of the non-comma characters to the end of the line.

Answer 4

sed -ne's/,//11;t' -e's/,/&/10p' <in >out

That first branches out any line with 11 or more commas, and then prints of what remains only those that match 10 commas.

Apparently I answered this before... Here's a me-plagiarism from a question looking for exactly 4 occurrences of some pattern:

You can target [num]th occurrence of a pattern with a sed s///ubstitution command by just adding the [num] to the command. When you test for a successful substitution and don't specify a target :label, the test branches out of the script. This means all you have to do is test for s///5 or more commas, then print what remains.

Or, at least, that handles the lines which exceed your maximum of 4. Apparently you also have a minimum requirement. Luckily, that is just as simple:

sed -ne 's|,||5;t' -e 's||,|4p'

...just replace the 4th occurrence of , on a line with itself and tack your print on to the s///ubstitution flags. Because any lines matching , 5 or more times have already been pruned, the lines containing 4 , matches contain only 4.

Answer 5

Throwing some short python:

#!/usr/bin/env python2
with open('file.csv') as f:
    print '\n'.join(line for line in f if line.count(',') == 10)

This will read each line and check if the number of commas in the line is equal to 10 line.count(',') == 10, if so print it will the line.

Answer 6

And here's a Perl way:

perl -F, -ane 'print if $#F==10'

The -n causes perl to read its input file line by line and execute the script given by -e on each line. The -a turns on automatic splitting: each input line will be split on the value given by -F (here, a comma) and saved as the array @F.

The $#F (or, more generally $#array), is the highest index of the array @F. Since arrays start at 0, a line with 11 fields will have an @F of 10. The script, therefore, prints the line if it has exactly 11 fields.

Answer 7

If fields can contain commas or newlines your code needs to understand csv. Example (with three columns):

$ cat filter.csv
a,b,c
d,"e,f",g
1,2,3,4
one,two,"three
...continued"

$ cat filter.csv | python3 -c 'import sys, csv
> csv.writer(sys.stdout).writerows(
> row for row in csv.reader(sys.stdin) if len(row) == 3)
> '
a,b,c
d,"e,f",g
one,two,"three
...continued"

I suppose that most solutions so far would discard the second and fourth row.

Answer 8

To display those lines NOT containing the exact number of delimiters :

[ $(awk -F, '!(NF == 11)' <myfile.csv | wc -l) -gt 0 ] && printf "\nPlease maintain the comma separator for the following line(s) in the myfile.csv: \n\n" && awk -F, '!(NF == 11)' <myfile.csv && echo && exit 1

Useful for those looking to validate their input data files.

This one liner is taken from my open source project called Automated_Quiz, which is hosted here : https://sourceforge.net/projects/automated-quiz/