BreadCrumbs: Awk Command

Awk Command

From Luke Jackson

(Difference between revisions)

Current revision

1 Usage
2 File Spacing
3 Numbering and Calculations
4 Text Conversion and Substitution
- 4.1 Remove newline char at end of line
5 Selective Printing of Certain Lines
6 Selective Deletion of Certain Lines
7 Examples
8 Sources
- 8.1 Convert Sorted 'du -k' into Human Readable (Megabytes)
- 8.2 Parse Backlog Tool
9 Sources

Usage

   Unix:  awk '/pattern/ {print "$1"}'    # standard Unix shells
DOS/Win:  awk '/pattern/ {print "$1"}'    # okay for DJGPP compiled
          awk "/pattern/ {print \"$1\"}"  # required for Mingw32

Most of my experience comes from version of GNU awk (gawk) compiled for Win32. Note in particular that DJGPP compilations permit the awk script to follow Unix quoting syntax '/like/ {"this"}'. However, the user must know that single quotes under DOS/Windows do not protect the redirection arrows (<, >) nor do they protect pipes (|). Both are special symbols for the DOS/CMD command shell and their special meaning is ignored only if they are placed within "double quotes." Likewise, DOS/Win users must remember that the percent sign (%) is used to mark DOS/Win environment variables, so it must be doubled (%%) to yield a single percent sign visible to awk.

If I am sure that a script will NOT need to be quoted in Unix, DOS, or CMD, then I normally omit the quote marks. If an example is peculiar to GNU awk, the command 'gawk' will be used. Please notify me if you find errors or new commands to add to this list (total length under 65 characters). I usually try to put the shortest script first.

  ls -1 | awk -F _ '{str=$2;for(i=3;i<=NF;++i) str=str FS $i; print("mv " $0 " "str)}' | /bin/sh
+Bulk Rename files separated by - character, including spaces in file names with bash
+ ls -1 | awk -F - '{str=$2;for(i=3;i<=NF;++i) str=str FS $i; print("mv " "\""$0"\"" " \""str"\"")}' | /bin/sh
 === Fetch local ip and return 3rd octet (Mac OS X) ===
  ifconfig en0 | grep 'broadcast' | awk '{print $2}' | awk -F . '{print $3}'
- ifconfig | grep -Eo '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}' | awk -F . '$1 != 127 && $4 < 255 {print $3}'
+ ifconfig | grep -Eo '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}' | awk -F . '$1 != 127 && $1 !=255 && $4 < 255 {print $3}'
 === Total list of numbers ===
  awk 'BEGIN{total=0} {total += $1} END{print total}'
