awk and csv file

Firebar · 03-09-2009, 05:30 PM

Hi everyone,

Hoping someone can help me with my basic awk script. I have a csv file containing lines like:

alias,hostname,interface,ipaddr,netmask,gateway,mac,profile
alias2,hostname2,interface2,ipaddr2,netmask2,gateway2,mac2,profile2
alias3,hostname3,inerface3,ipaddr3,netmask3,gateway3,mac3,profile3
(and so on....)

I'm wanting to read each line and put the different columns into variables to use in a script, e.g:

Code:

alias=$( awk -F, '{ print $1 }' /root/systems.csv )
hostname=$( awk -F, '{ print $2 }' /root/systems.csv )
interface=$( awk -F, '{ print $3 }' /root/systems.csv )
ipaddr=$( awk -F, '{ print $4 }' /root/systems.csv )
netmask=$( awk -F, '{ print $5 }' /root/systems.csv )
gateway=$( awk -F, '{ print $6 }' /root/systems.csv )
mac=$( awk -F, '{ print $7 }' /root/systems.csv )
profile=$( awk -F, '{ print $8 }' /root/systems.csv )

/usr/bin/systemadd $alias $hostname $interface [....]

However, it will print all the entires in each column rather than just the first. I'd rather like it to do the above for one line at a time if possible:

Code:

while read line
do
awk -F .....
/usr/bin/systemadd $alias [....]
done

However I can't seem to make any headway. Can anyone help? Does awk have a function whereby it can read a line at a time (however I expect I wont be able to do that as I'll be breaking out of awk to run the systemadd commands..?)?

Thanks for any assistance that can be given.

anomie · 03-09-2009, 05:52 PM

I don't know of a way to bend awk to do what you're describing, but I'll offer a simple solution: use awk to iterate through the file, and instead of actually running any commands, have awk write output to another file (which will eventually become a shell script).

In other words, the output file will contain lots of entries that look like:

Code:

/usr/bin/systemadd alias1 [....]
/usr/bin/systemadd alias2 [....]
/usr/bin/systemadd alias3 [....]

... and so on.

Afterwards, briefly preview the output file, and then run it like a shell script:
# sh /output/file

Probably much more elegant ways to accomplish this task -- i.e. with bash or with a more featured language -- but I've used what I just described on many occasions.

Firebar · 03-09-2009, 06:13 PM

Thanks for your input and that makes sense. However, I think the issue I'm hitting is I'm not sure how to print the field in each record (iterate through the file) with awk, rather than just the entire column. Or do you mean perhaps grep each line out, awk it and then form the shell script?

colucix · 03-09-2009, 06:27 PM

awk can do many things and offer the possibility to run system commands (and interact with their result). However, following your direction I'd suggest to stick with bash, by parsing the file line by line using a while loop.

Code:

while read alias hostname interface ipaddr netmask gateway mac profile
do
  /usr/bin/systemadd $alias $hostname $interface $ipaddr $netmask $gateway $mac $profile
done < <(cat file | tr "," " ")

The input to the while loop uses process substitution to pass the output of the tr command as input to the loop. The tr command just translates commas to blank spaces. The read statement can read multiple variables/fields separated by blank spaces (or better by the IFS, Input Field Separator).

If the arguments are passed to the /usr/bin/systemadd command in the same order as the input, you can just use read line and the eval statement to build and execute the command line. Something like (not tested)

Code:

while read line
do
  eval /usr/bin/systemadd $line
done < <(cat file | tr "," " ")

anomie · 03-09-2009, 09:25 PM

@Firebar: You got a good answer from colucix already, but for the sake of posterity I'll give an example of what I was referring to.

Given a file called "data" that contains comma-separated columns value and the output file (which value should be echoed to)...

Code:

$ cat data
Hello,file1
How are you?,file2
Bye,file3

$ awk -F, '{ OFS=" " ; print "/bin/echo " $1 " > " $2 }' data > my-script

$ cat my-script 
/bin/echo Hello > file1
/bin/echo How are you? > file2
/bin/echo Bye > file3

$ sh my-script 

$ grep '.' file*
file1:Hello
file2:How are you?
file3:Bye

Rather crude, but I am sure you get the idea. awk writes the otherwise monotonous script for you. (To be fair, three rows of data is not monotonous; but three-thousand rows of data could be.)

ghostdog74 · 03-09-2009, 09:37 PM

Code:

awk -F"," '{cmd = "/usr/bin/systemadd "$1" "$2" "$3 ......; system(cmd)}' file

colucix · 03-10-2009, 01:58 AM

If all the fields are passed as arguments to the command using the same order:

Code:

awk '{gsub(","," "); cmd ="ls -l "$0; system(cmd)}' file

Firebar · 03-10-2009, 03:33 AM

Quote:

The input to the while loop uses process substitution to pass the output of the tr command as input to the loop. The tr command just translates commas to blank spaces. The read statement can read multiple variables/fields separated by blank spaces (or better by the IFS, Input Field Separator).

Wow, a very elegant way of doing it without awk. I was too busy munging some bash with awk

Quote:

Given a file called "data" that contains comma-separated columns value and the output file (which value should be echoed to)...

Thank you, that explains it clearly to me.

Thanks for the input all. I'm going to go away and give all the methods a go to try and explore some more.

pavani.ventrapaati · 11-18-2010, 12:45 AM

how do i grep an entire column from a file using awk command??

for example i had a text file called name.txt

pavani praveena sireesha
pavithra nayanaswitha spandana
kusuma susmitha chandana

in that i want to grep entire second column

my output should be like this

raveena
nayanaswitha
susmintha

please help me with clear explanation,Thank you

grail · 11-18-2010, 05:00 AM

As stated you could easily do all in awk, but depending on how many operations you wish to perform for each line I would follow colucix's suggestion to just keep it in bash.
As such, here is another alternative:

Code:

#!/bin/bash

while read -r
do
    set - ${REPLY//,/ }

    #each item from the file is now a positional parameter
    #ie $1 equals alias based on your post
    /usr/bin/systemadd $1 $2 ...
done<file