1

Using a shell job I am getting a response from mysql in below text format,

response from mysql on vm

In a text way as requested

+-------+

|    A    |

+-------+

|   36   |

+-------+

+-------+

|    B    |

+-------+

|   57   |

+-------+

+-------+

|    C    |

+-------+

|   11   |

+-------+

or a response like

A

36

B

57

C

11

I want to be able to transform the data into simple way as text file like below:

A 36

B 57

C 11

https://justpaste.it/edit/30310895/df1838c844c0fcd1

  • What's the mysql command? Probably makes more sense to stop it from producing 'pretty tables' than sorting out this mess later – Panki Aug 28 at 11:27
  • It will be fine if we can get this transform without pretty tables, I'll update the question- its a simple query – dynamicJos Aug 28 at 11:30
  • Is your input numbers inside boxes as in your first example or just a list of numbers (much easier to parse) as in your second? Please edit your question to show the sample input and the expected output given that input plus what you've tried so far. Indent all 3 by 4 spaces so it's formatted properly. – Ed Morton Aug 28 at 13:35
  • When posting sample input, output, and/or code don't post an image and don't post a link - simply post text indented 4 spaces. – Ed Morton Aug 29 at 12:11
  • It was difficult to update as the view was being changed (to show) the exact response for the text, I have shared a link. Will also update the question as mentioned by you. – dynamicJos Aug 29 at 13:46
1

Is this what you're looking for?

$ cat file
A
36
B
57
C
11

$ awk '{printf "%s%s", $0, (NR%2 ? OFS : ORS)}' file
A 36
B 57
C 11
  • Hi Ed, this looks perfect works well, but due to some reason I can not stop mysql response to be like that instead I want to be to also transform pretty print to this simple response form can you help me do it ? – dynamicJos Aug 29 at 4:32
  • Probably but you didn't include that as textual input in your question so I don't have anything to test against. If you update your question to include that input/output I'll take another look. – Ed Morton Aug 29 at 12:06
  • Updating with text file – dynamicJos Aug 29 at 12:09
  • Don't post a link to text and don't post an image of text - simply post the actual text indented 4 spaces (you can use the editors {} button to do that for you). – Ed Morton Aug 29 at 12:17
1

This /-filter will unwrap your values from the pretty tables:

cat pretty-tables-dump.txt | 
sed 's/\xC2\xA0//g' |
awk '$1=="|"{if(f){print f" "$2;f=""}else{f=$2}}'

Edited to prepend stripping of UTF NBSP via sed.

  • Thanks alex, this is really helpful - I am new to shell especially text processing, can you share some link to learn and improve my skills with it ? or some path I should follow - just asking. – dynamicJos Aug 30 at 4:03
  • One question is after running the sed 's/\xC2\xA0//g' | awk '$1=="|"{if(f){print f" "$2;f=""}else{f=$2}}' the vm seems to be waiting for any other input - so I would have to interrupt using cntrl+c, How would I be able to do that in a shell/using command ? – dynamicJos Aug 30 at 6:10
  • 1
    @dynamicJos: My answer is a "bash shell pipeline filter expression using sed and awk". And yes, you need to feed it Input. For learning, just combine any unknown word in my answer/comment with "Howto", "Manual", "Introduction", "Examples", "Scripting",... And then learn to love to read! A Lot! For a long time! Welcome to the JEDI cult ;) – Alex Stragies Aug 30 at 8:31
  • I understand that, I have given a filename post the commands youve provided. I am just saying post hitting the command sed 's/\xC2\xA0//g' | awk '$1=="|"{if(f){print f" "$2;f=""}else{f=$2}}' filename - the output is generated as expected but the cursor is waiting for a cntrl+c to exit. How can I ensure the cursor jumps to bash once processing the input and displaying output ? – dynamicJos Sep 3 at 6:10
0

You can use \G instead of ;for your mysql query .

Example :

information_schema]> select count(distinct table_schema) as CNT_SCHEMA from TABLES ;
+------------+
| CNT_SCHEMA |
+------------+
|          9 |
+------------+
1 row in set (0.00 sec)

[information_schema]> select count(distinct table_schema) as CNT_SCHEMA from TABLES \G
*************************** 1. row ***************************
CNT_SCHEMA: 9
1 row in set (0.01 sec)

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.