+=== Bulk Rename SQL Tables ===
+ awk -F'_' '{t=$0; for(i=n;i<=NF;i++)$(i-(n-1))=$i;NF=NF-(n-1);print "RENAME TABLE `database`.`"t"` TO `database`.`user_"$0"`;" }' OFS='_' n=4 FILE_EXT
+ awk -F'_' '{t=$0; for(i=n;i<=NF;i++)$(i-(n-1))=$i;NF=NF-(n-1);print "ALTER TABLE `database`.`user_"$0"`  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;" }' OFS='_' n=4 FILE_EXT
+ awk -F'_' '{t=$0; for(i=n;i<=NF;i++)$(i-(n-1))=$i;NF=NF-(n-1);print "ALTER TABLE `database`.`user_"$0"` ENGINE = InnoDB;" }' OFS='_' n=4 FILE_EXT
+=== Sum Column of Numbers ===
+'''Earned Points'''
+ pbpaste | tr '\t' ' ' | grep -E '[0-9\.]{2,}$' | cut -d' ' -f 7 | awk '{s+=$1} END {print s}'
+'''Total Points'''
+ pbpaste | tr '\t' ' ' | grep -E '^ [0-9\.][^\.%]{2,}' | cut -d' ' -f 2 | awk '{s+=$1} END {print s}'
+'''Sum Ledger'''
+ pbpaste | cut -f 5 | sed 's/[^0-9\.]//g' | awk '{ sum+=$1} END {print sum}'
+=== Convert XML to MySQL ===
+Revers Line Order For Alter Table
+ awk -F'[<>]' '{a[i++]=$2} END {for (j=i-1; j>=0;) print "ALTER TABLE `user_prd_ddd_setup_types` ADD `"tolower(a[j--])"` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `name`;"}' tmp.xml
+Match INT(10)
+ awk -F'[<>]' '{print $2","$3}' tmp.xml | awk -F',' '$2 ~ /^[0-9]+$/ {print "ALTER TABLE `user_prd_ddd_setup_types` CHANGE `"tolower($1)"` `"tolower($1)"` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0';"}'
+Match FLOAT(6,3)
+ awk -F'[<>]' '{print $2","$3}' tmp.xml | awk -F',' '$2 ~ /^[0-9]+[\.]+[^\.[:alpha:]]*$/ {print "ALTER TABLE `user_prd_ddd_setup_types` CHANGE `"tolower($1)"` `"tolower($1)"` FLOAT( 6,3 ) UNSIGNED NOT NULL DEFAULT '0';"}'
+Match TINYINT(1)
+ awk -F'[<>]' '{print $2","$3}' tmp.xml | awk -F',' '$2 ~ /^(true|false)$/ {print "ALTER TABLE `user_prd_ddd_setup_types` CHANGE `"tolower($1)"` `"tolower($1)"` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0';"}'
+=== Add Prefix AND/OR Suffix to String (Convert Windows Line Break) ===
+ cat A1000.kst | sed 's/^ *//;s/ *$//' | sed $'s/\r$//' | awk -v PRE='$file_body .= "' -v SUF='"."\\n";' '{ $0=PRE$0SUF; print ($0) }'
+ cat file.txt | tr -d "\r" | awk '{print "$file_body .= \""$0"\".\"\\n\";"}'
+ pbpaste | cut -f 2- | awk '{print "* "$0"\n"}' | pbcopy
+=== If Statements (if field length is...) ===
+Export only src or href from course data
+<pre>
+grep -H 'ENV = ' video-lectures-lesson-* | tr '"' '\n' | awk -F'[: ]' '{ if ($4 == "ENV" && $1 != prefix) prefix=$1; print prefix","$0}'| sed -n '/src/{n;p;}' | sed 's/\\//g;s/"//g'
+</pre>
+Convert to wget string with awk and execute with bash
+<pre>
+grep -H 'ENV = ' video-lectures-lesson-* | tr '"' '\n' | awk -F'[: ]' '{ if ($4 == "ENV" && $1 != prefix) prefix=$1; split(prefix,s,"[\-\.]"); print s[3]"-"s[4]","$0}'| sed -n '/src/{n;p;}' | sed 's/\\//g;s/"//g' | grep mp4 | awk -F',' '{ split($2,s,"[\/]");  print "wget "$2" -O "$1"-"s[5]}' | /bin/sh
+</pre>
+Export only contacts from google
+ cat contacts.csv | cut -d',' -f 1-3 | awk -F ',' '{ if (length($3) > 1 && length($2) == 0) print $0}'
+Replace values based on last value of specific field
+ awk -F'[: ]' '{ if ($4 == "ENV" && $1 != prefix) prefix=$1; print prefix","$0}'
+Export captions
+ cat 2505476419001_captions | grep '^[^0-9]' | awk '{ if (substr($0, length($0), 1)==",") print ""$0"#"; else print $0;}'
+ cat 2505476419001_captions | grep '^[^0-9]' | awk '/[^.]$/ { printf("%s ", $0); next } 1'
+'''Prefix the date from XML data to create comma separated values and grep on specific values'''
+ pbpaste | grep -H '<' *.xml |  awk -F'[>:.<]' '{ if ($4 == "Date" && $5 != prefix) prefix=$5; print prefix","$1","$4","$5}' | grep 'Date\|RightTablePrints\|LeftTablePrints\|TotalTablesPrints\|WhiteBridgePrints\|ColorBridgePrints'
+'''Prefix the date from XML data to create comma separated values'''
+ pbpaste | awk '{ if ($1 == "PDF") prefix="+"; else prefix=""; print $0prefix}' | tr '\n' ',' | tr '+' '\n'
+=== Safe String, remove all non alphanumerical chars and format for filenames ===
+ pbpaste | sed '/^$/d; s/^ *//; s/ *$//' | tr -dc '[:alnum:]\. \-\n\r' | awk '{gsub(/ /,"."); print tolower($0)}'
+=== Escape Single Quotes and build Python Dict from CSV list ===
+<pre>
+pbpaste | tr '\r\n' '\n' | awk -F',' '{print "'\''"$1"'\'': {'\''desc'\'': '\''"$2"'\'', '\''price'\'':"$3", '\''type'\'': '\''"$4"'\'', '\''avail'\'': "$5"},"}'
+</pre>
+=== List of numbers padded with zeros separated by string ===
+<pre>
+seq -f %02g 0 24 | awk 'BEGIN{ORS="'\'','\''"} {print ""$0""}'
+</pre>
+=== Mac OS X convert text from stdin bash command line into postscript pdf ===
+ pbpaste | enscript --word-wrap -p - | pstopdf -i -o ~/Downloads/tmp.pdf
+Closed Captions filtered through REGEX with string chunks concatenated into sentences (Carriage Return Safe)
+ cat file.txt | sed "s/[”“]/\"/g;s/[’]/'/g" | sed 's/\r$//;s/[^0-9A-Za-z [:punct:]]//g;/^ $/d;s/^ *//;s/ *$//;' | grep -E '^[^0-9]{2,}' | awk '/[^.]$/ { printf("%s ", $0); next } 1' | enscript --word-wrap -p - | pstopdf -i -o ~/Downloads/Ch2_New_Belgium_Brewery.pdf
+Youtube captions converted into PDF
+ curl-O https://www.youtube.com/api/timedtext?<query_string> | tr '>' '\n' | grep -E '^[[:alpha:]]{1,}' | cut -d'<' -f 1
+ cat timedtext.xml | tr '>' '\n' | sed '/^ $/d;s/^ *//;s/ *$//;' | grep -E '^[[:alnum:]\.,]{1,}' | cut -d'<' -f 1 | awk '/[^.]$/ { printf("%s ", $0); next } 1' | pbcopy
+'''Revised 2021.01.18'''
+ xmlstarlet sel -t -v "//text" timedtext.xml | xmlstarlet unesc | xmlstarlet unesc | awk '/[^.]$/ { printf("%s ", $0); next } 1' | enscript --word-wrap -p - | pstopdf -i -o ~/Downloads/trump.pdf
+Odd artifacts with HTML and XML encoding that forcing the unescape command to need to be ran twice...still not sure about &quot;
+'''Revised 2021.01.19'''
+ xmlstarlet sel -t -v "//text" timedtext.xml | xmlstarlet unesc | sed 's/&quot;/\"/g;' | xmlstarlet unesc | awk '{ printf("%s ", $0); next } 1' | sed 's/\([.!?]\)/\1\'$'\n/g' | sed '/^ $/d; s/^ *//; s/ *$//;'
+ xmlstarlet sel -t -v "//text" timedtext.xml | xmlstarlet unesc | sed 's/&quot;/\"/g;' | xmlstarlet unesc | awk '{ printf("%s ", $0); next } 1' | sed 's/\([.!?]\)/\1\'$'\n/g' | sed '/^ $/d; s/^ *//; s/ *$//;' | enscript --word-wrap -p - | pstopdf -i -o ~/Downloads/trump3.pdf
+Solved documented xmlstarlet bug unescaping "&quot ;" using sed after first unescape, double unescape necessary due to html download of xml content, change in parsing results in less favorable line breaks. Adjusted awk to concatenate on all lines and then rely on sed to add newlines back in on line ending punctuation (!?.), sed cleanup to address awk inserted spaces
+Direct Download M3U8 file playlist stream and convert into local MP4 video file
+ echo "Enter m3u8 link:";read link;echo "Enter output filename:";read filename;ffmpeg -i "$link" -bsf:a aac_adtstoasc -vcodec copy -c copy -crf 23 $filename.mp4
+<pre>
+-bsf:a aac_adtstoasc
+    bsf = (bit stream filter)
+    use aac_adtstoasc bsf for a audio streams, this is need if .m3u8 file consists with .ts files and output is .mp4
+    reference https://ffmpeg.org/ffmpeg-bitstream-filters.html#aac_005fadtstoasc
+-c copy -vcodec copy
+    skip codec (encode and decode), just demux and mux
+    I guess .ts and .mp4, for video stream, they are both H.264 codec, just guess.
+    reference https://ffmpeg.org/ffmpeg.html#Stream-copy
+-crf 50
+    reference https://trac.ffmpeg.org/wiki/Encode/H.264#CRFExample
+    the example shows -c:a copy did not re-encode, guess this option is not needed here.
+    And 0 is lossless, 23 is the default, and 51 is worst quality possible 😢
+</pre>
+https://gist.github.com/tzmartin/fb1f4a8e95ef5fb79596bd4719671b5d
 === Convert Sorted 'du -k' into Human Readable (Megabytes) ===
  du -d 1 -k | sort -nr | awk '$1 > 10240 {print $1 / 1024 $2}'
