2

In the text file I have multi records. Every record has multi column with comma separated, some column have one sets of curly brace and other have more than one curly brace.

I need:

  1. If a comma is found outside one or more sets of curly braces, the comma should be replaced with a pipe.

  2. If a comma is found inside one or more sets of curly braces, the comma should be left alone. So given THING1,{THING2,{THING3,}},THING4 the output should be THING1|{THING2,{THING3,}}|THING4.

Sample record :

(999969,2500,"777777888",0,"45265","65522",NULL,10001,2014-09-15 10:27:07.287,2014-09-15 10:28:49.085,2014-09-15 06:28:50.000,0,0,NULL,"text","401c4133091977",{F,F,"711592473,"00967711580001,F,NULL,NULL,"421010617759466","'401c4133091977H'",NULL,NULL,NULL,NULL,NULL,NULL,1,1,10,1,0,0,0,"a30200000000276f",NULL},NULL,{gggg{-1, 0, -1, 1410762530000, 87, 0, 0}, rrrr[{"foot", 24000, 976000, 3999-12-31 23:59:59, 0}], rrrr[{1000003, 1410762443000, 120, 87, 0, 0, 2, 1, 24000, 0, 0}]},{dd=0, ff=0, gg=0, hh=1, ctr="live", dddd="52265", eni=55, cuc=1},NULL,NULL,NULL,NULL,NULL,{NULL,NULL,NULL,0,"wwww","eeee",2014-10-10 10:45:59.000,2015-03-09 23:59:59.000,2015-06-07 23:59:59.000,2015-08-06 23:59:59.000,NULL})

the result should be :

(**999969|2500|"777777888"|0|"45265"|"65522"|NULL|10001|2014-09-15 10:27:07.287|2014-09-15 10:28:49.085|2014-09-15 06:28:50.000|0|0|NULL|"text"|"401c4133091977"|**{F,F,"711592473,"00967711580001,F,NULL,NULL,"421010617759466","'401c4133091977H'",NULL,NULL,NULL,NULL,NULL,NULL,1,1,10,1,0,0,0,"a30200000000276f",NULL}**|NULL|**{gggg{-1, 0, -1, 1410762530000, 87, 0, 0}, rrrr[{"foot", 24000, 976000, 3999-12-31 23:59:59, 0}], rrrr[{1000003, 1410762443000, 120, 87, 0, 0, 2, 1, 24000, 0, 0}]}**|**{dd=0, ff=0, gg=0, hh=1, ctr="live", dddd="52265", eni=55, cuc=1}**|NULL|NULL|NULL|NULL|NULL|**{NULL,NULL,NULL,0,"wwww","eeee",2014-10-10 10:45:59.000,2015-03-09 23:59:59.000,2015-06-07 23:59:59.000,2015-08-06 23:59:59.000,NULL})
  • Why are there sometimes two stars ** in the example result? – chaos Oct 9 '14 at 10:16
4

You could do this simply through Perl+regex combination.

perl -pe 's/(\{(?:[^{}]|(?1))*\})(*SKIP)(*F)|,/|/g' file

Example:

$ perl -pe 's/(\{(?:[^{}]|(?1))*\})(*SKIP)(*F)|,/|/g' file
(999969|2500|"777777888"|0|"45265"|"65522"|NULL|10001|2014-09-15 10:27:07.287|2014-09-15 10:28:49.085|2014-09-15 06:28:50.000|0|0|NULL|"text"|"401c4133091977"|{F,F,"711592473,"00967711580001,F,NULL,NULL,"421010617759466","'401c4133091977H'",NULL,NULL,NULL,NULL,NULL,NULL,1,1,10,1,0,0,0,"a30200000000276f",NULL}|NULL|{gggg{-1, 0, -1, 1410762530000, 87, 0, 0}, rrrr[{"foot", 24000, 976000, 3999-12-31 23:59:59, 0}], rrrr[{1000003, 1410762443000, 120, 87, 0, 0, 2, 1, 24000, 0, 0}]}|{dd=0, ff=0, gg=0, hh=1, ctr="live", dddd="52265", eni=55, cuc=1}|NULL|NULL|NULL|NULL|NULL|{NULL,NULL,NULL,0,"wwww","eeee",2014-10-10 10:45:59.000,2015-03-09 23:59:59.000,2015-06-07 23:59:59.000,2015-08-06 23:59:59.000,NULL})

