Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
1  
your question and the linked question are not the same question. you ask how to how to handle lines with no more or fewer than a certain number of matches, whereas that question requires only a minimum match count. the reality is that question is more easily answered - it doesn't require scanning a line in full, or (at least, as the sed does here) only as far as one more match than is looked for, though this question does. You should not have closed this. – mikeserv Jan 13 at 18:42
1  
actually, looking closer, the asker there does want no more or fewer than matches. that question needs a new title. but the grep answer there is not an acceptable answer for either question... – mikeserv Jan 13 at 18:52
up vote 13 down vote accepted

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.

share|improve this answer
5  
That's actually the first thing that came to my mind on this question. I thought it was overkill, but looking at the code...it sure is clearer. For the benefit of others: -F sets the field separator and NF refers to the number of fields in a given line. Since no code block {statement} is appended to the condition NF == 11, the default action is to print the line. (@cuonglm, feel free to incorporate this explanation if you like.) – Wildcard Jan 13 at 9:16
2  
+1: Very elegant and readable solution that is also very general. I can e.g. found all malformed lines with awk -F , 'NF != 11' <file – Miroslav Sabo Jan 13 at 10:08
    
@gardenhead: It's easy to get it, as you see the OP said in his comment. I sometime answer from my mobile, so it's difficult to add the details explanation. – cuonglm Jan 13 at 10:15
1  
@mikeserv: No, sorry if I made you confused, it's just my bad English. You can't have 11 fields with 1-9 commas. – cuonglm Jan 13 at 10:27
1  
@OlivierDulac: It guards you against file start with - or named -. – cuonglm Jan 13 at 15:42

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.

share|improve this answer
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.

share|improve this answer
1  
@cuonglm - that is what I had actually, at first, but people are always telling me i should write more readable code. since i can read the stuff others dispute as unreadable im not sure what to keep and what to drop...? so i put the second comma. – mikeserv Jan 13 at 10:04
    
@cuonglm - you can mock me - it wont hurt my feelings. i can take a joke. if you were mocking me it was a little funny. its ok - i just wasn't sure and wanted to know. in my opinion, people should be able to laugh at themselves. anyway, i still dont get it! – mikeserv Jan 13 at 10:34
    
Haha, right, it's a very positive thinking. Anyway, it's very funny to chat with you and sometimes, you stress my brain. – cuonglm Jan 13 at 10:42
    
It's interesting that in this answer, if I replace s/hello/world/2 with s//world/2, GNU sed work fine. With two sed from heirloom, /usr/5bin/posix/sed raise segfault, /usr/5bin/sed goes into infinitive loop. – cuonglm Jan 14 at 4:23
    
@mikeserv, in reference to our earlier discussion about sed and awk (in comments)—I like this answer and upvoted it, but notice the translation of the accepted awk answer is: "Print lines with 11 fields" and the translation of this sed answer is: "Attempt to remove the 11th comma; skip to next line if you fail. Attempt to replace the 10th comma with itself; print line if you succeed." The awk answer gives the instructions to the computer just the way you would express them in English. (awk is good for field based data.) – Wildcard Jan 14 at 6:24

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
You could also do print if @F==11 as an array in a scalar context returns the number of elements. – Sobrique Jan 14 at 9:34

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.