0

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

8
  • 2
    Possibly same assignment posted here Jun 10, 2021 at 10:00
  • 15
    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
    Jun 10, 2021 at 18:29
  • 4
    @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
    Jun 11, 2021 at 8:21
  • 1
    @steeldriver should there be a [homework] tag? serious question.
    – Criggie
    Jun 11, 2021 at 9:06
  • 3
    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
    Jun 11, 2021 at 14:05

5 Answers 5

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
8

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
4

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. Jun 10, 2021 at 17:04
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, 2021 at 8:56
  • @AdminBee the OP used sort in his first example
    – rhellen
    Jun 10, 2021 at 9:00
0

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

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.