Explanation:

I split the regex into two parts for the explanation.

  1. (\{(?:[^{}]|(?1))*\})
  2. (*SKIP)(*F)|,

1st part

(\{(?:[^{}]|(?1))*\})
  • This trick will work only if the curly braces are properly paired.
  • () These are capturing groups, used to capture characters.
  • \{ matches an opening curly brace.
  • (?:[^{}]|(?1))

    • (?:...) Called non-capturing group.
    • [^{}] It would match any character but not of { or }
    • | Logical OR operator.
    • (?1) Recurses the first capturing group.
  • (?:[^{}]|(?1))* Match the previous token zero or more times.
  • \} Closing } symbol.

Consider the below example and the pattern which matches the nested brackets in that.

String:

h{foo{bar}foobar}

Pattern:

h(\{(?:[^{}]|(?1))*\})
  • At first the regex engine tries to match the h (which was in the pattern) against the input string. So the first letter h got matched.
  • Pattern for finding the balanced parenthesis are fed into a capturing group.
  • Now the engine takes the second character (ie, \{) in the pattern and tries to match against the input string. So the first { got captured. I used the word captured instead of match because \{ is inside a capturing group.
  • (?:[^{}]|(?1))* This tells the regex engine to match any character except { or } zero or more times. If you found any { or } character then recurse to the first capturing group one more. So now the string foo got captured. The following character is {, so it recurses to the first capturing group. Now the regex engine is one level down in recursion. What's the first pattern in our first capturing group (see the regex)? It's \{, now it matches the { symbol which was just after to the string foo.
  • The engine is still one level deep in recursion, again the pattern (?:[^{}]|(?1))* matches the string bar. Now the character after the bar is }, so after matching the string bar, the regex engine won't get into (?1) that's why we made the non-capturing group to repeat zero or more times. Next pattern (pattern after to (?:[^{}]|(?1))*) in the regex is \}. So this \} would match the } brace which was just after to bar. Now the regex engine get out of one level deep in recursion and the pattern [^{}]* would match the following string foobar . Last \} would match the last curly bracket.
  • Now our 1st capturing group contains {foo{bar}foobar}.

2nd Part

  • (*SKIP)(*F) Causes the characters which are matched or captured to fail. So in our case all the captured balanced curly braces were skipped. That is, it forces the regex engine to match the characters from the remaining string.
  • Syntax or Format of (*SKIP)(*F)

        part1(*SKIP)(*F)|part2
         |                  |
     |----                  -----> Match this
    Don't match this 
    
  • So the pattern which was just after the | will try to match the characters from the remaining string (string except the nested braces).

  • In our case the pattern after the | is ,. So all the commas which are outside the nested braces got matched.

Read this to understand the Regular Expression Recursion.

Note:

  • (?R) recurses the entire sub-pattern ie, entire match. We could also write (?R) as (?0)
  • (?1) recurses the first subpattern (ie, pattern inside the first capturing group)
0

replace

,{ 

with

|{ 

and

}, 

with

}|

 echo "THING1,{THING2,{THING3,}},THING4" | sed -re "s/,\{/|{/gi" | sed -re "s/},/}|/gi"

results in

THING1|{THING2|{THING3,}}|THING4
  • Yes ,but there are money comma before { should by replace by | and there are more one comma inside {THING4,THING5{THING6,THING7}}should be stay as was comma the result be THING1|THING2|THING3{THING4,THING5{THING6,THING7}}|THING8|THING9 – M.H.A Oct 10 '14 at 12:39
0

Don't be afaid it's a hard sed statement. But, it should respect cascading. Here is it in one line:

sed -e 's/,/|/g;:a;s/{\([^{}]*\)|\([^{}]*\)}/{\1,\2}/g;ta;s/{\([^{}]*\)}/<\1>/g;ta;:b;s/<\([^<>]*\)>/{\1}/g;tb' file

Here is the commented version:

sed -e '
        s/,/|/g;                                 #replaces all commas (,) with pipes (|)
        :a;                                      #sets a label called a
            s/{\([^{}]*\)|\([^{}]*\)}/{\1,\2}/g; #replaces {a|b|c} with {a,b|c}
          ta;                                    #go back to the label `a` and repeat the
                                                 #prevous part until there is nothing more
                                                 #to replace: when {a|b|c} became {a,b,c}
          s/{\([^{}]*\)}/<\1>/g;                 #replace {...} with <...>
        ta;                                      #go back to label a again until all {} are 
                                                 #replaces by <>
        :b;                                      #create a new label called b
          s/<\([^<>]*\)>/{\1}/g;                 #replace <...> back to {...}
        tb;                                      #and back to label b to repeat the previous
                                                 #part
' file

With that I got the desired output.

  • thanks for your answered, but I got error : Label too long: – M.H.A Oct 10 '14 at 12:15
  • @M.H.A can you post which version of sed you are using? sed --version – chaos Oct 10 '14 at 12:30
  • What happens if there are pipes already between the braces? – mikeserv Oct 13 '14 at 16:39
0

I came up with a few ways to do this in sed, but most broke in corner cases. One, however, doesn't:

sed 's/^/\n/;:b
/\n\n/!s/\(\n[^,{}]*\),/\1|/;tb
s/\(\n\n*\)\([^{}]*[{}]\)/\2\1/
s/{\(\n\)/&\1/;s/\(}\n\)\n/\1/;tb
s/\n//g' <<\DATA
(999969,2500,"777777888",0,"45265","65522",NULL,10001,2014-09-15 10:27:07.287,2014-09-15 10:28:49.085,2014-09-15 06:28:50.000,0,0,NULL,"text","401c4133091977",{F,F,"711592473,"00967711580001,F,NULL,NULL,"421010617759466","'401c4133091977H'",NULL,NULL,NULL,NULL,NULL,NULL,1,1,10,1,0,0,0,"a30200000000276f",NULL},NULL,{gggg{-1, 0, -1, 1410762530000, 87, 0, 0}, rrrr[{"foot", 24000, 976000, 3999-12-31 23:59:59, 0}], rrrr[{1000003, 1410762443000, 120, 87, 0, 0, 2, 1, 24000, 0, 0}]},{dd=0, ff=0, gg=0, hh=1, ctr="live", dddd="52265", eni=55, cuc=1},NULL,NULL,NULL,NULL,NULL,{NULL,NULL,NULL,0,"wwww","eeee",2014-10-10 10:45:59.000,2015-03-09 23:59:59.000,2015-06-07 23:59:59.000,2015-08-06 23:59:59.000,NULL},poopoer,sciioooper)
DATA

That steps across a line of data using a delimiter you can be certain not to find on a line - a newline character. It walks the line from left to right stopping at the next of either of two points of interest - the characters }{. When it stops at a { it adds a newline character to its delimiter; when at a } it subtracts one if two are to be had.

When it is stopped at a point in which there is only one newline character to be found on the line and a comma follows its delimiter before either a {} it will replace it with a pipe and recurse back to try the same replacement test again.

This should protect even unbalanced brace groups if necessary, though it doesn't employ any method of handling a quoted brace, which might be done by adding points of interest I guess, but I'm not terribly excited about finding out.

The output from your sample:

(999969|2500|"777777888"|0|"45265"|"65522"|NULL|10001|2014-09-15 10:27:07.287|2014-09-15 10:28:49.085|2014-09-15 06:28:50.000|0|0|NULL|"text"|"401c4133091977"|{F,F,"711592473,"00967711580001,F,NULL,NULL,"421010617759466","'401c4133091977H'",NULL,NULL,NULL,NULL,NULL,NULL,1,1,10,1,0,0,0,"a30200000000276f",NULL}|NULL|{gggg{-1, 0, -1, 1410762530000, 87, 0, 0}, rrrr[{"foot", 24000, 976000, 3999-12-31 23:59:59, 0}], rrrr[{1000003, 1410762443000, 120, 87, 0, 0, 2, 1, 24000, 0, 0}]}|{dd=0, ff=0, gg=0, hh=1, ctr="live", dddd="52265", eni=55, cuc=1}|NULL|NULL|NULL|NULL|NULL|{NULL,NULL,NULL,0,"wwww","eeee",2014-10-10 10:45:59.000,2015-03-09 23:59:59.000,2015-06-07 23:59:59.000,2015-08-06 23:59:59.000,NULL}|poopoer|sciioooper)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.