Making ths CSV

Aditya.Gurgaon · April 30, 2013, 5:12am

can any body help me on this:

SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              13
SGSAP:     MT_SMS_PAGING               52
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             54
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              5
SGSAP:     MT_SMS_PAGING               25
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             19
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              4
SGSAP:     MT_SMS_PAGING               2
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             5
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              1
SGSAP:     MT_SMS_PAGING
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             1
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              3
SGSAP:     MT_SMS_PAGING               8
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             11
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              1
SGSAP:     MT_SMS_PAGING               3
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             4
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              1
SGSAP:     MT_SMS_PAGING               7
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             8
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              8
SGSAP:     MT_SMS_PAGING               13
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             16
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              14
SGSAP:     MT_SMS_PAGING               38
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             43
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              21
SGSAP:     MT_SMS_PAGING               67
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             54
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              35
SGSAP:     MT_SMS_PAGING               67
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             86
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              47
SGSAP:     MT_SMS_PAGING               94
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             124
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              54
SGSAP:     MT_SMS_PAGING               117
SGSAP:     MT_USSD_PAGING
SGSAP:     MT_SERVICE_REQ             150
SGSAP:     MT_OTHER_PAGING             0
SGSAP:     MT_CALL_PAGING              83
SGSAP:     MT_SMS_PAGING               210
SGSAP:     MT_USSD_PAGING

i want to create a csv file having

MT_OTHER_PAGING    MT_CALL_PAGING   MT_CALL_PAGING
150                                    0                                    83
124                                   0                                     54

vidyadhar85 · April 30, 2013, 5:23am

Whats the criteria behind those values under the columns? also you want same column twice? (MT_CALL_PAGING MT_CALL_PAGING
)

Aditya.Gurgaon · April 30, 2013, 5:30am

no i want clomns will be printed once
but the value will be diff as i have in the file

vidyadhar85 · April 30, 2013, 5:34am

My question is more around the values which have written in desired o/p.. How exactly you arrived to those values?

Aditya.Gurgaon · April 30, 2013, 5:42am

i have that file...which contains the value

i need to make it CSV format.........

in csv it should look like :
MT_OTHER_PAGING MT_CALL_PAGING MT_CALL_PAGING 150 0 83 124 0 54

Jotne · April 30, 2013, 6:26am

Like this? CSV=Comma-separated values

awk 'BEGIN {print "MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING,MT_SERVICE_REQ"} {printf "%s,",$3; if ($2=="MT_SERVICE_REQ") print ""}' file
MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING,MT_SERVICE_REQ
0,13,52,,54,
0,5,25,,19,
0,4,2,,5,
0,1,,,1,
0,3,8,,11,
0,1,3,,4,
0,1,7,,8,
0,8,13,,16,
0,14,38,,43,
0,21,67,,54,
0,35,67,,86,
0,47,94,,124,
0,54,117,,150,
0,83,210,,,

Aditya.Gurgaon · April 30, 2013, 8:09am

hi Jotne
thank you.............

but while running the script .......the result is coming like following :

MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING,MT_SERVICE_REQ
,Following,,,0,13,52,,54,
0,5,25,,19,
0,4,2,,5,

=======

Following,, -> this is coming
and ,, are coming..it has to come ,

Jotne · April 30, 2013, 9:55am

Can you post the complete data you have?

Aditya.Gurgaon · May 2, 2013, 12:37am

ouput is coming like dis...............pls help me on this....lots of , are coming and " ,Following,,," is coming.....

MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING
,Following,,,0,13,52,,
54,0,5,25,,
19,0,4,2,,
5,0,1,,,
1,0,3,8,,
11,0,1,3,,
4,0,1,7,,
8,0,8,13,,
16,0,14,38,,
43,0,21,67,,
54,0,35,67,,
86,0,47,94,,
124,0,54,117,,
150,0,83,210,,
199,0,86,246,,
272,1,102,455,,
482,1,83,267,,
269,0,89,282,,
309,0,88,369,,
361,1,67,243,,
245,0,80,211,,
210,0,40,178,,
173,0,19,103,,
110,0,12,67,,

vidyadhar85 · May 2, 2013, 12:40am

Could you please post your input file? also extra , are due to NULL values in your file.

