Page 1 of 1

text data parsing

Posted: Thu Nov 15, 2007 8:57 am
by sheridany
I am working with some application logging data and I want to:
1) create one row per unique timestamp (first field) of all the data associated with that timestamp.
2) extract certain data elements from various rows based on what key row identifer is like 1,2,25, 25.
3) at the beginning of a new timestamp, create a new row.
4) get the data into a csv or excel file.
I have tried do it in easy pattern helper and text pipe and nothing is working...
Data looks like this.

Any suggestions is greatly appreciated.

1194626354|1|10.33.22.95|1194626354|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|CIV 54.0 B294|cci-sf-dev02.wellsfargo.com|GTST0476|OMAR RIVERO|14141|451526| MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727|Online Customer Service Agent|CRSSUPERVISOR=TRUE|OCS_BOB=TRUE|CRS_WD_OPS=TRUE|
1194626354|3|10.33.22.95|1194626354|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|FALSE|N|
1194626355|25|10.33.22.95|1194626355|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|GTST0476|JV:1.4.2_06|VMV:1.4.2_07-b05|OS:Windows XP|OSA:x86|OSV:5.1|TM:3956736|FM:1125128|MM:99942400|JH:C:\PROGRA~1\Java\J2RE14~1.2_0|LP:C:\Program Files\Internet Explorer;.;C:\WINDOWS\system32;C:\WINDOWS;C:\Program Files\Internet Explorer;;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\Diskeeper Corporation\Diskeeper\;C:\Program Files\E!PC\|JC:null|UN:riveroo|CP:C:\PROGRA~1\Java\J2RE14~1.2_0\classes|CB0:https://iat.ibanker.wellsfargo.com:1054 ... |CN0:class com.wellsfargo.civ.presentation.menu.applet.SwingMenuApplet|LO0:en_US||
1194626415|35|10.33.22.95|1194626415|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|7357|
1194626523|4|10.33.22.95|1194626523|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|SoftPhone|false|null|null|null|
1194628185|8|10.33.22.95|1194628185|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|1|s|cci-sf-dev02.wellsfargo.com:-5c4f8c52:1161c1c0df7:-6f78|3391|crossbow::20::listCustForUser = 301|getCustomerInformation = 2367|=2668|473437668|588519481346112|||
1194628232|8|10.33.22.95|1194628232|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|1|e|cci-sf-dev02.wellsfargo.com:-5c4f8c52:1161c1c0df7:-6f6c|32015|crossbow::20::listCustForUser = 220|=220|473437668||||
1194628240|8|10.33.22.95|1194628240|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|1|e|cci-sf-dev02.wellsfargo.com:-5c4f8c52:1161c1c0df7:-6f6b|735|crossbow::20::listCustForUser = 177|=177|473437668||||
1194628247|8|10.33.22.95|1194628247|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|1|e|cci-sf-dev02.wellsfargo.com:-5c4f8c52:1161c1c0df7:-6f6a|672|crossbow::20::listCustForUser = 273|=273|473437668||||
1194628561|2|10.33.22.95|1194628561|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|LogOff|true|SESSION_TIMED_OUT=false|
1194628561|12|10.33.22.95|1194628561|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|customerName=IVRQA OATHB|prodRecStart=RECOMMENDATIONS|prodRecStatus=Not looked At|kosl018=KOSL018|startTime=UTC 11/09/2007 08:42:06|elapsedTime=2422|successIndicator=S|interactionId=I58851948134611220071109084208254|kosl020=KOSL020|correlationId=|startTime=|elapsedTime=|successIndicator=|productId1=|dispositionId1=|offerId1=|productId2=|dispositionId2=|offerId2=|productId3=|dispositionId3=|offerId3=|productId4=|dispositionId4=|offerId4=|productId5=|dispositionId5=|offerId5=|productId6=|dispositionId6=|offerId6=|interactionId=|korf028=KORF028|correlationId=|startTime=|elapsedTime=|successIndicator=|interactionId=|referralId1=|referralId2=|referralId3=|referralId4=|referralId5=|referralId6=|customerECN=588519481346112|bankerId=GTST0476|bankerName=OMAR RIVERO|bankerHris=451526|bankerCity=Albuquerque|UOW=cci-sf-dev02.wellsfargo.com:-5c4f8c52:1161c1c0df7:-6f78|
1194628561|2|10.33.22.95|1194628561|UpOA4ICuR1tyBA+BNQ7pfg==|iBK|LogOff|false|SESSION_TIMED_OUT=true|

Posted: Fri Nov 16, 2007 12:33 pm
by DataMystic Support
Ok, here is a perl pattern than captures this data:
(\d+?\|)[^\r\n]*
((\1[^\r\n]*\r\n)*?(\1[^\r\n]*?))??
Set replace to
$0
and prompting on, and then decide what you need to do next.