0

I need to create a bash script that would transform the text file content separated by | and ] ...

Text File Content for example:

Col1|Col2|Col3|P1]P2]P3|D1]D2]D3||Col4 
Col3|ColA|ColA|PA]PB]|DA]DB]|ColD||

The desired output would be:

Col1 Col2 Col3 P1 D1 0 Col4
Col1 Col2 Col3 P2 D2 0 Col4
Col1 Col2 Col3 P3 D3 0 Col4
Col3 ColA ColA PA DA ColD 0
Col3 ColA ColA PB DB ColD 0
Col3 ColA ColA 0 0 ColD 0

EDITED: blank column and blank data after ] would all be replaced by 0

Thank you.

  • 2
    What does this have to do with an array (as mentioned in the title)? – Panki Mar 13 at 8:34
  • 1
    Can the first 2 columns also contain ] characters? – Stéphane Chazelas Mar 13 at 9:59
  • Yes, it is possible. – Richmond Magpayo Mar 13 at 10:06
  • If you have a different question with different requirements, don't edit your question with the new requirements, post a separate question, possibly referencing this one for context if you like. Also please try and specify the exact requirements. An example is not enough. – Stéphane Chazelas Mar 27 at 9:11
  • Apologized as I am new to this. The only difference from the previous one was the replacing empty column and field to '0' or NULL – Richmond Magpayo Mar 27 at 9:30
5

You can do it via sample script (mine is not optimal but will work)

awk -F'[]|]' '{
   print $1,$2,$3,$4,$7,$10
   print $1,$2,$3,$5,$8,$10
   print $1,$2,$3,$6,$9,$10 }' input_filename

Or

awk -F'[]|]' '{
   for (i = 4; i <= 6; i++)
     print $1,$2,$3,$i,$(i+3),$10}' input_filename

You can change the output field separator (space by default) by adding -v OFS=','.

And thanks to @steeldriver one more flexible way (with internal separation of field) to do the job:

awk -F'|' '{
  split($3,a,/]/); n = split($4,b,/]/); 
  for(i=1;i<=n;i++) print $1,$2,a[1],a[i+1],b[i],$5}' input_filename

As per edited question if you want to replace empty field with 0 (zero) you can do it with script like:

awk -F'[]|]' '{ 
   for (i = 1; i <= 11; i++) if ($i == "") $i=0}
   {
   print $1,$2,$3,$4,$7,$10,$11
   print $1,$2,$3,$5,$8,$10,$11
   print $1,$2,$3,$6,$9,$10,$11 }' input_filename

From your comment the script should look like:

awk -F'|' -v OFS="\t" '{
 n = split($4,D,"]"); split($5,E,"]");
 for (i = 1; i <= n; i++) {
     if (D[i] == "") D[i]=0;
     if (E[i] == "") E[i]=0;}
     print $1,$2,$3,D[i],E[i],$6,$7 }' input_file 
  • 1
    Thank you very much Romeo Ninov! – Richmond Magpayo Mar 13 at 8:58
  • 1
    Hi Romeo yep it worked like magic, imma have to try and play with the other suggestion too, but already tested this working. Now how to accept or upvote this. Im new here :) sorry --- Got it :) – Richmond Magpayo Mar 13 at 9:13
  • 1
    @StéphaneChazelas, first thank you about the idea with for :) And "maybe" there is way to do it with kind of logic (do not explicit select values) – Romeo Ninov Mar 13 at 9:34
  • 3
    You could make it less hard-coded by using only | as the FS and then doing further splits on ] e.g. awk -F'|' '{split($3,a,/]/); n = split($4,b,/]/); for(i=1;i<=n;i++) print $1,$2,a[1],a[i+1],b[i],$5}' however this still assumes a particular relationship between the number of subfields in $3 and $4 – steeldriver Mar 13 at 10:18
  • 1
    @RomeoNinov, Ok thank you. I will get back here whenever we have the actual data to play with. Thank you again for all your time. – Richmond Magpayo Mar 15 at 7:54
4

Another awk take at the problem:

awk -F'|' '
  {
    n = split($4, f4, "]")
        split($3, f3, "]")
    for (i = 1; i <= n; i++) {
      $3 = f3[1] OFS f3[1+i]
      $4 = f4[i]
      print
    }
  }'
  • Can make it something dynamic, like if there is a bracket ] inside a pipe | then it will explode just like on the sample, as it could have it on 2nd col or other column too. Thank you again for all help. Very much appreciated and all are working. – Richmond Magpayo Mar 14 at 0:51
  • The output isnt the desired... COL1 COL2 COL3 PAYMENT1 DATE1]DATE2]DATE3 COL4 COL1 COL2 COL3 PAYMENT2 DATE1]DATE2]DATE3 COL4 COL1 COL2 COL3 PAYMENT3 DATE1]DATE2]DATE3 COL4 COL11 COL21 COL31 PAYMENT11 DATE11]DATE21]DATE31 COL41 COL11 COL21 COL31 PAYMENT21 DATE11]DATE21]DATE31 COL41 COL11 COL21 COL31 PAYMENT31 DATE11]DATE21]DATE31 COL41 – Richmond Magpayo Mar 14 at 1:37

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.