Aditya.Gurgaon · May 2, 2013, 12:46am

Input file is :

MT_OTHER_PAGING             0
MT_CALL_PAGING              5
MT_SMS_PAGING               25
MT_USSD_PAGING

MT_OTHER_PAGING             0
MT_CALL_PAGING              4
MT_SMS_PAGING               2
MT_USSD_PAGING

yes there is a null value...........

vidyadhar85 · May 2, 2013, 12:52am

Considering you are using the same script suggested by Jotne i dont see why you are getting Following in the output unless either you have made some typo in the script or your input file has that word.

Regarding Null values what exactly you want to do with them? you want them to be displayed as 0 or ,,?

Jotne · May 2, 2013, 12:57am

You input in post #11 does not look like input in post #1, how do you expect the script should work on different input?
And do remember to use code tags on your post

This script does work on data from post #11. If you change input, you need change script.

awk 'BEGIN {print "MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING"} {printf "%s,",$2; if (!NF) print ""}' t
MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING
0,5,25,,,
0,4,2,,

Aditya.Gurgaon · May 2, 2013, 1:09am

yes for null value i want to print 0 ...not ,,

---------- Post updated at 09:09 PM ---------- Previous update was at 09:03 PM ----------

i want for null value ..it should print 0 not ,,

Jotne · May 2, 2013, 1:16am

A simple solution is to add 0

awk 'BEGIN {print "MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING,"} {if (NF) printf "%s,",$2+0; else print ""}' t
MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING,
0,5,25,0,
0,4,2,0,

Aditya.Gurgaon · May 2, 2013, 1:31am

hi jonte
Now out put is coming like :

MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING
,Following,,,0,13,52,,54,0,5,25,,19,0,4,2,

all are coming in one row.........

and for null value ,, is coming........

i need :

MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING
0,13,52,0
54,0,5,25
19,0,4,2

hanson44 · May 2, 2013, 1:40am

Code tags missing.

Aditya.Gurgaon · May 2, 2013, 1:55am

still the output is coming in one line:

MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING
0,0,0,0,0,13,52,0,54,0,5,25,0,19,0,4,2,

it is nt coming in csv format.

---------- Post updated at 09:55 PM ---------- Previous update was at 09:46 PM ----------

hi jonte
still the output is coming in one line:

MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING
0,0,0,0,0,13,52,0,54,0,5,25,0,19,0,4,2,

it is nt coming in csv format...all values r coming in single row

Jotne · May 2, 2013, 2:02am

Post what unix you are using.
I do only use code you have provided above and get correct result.
When posting, click go to advacnced .
Then upload the original files containing your input data.
It seems to contain more than you are posting.

Also STOP bumping (double post), edit it instead.
DO USE CODE TAGS .

Follow board rules or you get no more help.

Try paste this to your terminal:

echo 'MT_OTHER_PAGING 0
MT_CALL_PAGING 5
MT_SMS_PAGING 25
MT_USSD_PAGING

MT_OTHER_PAGING 0
MT_CALL_PAGING 4
MT_SMS_PAGING 2
MT_USSD_PAGING' | awk 'BEGIN {print "MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING,"} {if (NF) printf "%s,",$2+0; else print ""}'

You should then get same as me:

MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING,
0,5,25,0,
0,4,2,0,

Aditya.Gurgaon · May 2, 2013, 2:21am

cat /tmp/stat.txt

MT_OTHER_PAGING 0 MT_CALL_PAGING 5 MT_SMS_PAGING 25 MT_USSD_PAGING  MT_OTHER_PAGING 0 MT_CALL_PAGING 4 MT_SMS_PAGING 2 MT_USSD_PAGING

i am running :

cat /tmp/wholeday.txt | awk 'BEGIN {print "MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING,"} {if (NF) printf "%s,",$2+0; else print ""}' > /tmp/wholeday.csv

---------- Post updated at 10:21 PM ---------- Previous update was at 10:20 PM ----------

output is coming :

cat /tmp/wholeday.csv
MT_OTHER_PAGING,MT_CALL_PAGING,MT_SMS_PAGING,MT_USSD_PAGING,
0,0,0,0,0,13,52,0,54,0,5,25,0,19,0,4,2,0,5,0,1,0,0

coming in one row