1

I have a csv file with loads of data. I wish to cut the 9th column for values >=1 and then use grep to display full rows that match.

Sample format:

ABC,XYZ,RTY,CREAM,FRANCE,170019,ST REMY CREME,3035540005229,0.75,1,15,26.99,10       
ABC,RDS,XSD,SPICE,NETHERLANDS,390476,THE KINGS GINGER,5010493025621,1.5,1,41,49.95,NA      
ABC,RMS,DKS,TABLE WINE RED,CHILE,400176,SANTA ISABELA,63657001349,3,1,12.5,31.99,0    

I have tried with

grep . Myfile.csv |cut -d"," -f9 | sort  |grep -E  "^(1*[1-9][2-9]*(\.[2-9]+)?|1+\.[2-9]*[1-9][2-9]*)$"

but it only shows the 9th column values not the full rows with all the columns.

and also

grep $(cut -d"," -f9 Myfile.csv | grep -E  "^(1*[1-9][2-9]*(\.[2-9]+)?|1+\.[2-9]*[1-9][2-9]*)$") Myfile.csv

Any help would be great.

PS: can't use awk (:-

New contributor
Sandeep Suri is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
8
  • 2
    Possibly same assignment posted here – steeldriver Jun 10 at 10:00
  • 13
    Why can't you use awk? It is a standard tool and should be available on just about any *nix environment. Do you just mean you don't know how to use it? – terdon 2 days ago
  • 3
    @Criggie yes, I guessed as much but I am afraid we won't cater to that kind of artificial limitation. We are happy to help users get around actual limitations of their systems but not absolutely pointless ones imposed by a teacher. Awk and the like are the right tools for the job, so that's what our answers should use because that way they will be useful to all the future users who'll see this question, none of whom will have any problem using something as portable as awk. – terdon 2 days ago
  • 1
    @steeldriver should there be a [homework] tag? serious question. – Criggie 2 days ago
  • 2
    I voted to close this because the question belongs on StackOverflow. A Unix system by definition has Awk. It's described in POSIX. How do I solve this without using specific Unix tools is not useful for future visitors to this site. – Kaz 2 days ago
13

Although you state awk is not a possibility - for the sake of completeness:

awk -F',' '$9>=1' input.csv 

This will instruct awk to consider , as field separator and print only lines where field 9 has a value equal or larger than 1.

0
7

Using csvsql:

Assuming your file csv file as:

$ cat input.csv
col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13
ABC,XYZ,RTY,CREAM,FRANCE,170019,ST REMY CREME,3035540005229,0.75,1,15,26.99,10   
ABC,RDS,XSD,SPICE,NETHERLANDS,390476,THE KINGS GINGER,5010493025621,1.5,1,41,49.95,NA
ABC,RMS,DKS,TABLE WINE RED,CHILE,400176,SANTA ISABELA,63657001349,3,1,12.5,31.99,0

This would do:

<input.csv csvsql  --query "select * from stdin where col9 >=1 "

You can change col9 with column name in header in csvfile.

3

try

grep -E "^([^,]+,){8}[1-9][0-9]*(.[1-9]+)?" MyFile.csv

where

  • -E tell grep to use extended regular expression
  • ^ begin of line
  • [^,]+, match not a comma, one or more time, a comma
  • ( ){8} repeat 8 time (including trailing comma)
  • [1-9][0-9]*(.[1-9]+)? leading non 0 optional dot part

thanks to Daniel Junglas for repeating pattern.

1
  • 2
    That's a seriously limited set of real number formats (OK for current case, not for production code). Beware Nan, Inf, 1.3e-04, 0.3e+05, -2, 1String are all on the edge in various ways. – Paul_Pedant 2 days ago
2

cut is not necessary, sort can do the field inspection for you, then add a component to the regex to go past the first 8 fields:

sort -t"," -k9 MyFile.csv | grep -E "^([^,]*,){8}(1*[1-9][2-9]*(\.[2-9]+)?|1+\.[2-9]*[1-9][2-9]*)"

[^,]*, means any number of non-comma characters followed by a comma, then {8} means 8 repeats. Note this needs the -E flag to grep

2
  • I'm curious, why would you need sort at all? – AdminBee Jun 10 at 8:56
  • @AdminBee the OP used sort in his first example – rhellen Jun 10 at 9:00

Your Answer

Sandeep Suri is a new contributor. Be nice, and check out our Code of Conduct.

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.