+=== Parse Backlog Tool ===
+'''Exclamation Mark Reverses Order'''
+ pbpaste | awk -F'%' '/[0-9]{6,}\-[0-9]{6,}|([0-9]{4}\-[0-9]{2}\-[0-9]{2} [0-9]{2}\:[0-9]{2}\:[0-9]{2})\([0-9]+ h\)/ {print $0}' | awk '!(NR%2){print p","$0}{p=$0}'
+ pbpaste | awk -F'%' '/[0-9]{6,}\-[0-9]{6,}|([0-9]{4}\-[0-9]{2}\-[0-9]{2} [0-9]{2}\:[0-9]{2}\:[0-9]{2})\([0-9]+ h\)/ {print $0}' | awk '(NR%2){print p","$0}{p=$0}'
+ pbpaste | awk '/[0-9]{6,}\-[0-9]{6,}/ {print x","$0};{x=$0}'
+'''If field two is equal to nothing, do nothing, else return line'''
+ "C:\Program Files (x86)\GnuWin32\bin\awk.exe" -F"[,]" "{ if ($2 ==\"\") print \"~,\"$1; else print \",\"$2; }"
+== Sources ==
+* http://www.student.northpark.edu/pemente/awk/awk1line.txt
+* http://www.tldp.org/LDP/Bash-Beginners-Guide/html/sect_06_03.html
+* http://www.unix.com/shell-programming-scripting/34163-help-awk.html
+Special thanks to Peter S. Tillier for helping me with the first release
+of this FAQ file.
+For additional syntax instructions, including the way to apply editing
+commands from a disk file instead of the command line, consult:
+"sed & awk, 2nd Edition," by Dale Dougherty and Arnold Robbins
+  O'Reilly, 1997
+"UNIX Text Processing," by Dale Dougherty and Tim O'Reilly
+  Hayden Books, 1987
+"Effective awk Programming, 3rd Edition." by Arnold Robbins
+  O'Reilly, 2001
+To fully exploit the power of awk, one must understand "regular
+expressions." For detailed discussion of regular expressions, see
+"Mastering Regular Expressions, 2d edition" by Jeffrey Friedl
+   (O'Reilly, 2002).
+The manual ("man") pages on Unix systems may be helpful (try "man awk",
+"man nawk", "man regexp", or the section on regular expressions in "man
+ed"), but man pages are notoriously difficult. They are not written to
+teach awk use or regexps to first-time users, but as a reference text
+for those already acquainted with these tools.
+USE OF '\t' IN awk SCRIPTS: For clarity in documentation, we have used
+the expression '\t' to indicate a tab character (0x09) in the scripts.
+All versions of awk, even the UNIX System 7 version should recognize
+the '\t' abbreviation.
+[[Category:Mac OS X]]
+[[Category:Linux]]
+=== Convert Sorted 'du -k' into Human Readable (Megabytes) ===
+ du -d 1 -k | sort -nr | awk '$1 > 10240 {print $1 / 1024 $2}'
+=== Parse Backlog Tool ===
+'''Exclamation Mark Reverses Order'''
+ pbpaste | awk -F'%' '/[0-9]{6,}\-[0-9]{6,}|([0-9]{4}\-[0-9]{2}\-[0-9]{2} [0-9]{2}\:[0-9]{2}\:[0-9]{2})\([0-9]+ h\)/ {print $0}' | awk '!(NR%2){print p","$0}{p=$0}'
+ pbpaste | awk -F'%' '/[0-9]{6,}\-[0-9]{6,}|([0-9]{4}\-[0-9]{2}\-[0-9]{2} [0-9]{2}\:[0-9]{2}\:[0-9]{2})\([0-9]+ h\)/ {print $0}' | awk '(NR%2){print p","$0}{p=$0}'
+ pbpaste | awk '/[0-9]{6,}\-[0-9]{6,}/ {print x","$0};{x=$0}'
+'''If field two is equal to nothing, do nothing, else return line'''
+ "C:\Program Files (x86)\GnuWin32\bin\awk.exe" -F"[,]" "{ if ($2 ==\"\") print \"~,\"$1; else print \",\"$2; }"
 == Sources ==

Awk Command

From Luke Jackson

Current revision

Contents

Usage

File Spacing

Numbering and Calculations

Text Conversion and Substitution

Remove newline char at end of line

Selective Printing of Certain Lines

Selective Deletion of Certain Lines

Examples

Parse Hyperlink Text between href tags

Fetch Printer Report and E-Mail

Clean Up Jpeg Files Based on Date

Batch rename files

Fetch local ip and return 3rd octet (Mac OS X)

Total list of numbers

Bulk Rename SQL Tables

Sum Column of Numbers

Convert XML to MySQL

Add Prefix AND/OR Suffix to String (Convert Windows Line Break)

If Statements (if field length is...)

Safe String, remove all non alphanumerical chars and format for filenames

Escape Single Quotes and build Python Dict from CSV list

List of numbers padded with zeros separated by string

Mac OS X convert text from stdin bash command line into postscript pdf

Convert Sorted 'du -k' into Human Readable (Megabytes)

Parse Backlog Tool

Sources

Convert Sorted 'du -k' into Human Readable (Megabytes)

Parse Backlog Tool

Sources

Views

Personal tools

Navigation

Search

Toolbox