Grep and Cut command in linux

Question

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 (:-

Answer 1

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.

Answer 2

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.

---

Using miller:

Please install miller for running the command.

mlr --csv filter '$col9 >= 1' input.csv

Answer 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.

Answer 4

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

Answer 5

Using Raku (formerly known as Perl_6)

raku -ne '.put if .split(",")[8] >= 1;' 

Sample Input:

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 

Sample Output:

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

Briefly, the -ne commandline flags tell Raku to expect code, and to run it linewise without autoprinting. Linewise data loads into the $_ topic variable. The .split() call is short for $_.split, meaning that split will work on that data.

The [8] zero-indexed 8th column is then selected, and tested to see if its numeric value is >= 1. If so, then $_ is put, which could be written $_.put but is here abbreviated as .put. (So the whole $_ input line gets output if the conditional is satisfied).

Finally, sometimes its useful to "blank out" non-grepped lines, leaving matching lines at the same position. The below Raku code performs that task, returning a blank first line:

~$ raku -ne '.grep(*.split(",")[8] >= 1).put;'  file

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    

https://raku.org