4

I am looking for a bash script. In a text file I have data like:

+------+------
| Id   | User | 
+------+------+
| 8192 | root | 
| 8194 | root |
| 8202 | root |
| 8245 | root | 
| 8434 | root |  
| 8754 | root | 
| 8761 | root | 
| 8762 | root | 
| 8764 | root | 
| 8771 | root | 
+------+------+

I want to extract the data like this:

8192,8194,8202,8245,8434,8754,8761,8762,8764

I mean, I need the first field containing numbers, but not the last one, and all the numbers extracted should be separated by commas (,).

Could somebody help me to get it ?

5
  • Where does this come from? SQL? If so why not SELECT GROUP_CONCAT(DISTINCT {fields} ) FROM ...
    – Rinzwind
    Feb 24, 2014 at 15:48
  • @Rinzwind yes it is a SQL output. No AFAIK its not from a table. All these are MYSQL sleeping PID which I have to kill.
    – Raja G
    Feb 24, 2014 at 15:56
  • Is this still needed due to Kill Sleeping Process in MySQL ? :D
    – Wilf
    Feb 24, 2014 at 16:02
  • 1
    Yes @Wilf , there I can list the data but I can not put it in format because sometimes I may get 100 Connections. 100 ID putting in format by separating with comma , bash can do that very easily rather than me.
    – Raja G
    Feb 24, 2014 at 16:04
  • almost All answers are excellent , which one I have to accept thats big deal.
    – Raja G
    Feb 24, 2014 at 19:04

5 Answers 5

6

You don't need a script for such a simple thing. You can use awk:

awk '$2 ~ "^[0-9][0-9]*$" { print $2 }' file.txt | head -n -1 | awk '{print}' ORS=',' | sed 's/,$/\n/'

Some explanations:

  • awk '$2 ~ "^[0-9][0-9]*$" { print $2 }' file.txt - print from the file.txt only the fields which are numbers.
  • head -n -1 - remove last line / last number.
  • awk '{print}' ORS=',' - concatenate all lines in one single line, each number separated by ,.
  • sed 's/,$/\n/' - replace last , with a newline character.

Or, shorter:

awk '$2 ~ "^[0-9][0-9]*$" { print $2 }' ORS=',' file.txt | sed 's/,[0-9]*,$/\n/'
2
  • My brother , Thanks for answering.+1 , How about If i do have IP's because due to security reasons I have not gave you any IP's.IP's are third field.
    – Raja G
    Feb 24, 2014 at 18:15
  • @rajagenupula Just replace { print $2 } with { print $6 } and sed 's/,[0-9]*,$/\n/' with sed 's/,[0-9.]*,$/\n/' Feb 24, 2014 at 18:58
4

The most shortest way I can find:

echo `sed 's/[^0-9]//g' your_file` | sed 's/ /,/g'

Result is:

8192,8194,8202,8245,8434,8754,8761,8762,8764,8771

[^0-9] - means everything but numericals

s/[^0-9]//g - removes everything but numericals

Substitute your_file with path to your file


For you finally task, with kill command. Be carefully when using this line, it will kill every PID in list:

for pid in `sed 's/[^0-9]//g' your_file | grep -v '^$'`; do kill -9 $pid;done

Before run previous line, you may want to run this line:

for pid in `sed 's/[^0-9]//g' your_file | grep -v '^$'`; do echo "kill -9 $pid";done

It will show something like this:

kill -9 8192
kill -9 8194
kill -9 8202
kill -9 8245
kill -9 8434
kill -9 8754
kill -9 8761
kill -9 8762
kill -9 8764
kill -9 8771

Good luck!

6
  • you are awesome man , thanks for that such short one. but actually in connection files sometimes I may get IP's also which also numerical. so how you're command can separate them from process IP's ? Thank you
    – Raja G
    Feb 24, 2014 at 18:12
  • Shouldn't you be sending SIGTERMs first? Feb 24, 2014 at 20:53
  • I cannot send SIGTERM to a MySQL database to terminate its sleeping process
    – Raja G
    Feb 25, 2014 at 8:06
  • @rajagenupula What about SIGINT and SIGHUP? Feb 26, 2014 at 2:54
  • 1
    Programs should be sent signals they can actually handle, in order to give them a chance to clean up. I don't know anything about MySQL, but this seems especially true for database-related processes. If SIGKILL is the only way to get a program to exit, uninstall it and find an alternative. Feb 27, 2014 at 2:56
4

Here is an one liner for your purpose,

sed 's/[^0-9]//g' file.txt| xargs | sed 's/ /,/g'

