perl

How to convert lists in a swift and convenient way on the fly for further processing?

The focus is on a general approach to achive a fully fluent workflow (without the hassle of handling files, pipes, cut & past and so on) for further processing.

Q1) Convert lists into comma-separated strings in single quotes

For quick copy and paste conversion of lists into a quoted string, e.g. to INSERT values in databases like MySQL, MariaDB, Oracle and so on.

Important note: This approach should only be used for quick copy and paste actions, as this procedure in scripts harbors a risk of SQL injection attacks. In other words: this hack should only be used by yourself using a SQL client.

Convert A B C into 'A','B','C'

Q2: How to convert list of ids into a list of commands using the ids

Mostly needed to repeat a single command with, lets say, multiple process numbers in order to terminate numerous isakmp sessions (VPN) on a Cisco router due to a lack of shell-capabilities (like for-loops or xargs) on the IOS command line (despite the fact it could be achived using the Cisco IOS tclsh).

Convert 23828 11281 22873 3765 1234 into

clear crypto isakmp 23828
clear crypto isakmp 22873
clear crypto isakmp 11281
clear crypto isakmp 22873
clear crypto isakmp  3765
clear crypto isakmp  1234

Addtion to the main focus about this question

Be fully aware about the risk of SQL injection attacks. But security is not the point here, as you should know what kind of data your lists carry. The main focus is to have a more generic approach to convert lists on the fly, while being as much flexible as possible. Certainly, some tasks can be better achieved using the appropriate tool like sed, awk, tr, cut or whatever tool buzz around. Unfortunately, everytime choosing the best tool for a specific task you have to fiddle around with the syntax, switches and aside how the tool work. This is exactly the hassle I want to avoid having a more generic approach at hand.

Hence, bear the topic in mind: Calling a Perl one-liner just by pressing a keystroke which can be easily adjusted/edited in the shell before executing it. My approach - see the answer by myself and this as a side node - fulfills exactly this requirement. So, it could be a good idea to read my answer before just posting a solution or suggestion to question. ;-)

Solution

  • Both can be done quickly and easily using a short perl one-liner.

    A1: Build one string (i. e. one single line) with comma-separated strings in single quotes

    First create a list to be used in the following (SQL) examples:

    cat > list.txt <<EOF
A
B
C
D
EOF

    Or in Perl style:

    perl -le 'print foreach (A..D)' > list.txt

    Create the comma separated list with single quoted strings:

    In order to avoid struggling with bash's interpreation of single quotes use the two-digit hex ASCII value \x27 for the single quote < ' >.

    perl -e 'print join ",", map { chomp; qq(\x27$_\x27) } <>' list.txt     # 'A','B','C','D'

    Same principle, just using the octal ASCII value \047:

     perl -e 'print join ",", map { chomp; qq(\047$_\047) } <>' list.txt    # 'A','B','C','D'

    For other purposes (non SQL) where double quotes are needed:

     perl -e 'print join ",", map { chomp; qq("$_") } <>' list.txt          # "A","B","C","D"

    In case #, ; or : is needed as separator, just replace the , within the double-quotes of join ",", into what is required.

    For example:
    join "#",
    join ";",
    join ":",

    As in most cases lists are taken by cut & paste from somewhere else. So, the usage of pbpaste on Mac OS X | macos can substantially shorten the workflow. btw: Any hint for an equivalent Linux command would be highly appreciated. To test the following example first fill the clipboard with the above created list: cat list.txt | pbcopy 

     pbpaste | perl -e 'print join ",", map { chomp; qq(\x27$_\x27) } <>'

    Even shorter; replace the clipboard's content with the transformed list on the fly using pbcopy:

     pbpaste | perl -e 'print join ",", map { chomp; qq(\x27$_\x27) } <>' | pbcopy

    In order to having it at hand when needed (e. g. by pressing ctrl + x + c ~ c to convert), just write this line to $HOME/.inputrc

    "\C-xc": "pbpaste | perl -e 'print join ",", map { chomp; qq(\x27$_\x27) } <>' | pbcopy"

    Activate the key-binding (see readline or the many questions tagged to learn more):

     bind -f $HOME/.inputrc     # read/activate settings
 bind -s                    # show key-bindings

    Now just hit ctrl + x + c for a fully automated conversion of your SQL INSERT Values.

    For the sake of completeness some annotations about SQL-quoting

    Excerpt taken from this very good clarification about quotes and backticks: Single quotes should be used for string values like in the VALUES() list. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double quotes.

    A2: Create a command list (i. e. many lines) from a list (e. g. process numbers)

    This approach builds - in contrast to A1 - multiple lines (a repetition of commands with process ids).

    Again: First create a list to be used for the following (Cisco) example:

    perl -le 'print foreach (1000..1010)' | pbcopy

    In reality copy the real process ids to the clipboard; after that transform the plain list of numbers into the appropriate command list:

    pbpaste | perl -wle 'while (<>) {chomp; print "clear crypto isakmp $_"}'

    This gives:

    clear crypto isakmp 1000
clear crypto isakmp 1001
clear crypto isakmp 1002
clear crypto isakmp 1003
clear crypto isakmp 1004
clear crypto isakmp 1005
clear crypto isakmp 1006
clear crypto isakmp 1007
clear crypto isakmp 1008
clear crypto isakmp 1009
clear crypto isakmp 1010

    To have it also at hand when needed (e. g. by pressing ctrl + x + p ~ p for process list), just write this line to $HOME/.inputrc

    "\C-xc": "pbpaste | perl -wle 'while (<>) {chomp; print "clear crypto isakmp $_"}' | pbcopy"