shell script for text to csv conversion

[ammayi67]

thanks for allowing me join your forum
i have an output of linux command "who"
which provides following details.....

CURRENT USER/ACCT INFO
17:31:36 up 4:49, 4 users, load average: 0.03, 0.04, 0.00
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
root :0 - 12:59 ?xdm? 4:54 0.02s /bin/sh /usr/bi
root pts/0 :0 12:59 4:31m 0.00s 1:20 kded [kdeinit]
root pts/1 :0.0 16:18 1.00s 0.00s 0.00s -bash
root pts/2 :0.0 16:25 49.00s 0.02s 0.00s bash

This output i saved into a file named WHO.log

Now how to covert this output into CSV format so that i can export it into some database-------
using some bash script!!!!!!!!!!!!!!!!

any suggestion in this regard is greatly appreciated
thanks in advance

[cfajohnson]

sed 's/ /,/g' "$file" > "$file.csv"

[JeoSaurus]

Is that output from 'who'? It looks more like the 'w' command.

The only problem with the straight sed approach above is that you're going to end up with a lot more commas than you expect. You probably don't want commas in the "WHAT" column between commands and arguments and things.

Try something like this, it should provide a fairly clean result (I love the 'read' command):

# w | awk 'NR>1' | while read USER TTY LOGIN IDLE JCPU PCPU WHAT; do
  echo "$USER,$TTY,$LOGIN,$IDLE,$JCPU,$PCPU,$WHAT";
done

The result should look something like this:

USER,TTY,LOGIN@,IDLE,JCPU,PCPU,WHAT
username,pts/0,07:57,0.00s,0.17s,0.04s,screen -dr
username,pts/1,07:57,3days,3.94s,0.00s,sleep 120
username,pts/3,07:58,0.00s,0.28s,0.01s,w

I hope this helps!

[cfajohnson]

Better than using a shell loop is to do it all in awk:

w |awk 'BEGIN { OFS="," }
NR > 2 {$1=$1; print}'

[JeoSaurus]

Thanks for that cfajohnson! I love awk, but I've barely scratched the surface of what it can do...

One difference I noticed though is that with my 'read', I can get everything from the 'WHAT' column in one field, because of the way 'read' treats the rest of the line as one field unless you give it another variable to use.

With your awk script, any arguments to the command displayed in the 'what' column are given their own field. For example:

## This source line...
username  pts/0  07:57  0.00s  0.17s  0.04s  screen -dr

## Parsed with 'read'...
username,pts/0,07:57,0.00s,0.17s,0.04s,screen -dr

## puts the command and its arguments into the same column.
## Parsing the same output with the (much more elegant
## and clean!) awk script puts each argument in its own column...

username,pts/0,07:57,0.00s,0.17s,0.04s,screen,-dr

So the end result is that we get 'screen,-dr' as opposed to 'screen -dr' all in one field. Is there a simple way to overcome this in awk?

Thanks!
-G