Parsing with Name value pair and creating a normalized file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Parsing with Name value pair and creating a normalized file
# 1  
Old 02-14-2012
Parsing with Name value pair and creating a normalized file

I have url string as follows and I need to parse the name value pair into fields /rows
Code:
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="
Code:
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
Code:
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 ?

Moderator's Comments:
Mod Comment Use code tags please, see PM.

Last edited by zaxxon; 02-14-2012 at 04:45 PM.. Reason: code tags
# 2  
Old 02-15-2012
A solution with awk :
Code:
awk '
$4 !~ /&/ {
    print;
    next;
}
{
    out = $1 OFS $2 OFS $3;
    kvc = split($4, kv, /&/);
    for (i=2; i<=kvc; i++) {
        v = kv[i];
        sub(/.*=/, "", v);
        out = out OFS v;
    }
    print out;
}
' OFS=',' inputfile

Output (from your sample datas) :
Code:
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

7 More Discussions You Might Find Interesting

1. Programming

Calculate ratios for each pair in a given file

Hello, My input file looks like this #CHROM POS ID REF ALT QUAL FILTER INFO FORMAT Individual1 Individual2 Individual3 Individual4 Individual5 Individual6 22 10000 ID1 A ... (0 Replies)
Discussion started by: nans
0 Replies

2. Shell Programming and Scripting

Parsing a log file and creating a report script

The log file is huge and lot of information, i would like to parse and make a report . below is the log file looks like: REPORT DATE: Mon Aug 10 04:16:17 CDT 2017 SYSTEN VER: v1.3.0.9 TERMINAL TYPE: prod SYSTEM: nb11cu51 UPTIME: 04:16AM up 182 days 57 mins min MODEL, TYPE, and SN:... (8 Replies)
Discussion started by: amir07
8 Replies

3. Shell Programming and Scripting

Parsing a name Value pair file

Hi, I have a file having rows as Row1 : model=.1.3.6.1.4.1.9.1.1047,location=abc, pollgrp=PG_CISCO4, ifindex=3, ip=10.10.10.1,parttype=Interface, devtype=Router,part=GigabitEthernet0/1,ifmtu=1520 Row2 :... (2 Replies)
Discussion started by: mksuneel
2 Replies

4. UNIX for Dummies Questions & Answers

Deleting a file with no corresponding pair

Hi, I am working with 2 sets of files (*csv and *asc) and I wanted to delete asc file with no corresponding csv counterpart. I did tried it manually but its been difficult working with a longer list of files. sample files in directory 20120601.csv 20120601_f1.asc 20120603.csv 20120602_f1.asc... (3 Replies)
Discussion started by: ida1215
3 Replies

5. Shell Programming and Scripting

Help with datafile parsing and creating spreadsheet

I have a datafile containing data in the following format name1,employee_number1,cell1,home1,fax1 name2,employee_number2,cell2,home2,fax2 name3,employee_number3,cell3,home3,fax3 name4,employee_number4,cell4,home4,fax4 name5,employee_number5,cell5,home5,fax5 ... ... .... I would like... (6 Replies)
Discussion started by: inditopgun
6 Replies

6. Shell Programming and Scripting

Sort files as pair file

Hello, I am wondering if there is a way to sort file in directory by pair name : I am looking to get the extension .txt above the .archlike this if possible liste_NATIVE_HINDCAST_PSY1V2R2_R20120314.txt flag.NATIVE_HINDCAST_PSY1V2R2.R20120314.arch... (4 Replies)
Discussion started by: Aswex
4 Replies

7. Shell Programming and Scripting

Parsing of file for Report Generation (String parsing and splitting)

Hey guys, I have this file generated by me... i want to create some HTML output from it. The problem is that i am really confused about how do I go about reading the file. The file is in the following format: TID1 Name1 ATime=xx AResult=yyy AExpected=yyy BTime=xx BResult=yyy... (8 Replies)
Discussion started by: umar.shaikh
8 Replies
Login or Register to Ask a Question