Shell script to csv

darling · March 15, 2012, 10:40am

I am preparing script and trying to send output into CSV format. there is property called RNAME when it is blank, next propery(XMIT) is coming into RNAME propery. So i want whenever RNAME is blank it should put "," in that field. So that all will fit in proper coloums in csv file. Can you please let me know how to put this "," when there is a blank space.

PFB code:

do
rq=$queue
lq=`echo "dis qr($queue) rname"|runmqsc $QMGR| tr ' ' '\n' | sed 's/^*//g'|grep RNAME| cut -d '(' -f2| cut -d ')' -f1`
rqm=`echo "dis qr($queue) rqmname"|runmqsc $QMGR| tr ' ' '\n' | sed 's/^*//g'|grep RQMNAME| cut -d '(' -f2| cut -d ')' -f1`
xmitq=`echo "dis qr($queue) xmitq"|runmqsc $QMGR| tr ' ' '\n' | sed 's/^*//g'|grep XMITQ| cut -d '(' -f2| cut -d ')' -f1`
chl=`echo "dis chl(*) xmitq"| runmqsc $QMGR |egrep 'CHANNEL|XMITQ'|egrep -v 'CLUSSDR|CLUSRCVR|RCVR|SYSTEM|SVRCONN'|sed s/'CHA
NNEL'/+/g|tr -d '\n'|tr '+' '\n'|grep $xmitq|awk '{print $1}'| sed -e 's/(/ /' -e 's/)/ /'`
ip=`echo "dis chl($chl) conname"|runmqsc $QMGR | tr ' ' '\n' | sed 's/*^//'| grep CONNAME| cut -d '(' -f2`
echo $rq $lq $xmitq $rqm $chl $ip| awk '{print $1 "," $2 "," $3 "," $4 "," $5 "," $6 }'
done

rocker_me2002 · March 15, 2012, 10:55am

I'm not exactly sure what your are trying to do.
But I think you can simply put the "," in the "echo" itself instead of piping it to awk.

echo $rq, $lq, $xmitq, $rqm, $chl, $ip

darling · March 15, 2012, 11:17am

Hi,

I prepared script and i am sending output put to csv file which is excel. So whenever lq property is blank next field xmitq is coming into lq property. So what i want is whenever lq is blank it should print ",". That is why i am using "," in awk. So it will separate the value and keep it in next coloumn. CSV(Comma separated value)

Corona688 · March 15, 2012, 11:18am

Please show the input you have and the output you want. There's undoubtedly more efficient ways than 7 lines of cut and grep in backticks.

darling · March 15, 2012, 11:26am

See the output. First one is proper.

but second one expecting as

Corona688 · March 15, 2012, 11:31am

I mean the outputs from "dis qr($queue) rname" and the like.

Without seeing the actual input you have, I will have no idea why this code isn't (or even is) producing the output you want. I don't have the equivalent commands installed to experiment with to hope and understand your actual purpose in this program.

darling · March 15, 2012, 11:35am

PFB my full script

#!/bin/ksh
QMGR=$1
echo "Displaying Remote queue information\n"
echo "\nQUEUE,RNAME,XMITQ,RQMNAME,CHANNEL,IPADDRESS"
echo "dis qr()" |runmqsc $QMGR| grep QUEUE| grep -v SYSTEM| cut -d '(' -f2| cut -d ')' -f1| while read queue
do
rq=$queue
lq=`echo "dis qr($queue) rname"|runmqsc $QMGR| tr ' ' '\n' | sed 's/^//g'|grep RNAME| cut -d '(' -f2| cut -d ')' -f1`
rqm=`echo "dis qr($queue) rqmname"|runmqsc $QMGR| tr ' ' '\n' | sed 's/^//g'|grep RQMNAME| cut -d '(' -f2| cut -d ')' -f1`
xmitq=`echo "dis qr($queue) xmitq"|runmqsc $QMGR| tr ' ' '\n' | sed 's/^//g'|grep XMITQ| cut -d '(' -f2| cut -d ')' -f1`
chl=`echo "dis chl() xmitq"| runmqsc $QMGR |egrep 'CHANNEL|XMITQ'|egrep -v 'CLUSSDR|CLUSRCVR|RCVR|SYSTEM|SVRCONN'|sed s/'CHA
NNEL'/+/g|tr -d '\n'|tr '+' '\n'|grep $xmitq|awk '{print $1}'| sed -e 's/(/ /' -e 's/)/ /'`
ip=`echo "dis chl($chl) conname"|runmqsc $QMGR | tr ' ' '\n' | sed 's/^//'| grep CONNAME| cut -d '(' -f2`
echo $rq $lq $xmitq $rqm $chl $ip| awk '{print $1 "," $2 "," $3 "," $4 "," $5 "," $6 }'
done
echo "\n"
echo "\nDisplaying Alias Queue Information\n"
echo "ALIAS QUEUE,TARGET QUEUE"
echo "dis qa()"| runmqsc $QMGR| grep QUEUE| grep -v SYSTEM| cut -d '(' -f2| cut -d ')' -f1| while read queue
do
qa=$queue
targq=`echo "dis qa($queue) targq"| runmqsc $QMGR| tr ' ' '\n' | sed 's/^//g'|grep TARGQ| cut -d '(' -f2| cut -d ')' -f1`
echo $qa $targq| awk '{print $1"," $2}'
done
echo "\n"
echo "\nDisplaying Local queue information\n"
echo "\nLOCAL QUEUE"
echo "dis ql() USAGE"|runmqsc $QMGR| grep QUEUE|grep -v SYSTEM| grep -v XMITQ| cut -d '(' -f2 | cut -d ')' -f1
echo "\n\n"
echo "Displaying receiver channel information\n"
echo "CHANNEL,SENDER IPADDRESS"
echo "dis chl() chltype(rcvr)"| runmqsc $QMGR |grep CHANNEL|grep -v SYSTEM| cut -d '(' -f2 | cut -d ')' -f1| while read chl
do
ip=`echo "dis chs($chl)"|runmqsc $QMGR| tr ' ' '\n'| sed 's/^*//'| grep CONNAME| cut -d '(' -f2| cut -d ')' -f1`
echo $chl $ip| awk '{print $1","$2}'
done

