0

I am trying to parse in a CSV file which contains a typical access control matrix table into a shell script. My sample CSV file would be

"user","admin","security"  
"user1","x",""  
"user2","","x"  
"user3","x","x"

I would be using this list in order to create files in their respective folders. The problem is how do I get it to store the values of column 2/3 (admin/security)? The output I'm trying to achieve is to group/sort all users that have admin/security rights and create files in their respective folders. (My idea is to probably store all admin/security users into different files and run from there.)

The environment does not allow me to use any Perl or Python programs. However any awk or sed commands are greatly appreciated.

My desired output would be

$ cat sample.csv
"user","admin","security"
"user1","x",""
"user2","","x"
"user3","x","x"
$ cat security.csv
user2
user3
$ cat admin.csv
user1
user3

user1358062
  • 23
  • 1
  • 1
  • 4
  • 5
    Don't store users into files -- they might suffocate. – devnull Jul 25 '13 at 05:09
  • Why are there 5 columns in the heading line? Are the two leading commas a mistake? In the body of the file, do you have to deal with any alternatives other than `""` or `"x"` for the format? – Jonathan Leffler Jul 25 '13 at 05:20
  • my mistake on heading line, will edit it. The csv will only have x and blanks for the formats – user1358062 Jul 26 '13 at 02:18
  • That's better. I should, I suppose, have requested you to show the desired output. However, I think [Justin L.](http://stackoverflow.com/users/292731/justin-l) has done a decent job in his [answer](http://stackoverflow.com/a/17849510/15168) of covering plausible bases. If your requirements can't be resolved with that, you need to show what you expect as output(s) form your given data file. – Jonathan Leffler Jul 26 '13 at 02:44

2 Answers2

5

if you can use cut(1) (which you probably can if you're on any type of unix) you can use

cut -d , -f (n) (file)

where n is the column you want.

You can use a range of columns (2-3) or a list of columns (1,3).

This will leave the quotes but you can use a sed command or something light-weight for that.

$ cat sample.csv
"user","admin","security"
"user1","x",""
"user2","","x"
"user3","x","x"

$ cut -d , -f 2 sample.csv
"admin"
"x"
""
"x"

$ cut -d , -f 3 sample.csv
"security"
""
"x"
"x"

$ cut -d , -f 2-3 sample.csv
"admin","security"
"x",""
"","x"
"x","x"

$ cut -d , -f 1,3 sample.csv
"user","security"
"user1",""
"user2","x"
"user3","x"

note that this won't work for general csv files (doesn't deal with escaped commas) but it should work for files similar to the format in the example for simple usernames and x's.

if you want to just grab the list of usernames, then awk is pretty much the tool made for the job, and an answer below does a good job that I don't need to repeat.

But a grep solution might be quicker and more lightweight

The grep solution:

grep '^\([^,]\+,\)\{N\}"x"'

where N is the Nth column, with the users being column 0.

$ grep '^\([^,]\+,\)\{1\}"x"' sample.csv
"user1","x",""
"user3","x","x"

$ grep '^\([^,]\+,\)\{2\}"x"' sample.csv
"user2","","x"
"user3","x","x"

from there on you can use cut to get the first column:

$ grep '^\([^,]\+,\)\{1\}"x"' sample.csv | cut -d , -f 1
"user1"
"user3"

and sed 's/"//g' to get rid of quotes:

$ grep '^\([^,]\+,\)\{1\}"x"' sample.csv | cut -d , -f 1 | sed 's/"//g'
user1
user3

$ grep '^\([^,]\+,\)\{2\}"x"' sample.csv | cut -d , -f 1 | sed 's/"//g'
user2
user3
Justin L.
  • 13,510
  • 5
  • 48
  • 83
  • is there any way to grab only the users when field has a value in it? Desired output is to list all users that have security rights – user1358062 Jul 26 '13 at 02:07
  • @user1358062 added it using `grep(1)`, `cut(1)`, and `sed(1)` :) – Justin L. Jul 26 '13 at 07:03
  • how to store the result in a variable (nvm i figured it out: i believe the ` character is what to use when storing a command to a variable in bash). edit again: why did the newlines disappear when i did that? – pete Jan 23 '15 at 18:54
1

Something to get you started (please note this will not work for csv files with embedded commas and you will have to use a csv parser):

awk -F, '
NR>1 { 
  gsub(/["]/,"",$0); 
  if($2!="" && $3!="") 
    print $1 " has both privileges"; 
    print $1 > "file"
}' csv
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
jaypal singh
  • 74,723
  • 23
  • 102
  • 147