mjhjmtu

 pid name tid mod state data
-------------------------------------------------------------------------------- 
39523 srv0051_0001_0 39642 20-10:59:28 Working 820000:500196:500077 
43137 srv0051_0005_0 43156 20-10:59:28 Working 820000:4250501:840057
43895 srv0051_0006_0 43903 20-10:59:28 Working 820000:4250501:840057
47523 srv0051_0009_0 47547 20-10:59:28 Working 600005:4250501:4250846
48841 srv0051_0010_0 48851 20-10:59:28 Working 600005:4290000:4290000
58182 srv0051_0020_0 58188 20-10:59:28 Working 820000:4250501:840057
8297 srv0079_0008_0 8316 20-10:59:27 Working 600005:3070001:3050012



pid,name,tid,mod,state,appnbr,request,tasknbr,appctx,username
39523,srv0051_0001_0,39642,20-10:59:28,Working,820000,500196,500077
43137,srv0051_0005_0,43156,20-10:59:28,Working,820000,4250501,840057
43895,srv0051_0006_0,43903,20-10:59:28,Working,820000,4250501,840057
47523,srv0051_0009_0,47547,20-10:59:28,Working,600005,4250501,4250846
48841,srv0051_0010_0,48851,20-10:59:28,Working,600005,4290000,4290000
58182,srv0051_0020_0,58188,20-10:59:28,Working,820000,4250501,840057
8297,srv0079_0008_0,8316,20-10:59:27,Working,600005,3070001,3050012

sed '
 # delete the 2nd line
 2d

 # remove any leading whitespace
 s/^[[:blank:]]+//

 # on line 1, replace "data" with other words
 1s/data/appnbr request tasknbr appctx username/

 # replace any sequences of whitespace with comma
 s/[[:blank:]]+/,/g

 # replace the 3rd and subsequent colons
 s/:/,/3g
' file

Required GNU sed for the s///3g action

Try this

grep -v "^-" test.txt | tr -s " " ',' | sed -e s/:/,/3g -e '0,/data/ s/data/appnbr,request,tasknbr,appctx,username/'

$ awk -f script.awk file.txt
pid,name,tid,mod,state,appnbr,request,tasknbr,appctx,username
39523,srv0051_0001_0,39642,20-10:59:28,Working,820000,500196,500077
43137,srv0051_0005_0,43156,20-10:59:28,Working,820000,4250501,840057
43895,srv0051_0006_0,43903,20-10:59:28,Working,820000,4250501,840057
47523,srv0051_0009_0,47547,20-10:59:28,Working,600005,4250501,4250846
48841,srv0051_0010_0,48851,20-10:59:28,Working,600005,4290000,4290000
58182,srv0051_0020_0,58188,20-10:59:28,Working,820000,4250501,840057
8297,srv0079_0008_0,8316,20-10:59:27,Working,600005,3070001,3050012

Where script.awk is

BEGIN OFS = "," # set output delimiter

NR == 1 
 # modify some fields of the header
 $6 = "appnbr"
 $7 = "request"
 $8 = "tasknbr"
 $9 = "appctx"
 $10 = "username"


NR == 2 next # skip line 2

NR > 2 
 # split the sixth field on ":" and extend the record with the bits
 split($6, a, ":")
 $6 = a[1]
 $7 = a[2]
 $8 = a[3]


1 # print

perl -lane 'print join ",", @F[0..$#F-1], $. == 1 ? qw/appnbr request tasknbr appctx username/ : split /:/, $F[-1] unless $. == 2' input-file.txt

Output:

id,name,tid,mod,state,appnbr,request,tasknbr,appctx,username
39523,srv0051_0001_0,39642,20-10:59:28,Working,820000,500196,500077
43137,srv0051_0005_0,43156,20-10:59:28,Working,820000,4250501,840057
43895,srv0051_0006_0,43903,20-10:59:28,Working,820000,4250501,840057
47523,srv0051_0009_0,47547,20-10:59:28,Working,600005,4250501,4250846
48841,srv0051_0010_0,48851,20-10:59:28,Working,600005,4290000,4290000
58182,srv0051_0020_0,58188,20-10:59:28,Working,820000,4250501,840057
8297,srv0079_0008_0,8316,20-10:59:27,Working,600005,3070001,3050012

Explanation:

1. Split each line, a.k.a., record, on whitespace(s) and store the reultant fields in the array @F. Array is indexed from 0 and goes all the way to $#F. So $#F-1 is the second last element.


2. All lines except the 2nd are to be processed, in that the last element is the deciding factor.


3. For the first line, the last element is replaced by multiple field names.


4. For the remaining, (remember 2nd is already discarded) are split up on : and these split up fields come in place of the last element.


5. The joining of all the elements is done by the , and then printed to stdout.

With POSIX sed, we can do the following:

sed -e '
 2d
 s/^[[:blank:]]*//;s/[[:blank:]]*$//;s/[[:blank:]]1,/ /g ;# trim n squeeze blanks
 1s/data$/appnbr request tasknbr appctx username/ ;# line 1 spl processing
 y/:/n/ ;# change all colons to newlines
 s/n/:/;s//:/ ;# undo the transformation for the first 2
 y/n/ / ;# change the rest to blanks
 y/ /,/ ;# now change these and the already existing blanks to commas
' input-file.txt

Here's my awk stab at it.

awk 'BEGINprint "id,name,tid,mod,state,appnbr,request,tasknbr,appctx,username"NR>2print $1","$2","$3","$4","$5","gensub(/:/,",","g",$6)' file.txt

• NR>2 so that record number 1 and 2 (headers) are skipped


• print fields $1 thru $5 with commas


• instead of printing field $6, print it with : replaced with ","