I will give qmgr as input. I hope now u can understand

Corona688 · March 15, 2012, 11:37am

I mean the outputs from "dis qr($queue) rname", and the like.

Without seeing what data's being fed into your code I have little hope of guessing what's going to come out of it, or why.

darling · May 23, 2012, 10:22am

output of "dis qr($queue) rname" is

    QUEUE(CCF.IPORB5T5.ORB.LLUHUB_TO_ORBBAM_APO.01)
    TYPE(QREMOTE)
    RNAME(CCF.PR.IPORB5T5.ORB.LLUHUB_TO_ORBBAM_APO.01)

---------- Post updated at 09:22 AM ---------- Previous update was at 09:04 AM ----------

I have a shellscript which gives output as below

#!/bin/ksh

QMGR=$1
RQM=$2
echo "enter Qm"
read QMGR
echo "enter RQM"
read RQM


echo "Displaying Remote queue information\n"
echo "\nQUEUE,RNAME,XMITQ,CHANNEL,IPADDRESS"
echo "dis q(*) where(RQMNAME eq $RQM)"|runmqsc -e $QMGR|grep QUEUE|sed -e 's/QUEUE(/ /g' -e 's/)/ /g' -e 's/TYPE(QREMOTE/ /g'| while read queue
do
rq=$queue
lq=`echo "dis qr($queue) rname"|runmqsc $QMGR| tr ' ' '\n' | sed 's/^*//g'|grep RNAME| cut -d '(' -f2| cut -d ')' -f1`
xmitq=`echo "dis qr($queue) xmitq"|runmqsc $QMGR| tr ' ' '\n' | sed 's/^*//g'|grep XMITQ| cut -d '(' -f2| cut -d ')' -f1`
chl=`echo "dis chl(*) xmitq"| runmqsc $QMGR |egrep 'CHANNEL|XMITQ'|egrep -v 'CLUSSDR|CLUSRCVR|RCVR|SYSTEM|SVRCONN'|sed 's/'CHANNEL'/+/g'|tr -d '\n'|tr '+' '\n'|grep $xmitq|awk '{print $1}'| sed -e 's/(/ /' -e 's/)/ /'`
ip=`echo "dis chl($chl) conname"|runmqsc $QMGR | tr ' ' '\n' | sed 's/*^//'| grep CONNAME| cut -d '(' -f2`
aq=`echo "dis qa(*) where(TARGQ eq '$queue')"|runmqsc -e $QMGR|grep QUEUE|sed -e 's/QUEUE(/ /g' -e 's/)/ /g' -e 's/TYPE(QALIAS/ /g'`
echo $aq,$rq,$lq,$xmitq,$chl, $ip
done

echo "Displaying cluster out bound Queue on $RQM\n"
echo "dis qc(*) where(clusqmgr eq $RQM)"|runmqsc -e $QMGR|grep QUEUE|sed -e 's/QUEUE(/ /g' -e 's/)/ /g'
echo "\n"
echo "Displaying cluster channel information to $RQM\n"
echo "\nCLUSTER,CHANNEL,IPADDRESS,PORT,QMTYPE"
echo "dis clusqmgr('$RQM') cluster(*) conname qmtype deftype status"|runmqsc -e $QMGR|egrep 'CHANNEL|QMTYPE|CLUSQMGR|CONNAME'|sed -e 's/CLUSQMGR(/+/g'|sed 's/)/,/g'|sed 's/(/,/g'|tr -d "\n"|tr "+" "\n"|tr -d " "| nawk -F"," -v OFS=',' '{print $3, $5, $7, $8, $13}'

and output is below... i want to get output into $QMGR_inf.csv

mqm@hwEHCss5 $ ./inf.sh
enter Qm
SSV.QM.HWCH05T6
enter RQM
SSV.QM.ASSA10T1
Displaying Remote queue information


QUEUE,RNAME,XMITQ,CHANNEL,IPADDRESS
,SSV.ASSA10T1.HUB.EHCSS_TO_HUB_APO.01,SSV.PR.ASSA10T1.HUB.EHCSS_TO_HUB_APO.01,SSV.XM.HWCH05T6_ASSA10T1.B1, HWCH05T6.ASSA10T1.B1 , 10.81.30.140
Displaying cluster out bound Queue on SSV.QM.ASSA10T1

    SSV.CL.EHCLLUT2.LLU.EHCSS_TO_HUBLLU_APO.01


Displaying cluster channel information to SSV.QM.ASSA10T1


CLUSTER,CHANNEL,IPADDRESS,PORT,QMTYPE
,,,,
EHCLLUT2.ASSA10T1.C1,SSV.CL.EHCLLUT2,10.81.30.140,51431,NORMAL
TO_QM_ASSA10T1.C1,SSV.CL.APOLLOC1,10.81.30.140,51431,REPOS

Can you please let me know how to get this output into csv file. Incode where i need to mention to convert into csv