text data parsing

A discussion of how to use EasyPatterns, EasyPattern Helper and using the EasyPattern library.

Moderator: DataMystic Support

sheridany
Posts: 39
Joined: Thu Nov 15, 2007 4:20 am

text data parsing

Postby sheridany » Thu Nov 15, 2007 8:57 am

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:10540/ibk/common/menu/|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|

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Postby DataMystic Support » Fri Nov 16, 2007 12:33 pm

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.
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments


Return to “EasyPatterns Support”

Who is online

Users browsing this forum: No registered users and 1 guest