5

I have to check if the fifth field is empty in a CSV file. This is my file:

1,abc,543,87,DATA,fsg; 
1,abc,543,87,,fsg; 
1,abc,543,87,DATA,fsg; 
1,abc,543,88,,fsg; 
1,abc,543,,DATA,fsg; 

As you can see, the second and fourth lines have an empty fifth field. I want to print all these lines.

Result should be this:

1,abc,543,87,,fsg;
1,abc,543,87,,fsg; 
  • No, fifth value is empty in only 2 lines. – Abhishek dot py Dec 5 '14 at 8:21
11

Another awk:

$ awk -F, '!length($5)' file
1,abc,543,87,,fsg; 
1,abc,543,88,,fsg;
  • suggestion: simply using awk -F, '!$5' file because the fifth field is empty and not including spaces. – αғsнιη Dec 5 '14 at 10:08
  • 6
    @KasiyA: It will fail with $5 = 0. – cuonglm Dec 5 '14 at 10:10
6

You can use a different field separator (by default space) with the -F option, and then you only have to check if the 5th column is empty:

awk -F',' '{if($5==""){print $0}' myfile

or more concisely:

awk -F, '$5==""' myfile

--

And with the sed command:

sed -n '/,,[^,]*$/p' myfile

Don't print (-n) except if it's matching /,,[^,]*$/ that is it ends (...$) with ,, (5th field empty) + a 6th field that does not contain a , ([^,] = everything except a comma).
Note that if you have more than 6 fields, the command won't work as expected.

  • the sed solution does work for lines with more than six fields - and for those with fewer. it just prints lines in which there is no more than one field between the empty one and end of line. And so it prints ,, probably. – mikeserv Dec 5 '14 at 9:34
3

A simple awk would suffice:

awk -F, '$5==""' your_file

Explanation

  • The field separator is set to , (-F ,)
  • The default action of awk is to print the current record. We ensure this only happens when the fifth field is blank ($5 == "")
2

With grep (as asked):

grep -e '.*,.*,.*,.*,,' input 

although this will match lines that have empty fields after the 5th

  • this will match any number of fields before the fifth as well. you want a ^ head-of-line anchor and to use [^,]* rather than .* i believe. – mikeserv Dec 5 '14 at 9:28
  • @mikeserv In that case it should match the last line of the example input, shouldn't it. Or do you mean something else? There is certainly room for improvement. – Anthon Dec 5 '14 at 10:25
  • i said that backwards, kind of - i mean that 5 fields is a minimum here - youll get any amount of any character - to include commas or the null string- separated by commas 5 times at least. – mikeserv Dec 5 '14 at 12:41
1

You can use the awk command:

$ awk '/^(.*,){4},.*/' filename
1,abc,543,87,,fsg;
1,abc,543,88,,fsg;

The regexp test for any character followed by a , four times, and after that the next character must be another , (fifth blank field) followed by whatever.

  • do you you want [^,]*, there? i dont know awk well - but i know elsewhere that statement could get a lot more than 4 comma delimited fields. – mikeserv Dec 5 '14 at 9:08
1

A Pythonic solution:

python3 -c 'import sys, csv;  csv.writer(sys.stdout).writerows(filter(lambda record: record[4] == "", csv.reader(sys.stdin)))' < input.csv
  • Using the csv module, we parse stdin, and filter any lines which have an empty fifth element (index 4, since we index from 0).
  • The filtered set then written out using the csv module to stdout.
  • that's going to first slurp the whole file into a list though – iruvar Dec 5 '14 at 13:21
  • @1_CR Updated to use filter. – muru Dec 5 '14 at 13:25
  • 2
    i prefer generator solutions , so i would recast that to writerows(record for record in csv.reader(sys.stdin) if record[4] == "")), +1 anyways – iruvar Dec 5 '14 at 14:12
0

with sed maybe:

sed -n 's/,/,\n/4;s/\n,/,/p'

Or even:

sed -n '/^\([^,]*,\)\{4\},/p'

The above should work for any grep as well - just leave off the bits before and after / chars. In some seds you may want a literal newline instead of n in the first s///ubstitution.

0

you can also find the null value for 5th column in list of fileS (BULK OF FILE) and print the record with its file name.

awk -F',' '$5 == "" {printf FILENAME"="$0"\n"}' *common_filepattern*  

explanation:

  • -F, set field limit to ,
  • $5 =="" is column 5 to null
  • FILENAME = file name is the keyword to print the name of the 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.