Topic
  • 5 replies
  • Latest Post - ‏2013-02-23T18:41:12Z by SystemAdmin
SystemAdmin
SystemAdmin
2808 Posts

Pinned topic my concatenation string doesn't work when it find a strings with , WHY

‏2013-02-23T00:17:01Z |
I have a text file that I would like to parse at the "|" delimiter. The relevance below work find until it gets to strings that has commas in it.

So for example a line of text in my file:

IBM|S|Servername|Windows|$admin-ibm-arcsight||000/C///user name Central Logging Security Exception ENT-2007-121; #cust#|Enabled|13/04/2011|*Admin-US-IBM-CentralLogging,Domain Admins,Domain Users,Administrators|Domain Admins,Administrators*
my relevance

((tuple string item 2 of it & " ") | "",(tuple string item 4 of it & " ") | "",(tuple string item 6 of it & " ") | "",(tuple string item 7 of it & " ") | "",(tuple string item 8 of it & " ") | "",(tuple string item 9 of it & " ") | "",(tuple string item 10 of it & " ") | "" ) of (concatenation ", " of substrings separated by "|" of it) of lines of files whose (name of it starts with "IBM_" and name of it as lowercase ends with ".mef3")of folder "c:\"
My current result is:
Servername , $admin-ibm-arcsight , 000/C///user name Central Logging RM10003458 Security Exception ENT-2007-121; #cust# , Enabled , 13/04/2011 , ,

I want:
Servername , $admin-ibm-arcsight , 000/C///user name Central Logging RM10003458 Security Exception ENT-2007-121; #cust# , Enabled , 13/04/2011 ,Admin-US-IBM-CentralLogging,Domain Admins,Domain Users,Administrators ,Domain Admins,Administrators
  • Lee Wei
    Lee Wei
    296 Posts

    Re: my concatenation string doesn't work when it find a strings with , WHY

    ‏2013-02-23T04:11:40Z  
    Nice job working through the statements.
    The reason for the issue is that tuples are separated by commas. So your item with commas has to be protected using parenthesis.

    If you try this statement - which is a tuple with 4 items (time, string, date, string), note how the Fixlet Debugger will automatically put parenthesis around the items with commas. Sort of like comma delimited text.

    
    q: (now, 
    "one, two, three", current date, 
    "simple string") A: ( Fri, 22 Feb 2013 20:10:37 -0800 ), ( one, two, three ), ( Fri, 22 Feb 2013 ), simple string
    


    This is an example with the items having been surrounded by parenthesis.

    
    (
    "(" & it & 
    ")") of substrings separated by 
    "|" of lines of files whose (name of it starts with 
    "IBM_" and name of it as lowercase ends with 
    ".mef3") of folder 
    "c:\"
    


    So your final statement can be like this:
    
    ( ((tuple string item  2 of it & 
    " ") | 
    "") & ((tuple string item  4 of it & 
    " ") | 
    "") & ((tuple string item  6 of it & 
    " ") | 
    "") & ((tuple string item  7 of it & 
    " ") | 
    "") & ((tuple string item  8 of it & 
    " ") | 
    "") & ((tuple string item  9 of it & 
    " ") | 
    "") & ((tuple string item 10 of it & 
    " ") | 
    "") ) of concatenation 
    ", " of (
    "( " & it & 
    " )") of substrings separated by 
    "|" of lines of files whose (name of it starts with 
    "IBM_" and name of it as lowercase ends with 
    ".mef3")of folder 
    "c:\"
    
  • SystemAdmin
    SystemAdmin
    2808 Posts

    Re: my concatenation string doesn't work when it find a strings with , WHY

    ‏2013-02-23T17:26:46Z  
    Lee thanks again, I tried the above relevance and it only give me the first line of the text file, it worked but only one line.

    the result from your relevance you gave me:
    HostName Username 000/C/// #CUST# 31MAY2011 Enabled Domain Users

    where as my initial relevance string resulted in 754 lines of data, one line per user
  • Lee Wei
    Lee Wei
    296 Posts

    Re: my concatenation string doesn't work when it find a strings with , WHY

    ‏2013-02-23T17:41:01Z  
    Lee thanks again, I tried the above relevance and it only give me the first line of the text file, it worked but only one line.

    the result from your relevance you gave me:
    HostName Username 000/C/// #CUST# 31MAY2011 Enabled Domain Users

    where as my initial relevance string resulted in 754 lines of data, one line per user
    Here is a modified version.

    (
            ((tuple string item  2 of it & ", ") | "") & 
            ((tuple string item  4 of it & ", ") | "") & 
            ((tuple string item  6 of it & ", ") | "") & 
            ((tuple string item  7 of it & ", ") | "") & 
            ((tuple string item  8 of it & ", ") | "") & 
            ((tuple string item  9 of it & ", ") | "") & 
            ((tuple string item 10 of it & ", ") | "") 
    ) of (concatenations ", " of ("( " & it & " )") of substrings separated by "|" of it) of 
    lines of files whose (name of it starts with "IBM_" and name of it as lowercase ends with ".mef3")of folder "c:\"
    
  • SystemAdmin
    SystemAdmin
    2808 Posts

    Re: my concatenation string doesn't work when it find a strings with , WHY

    ‏2013-02-23T18:24:56Z  
    Lee, That's it - thanks, I changed the commas to tabs so I can have a clean "text to columns" conversion in excel.
  • SystemAdmin
    SystemAdmin
    2808 Posts

    Re: my concatenation string doesn't work when it find a strings with , WHY

    ‏2013-02-23T18:41:12Z  
    I use semicolons to separate my fields

    (((tuple string item 2 of it & "") | "") & ";" & ((tuple string item 4 of it & "") | "") & ";" & ((tuple string item 6 of it & "") | "") & ";" & ((tuple string item 7 of it & "") | "") & ";" & ((tuple string item 8 of it & "") | "") & ";" & ((tuple string item 9 of it & "") | "") & ";" & ((tuple string item 10 of it & "") | "")) of (concatenations ", " of ("( " & it & " )") of substrings separated by "|" of it) of lines of files whose (name of it starts with "IBM_" and name of it as lowercase ends with ".mef3") of folder "c:\"