or

sed 's/[+|IdUserroot\-]*//g' file.txt | xargs | sed 's/ /,/g'

Output:

8192,8194,8202,8245,8434,8754,8761,8762,8764,8771

Explanation

man sed

s/regexp/replacement/
          Attempt  to  match  regexp  against the pattern space.  If successful, replace that
          portion matched with replacement.  The replacement may contain the special  charac‐
          ter  & to refer to that portion of the pattern space which matched, and the special
          escapes \1 through \9 to refer to the corresponding matching sub-expressions in the
          regexp.

g     Copy/append hold space to pattern space.

Regular expression[Wikipedia]

[^ ]    Matches a single character that is not contained within the brackets.
For example, [^abc] matches any character other than "a", "b", or "c". [^0-9]
matches any single character that is not a number from "0" to "9".

Hence using sed I have replaced everything with space. Next xargs put them in a line separated by space,

$ sed 's/[^0-9]//g' file.txt| xargs
8192 8194 8202 8245 8434 8754 8761 8762 8764 8771

At last step I have replaced all the spaces with , using sed

3
  • Thanks for answering +1 , I like to have some explanation because in future it perhaps helps someone to understood what this command can do. :)
    – Raja G
    Feb 24, 2014 at 18:11
  • @rajagenupula take a look at the edit. Plz let me know if it need any further explanation :)
    – sourav c.
    Feb 24, 2014 at 18:35
  • If i am checking with more number of fields ( they have sensitive information) , its not helping. but with given information you are right. I am not saying that this is wrong but I am saying that its not much accurate for exact extract. Thank you again for answering.
    – Raja G
    Feb 25, 2014 at 9:21
1

where the text is in an input file in the same directory, and result is given in output file.

cat ./input | sed -e 's/+------+------+//g' | sed -e 's/| Id   | User |//g' | sed -e 's/ | root |//g' | tr -d "\n" | sed -e 's/| /,/g' | sed -e 's/ ,/,/g' | sed -e 's/ ,/,/g' > output

When I did it your example I got:

+------+------,8192,8194,8202,8245,8434,8754,8761,8762,8764,8771

That may because the top line of your example was missing a + - there may be a few other errors...

Then I think you just need to add sudo /mysql_rms/bin/mysqladmin -S /mysql_rms/var/mysql_rms.sock -p kill at the beginning


Or, in what is hopefully a slight improvement on what Radu suggested (also based on the above input):

cat ./input | sed 's/[+-]*//g' | sed 's/ | root |//g' | tr -d "\n" | sed 's/| /,/g' | sed 's/ ,/,/g' | sed 's/ ,/,/g' | sed 's/^,//' | sed 's/,[0-9]* $/\n/' | sed 's/Id//g' | sed 's/,User,,//g' | sed 's/ //g' > output

Then, based on you Q&A here, you should be able to run:

sudo /mysql_rms/bin/mysqladmin -S /mysql_rms/var/mysql_rms.sock -p kill $(cat ./output)

although you may want to check whether it is the right command with this first:

echo "sudo /mysql_rms/bin/mysqladmin -S /mysql_rms/var/mysql_rms.sock -p kill $(cat ./output)"
5
  • 1
    Better to use sed -e 's/[+-]*//g' instead of sed -e 's/+------+------+//g'. Feb 24, 2014 at 17:07
  • how about fixing that small unwanted thing ? thanks for answering and effort :)
    – Raja G
    Feb 24, 2014 at 18:13
  • @RaduRădeanu - Better? - I would spend more time on learning this sort of thing, but I need more of a certain entity called 'time'
    – Wilf
    Feb 24, 2014 at 19:03
  • And cat is not necessary. Look at the man sed; sed can take as argument an input file (and this is normal, since sed is an stream editor). Feb 24, 2014 at 19:03
  • @Wilf But you have a lot of time, and you spend it on askubuntu like me an others :) Feb 24, 2014 at 19:07
0

I think this is what you needed. Some other answers failed to remove the last part.

sed -n 's/.*\([0-9]\{4\}\).*/\1/p' file | sed ':a;N;$!ba;s/\n/,/g; s/\(.*\),\(.*\)$/\1/g'

A small example,

$ (echo '| Id   | User |'; echo '| 8192 | root |'; echo '| 8194 | root |'; echo '| 8771 | root |') | sed -n 's+.*\([0-9]\{4\}\).*+\1+p' | sed ':a;N;$!ba;s/\n/,/g; s/\(.*\),\(.*\)$/\1/g'
8192,8194

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .