Parsing with Name value pair and creating a normalized file

Shell Programming and Scripting
1

I have url string as follows and I need to parse the name value pair into fields /rows

event_id   date time payload
1329130951 20120214 22.30.40 nvns:ns1=%22http://www.ebay.com/marketplace/mobile/v1/services%22&ns1:name=eBayRtmToolRefreshConfiguration&ns1:level=INFO&ns1:parentCorrelationId=5009153470691470889&ns1:clientInfo.ns1:location=d-sjc-00530678&ns1:clientInfo.ns1:clientDetails=UserName=tuhuynh@ebay.com&OSName=Windows+2003&OSVer=5.2&OSArch=x86&ns1:clientInfo.ns1:appDetails.ns1:environment=PRODUCTION&ns1:clientInfo.ns1:appDetails.ns1:version=2.1.2300&ns1:clientInfo.ns1:appDetails.ns1:appName=Realtime%20Messaging%20Management%20Tool&ns1:clientInfo.ns1:clientType=Web&ns1:duration=96764&ns1:data=time=96764&totalMemory=559153152&usedMemory=94383248&freeMemeory=464769904

In payload Between "& =" is the name and followd by = is the value which is true in most cases except "ns1:clientInfo.ns1:clientDetails=UserName=" and "&ns1:data=time="

nvns:ns1=%22http://www.ebay.com/marketplace/mobile/v1/services%22 
&ns1:name=eBayRtmToolRefreshConfiguration
&ns1:level=INFO
&ns1:parentCorrelationId=5009153470691470889
&ns1:clientInfo.ns1:location=d-sjdssc-25368
&ns1:clientInfo.ns1:clientDetails=UserName=gh@sbb.com
&OSName=Windows+2003
&OSVer=5.2
&OSArch=x86
&ns1:clientInfo.ns1:appDetails.ns1:environment=PROD
&ns1:clientInfo.ns1:appDetails.ns1:version=2.1.2300
&ns1:clientInfo.ns1:appDetails.ns1:appName=Reatoolsrefresh
&ns1:clientInfo.ns1:clientType=Web
&ns1:duration=96764
&ns1:data=time=96764
&totalMemory=559153152
&usedMemory=94383248
&freeMemeory=464769904

I need to parse only the values out from the payload field and have a csv delimited normailized file before loading. The first field "nvns:ns1=%22http://www.ebay.com/marketplace/mobile/v1/services%22" can be ignored

1329130951 ,20120214 ,22.30.40,  eBayRtmToolRefreshConfiguration, INFO, 5009153470691470889 ,d-sjc-00530678, gh@bb.com, Windows+2003 ,5.2, x86 ,prod, 2.1.300, Reatoolsrefresh, web, 96764 ,96764, 559153152 ,94383248, 464769904

Can it be done using awk or perl ?

2

A solution with awk :

awk '
$4 !~ /&/ {
    print;
    next;
}
{
    out = $1 OFS $2 OFS $3;
    kvc = split($4, kv, /&/);
    for (i=2; i<=kvc; i++) {
        v = kv;
        sub(/.*=/, "", v);
        out = out OFS v;
    }
    print out;
}
' OFS=',' inputfile

Output (from your sample datas) :

event_id   date time payload
1329130951,20120214,22.30.40,eBayRtmToolRefreshConfiguration,INFO,5009153470691470889,d-sjc-00530678,tuhuynh@ebay.com,Windows+2003,5.2,x86,PRODUCTION,2.1.2300,Realtime%20Messaging%20Management%20Tool,Web,96764,96764,559153152,94383248,464769904

Jean-Pierre.