Reading CSV files in bash script

id	role	start	stop	host	vmnr
1	role01	no	no	vmhost201	1
2	role02	no	no	vmhost201	2
3	role03	yes	no	vmhost201	3
4	role04	yes	no	vmhost201	4
5	role05	yes	no	vmhost151	1
6	role06	yes	no	vmhost151	2
7	role07	yes	no	vmhost151	3
8	role08	yes	no	vmhost151	4
9	role09	yes	no	vmhost92	1
10	role10	yes	no	vmhost93	2
11	role11	no	yes	vmhost94	3
12	role12	no	yes	vmhost95	4

# hLookupHeaderIndex1
echo "(hLookupHeaderIndex1 id)    :"$(hLookupHeaderIndex1 id)
echo "(hLookupHeaderIndex1 start) :"$(hLookupHeaderIndex1 start)
echo "(hLookupHeaderIndex1 vmnr)  :"$(hLookupHeaderIndex1 vmnr)
# OUTPUT:
#(hLookupHeaderIndex1 id)    :1
#(hLookupHeaderIndex1 start) :3
#(hLookupHeaderIndex1 vmnr)  :6

# vLookup
echo "(vLookup start yes role)    :"$(vLookup start yes role)
echo "(vLookup stop yes role)     :"$(vLookup stop  yes role)
# OUTPUT:
#(vLookup start yes role)    :role03 role04 role05 role06 role07 role08 role09 role10
#(vLookup stop yes role)     :role11 role12

# sortByTwoColumns
ROLES=$(vLookup start yes role)
echo "(vLookup start yes role)                :"$ROLES
echo "(sortByTwoColumns vmnr host role ROLES) :"$(sortByTwoColumns vmnr host role $ROLES)
echo "(sortByTwoColumns host vmnr role ROLES) :"$(sortByTwoColumns host vmnr role $ROLES)
# OUTPUT:
#(vLookup start yes role)                :role03 role04 role05 role06 role07 role08 role09 role10
#(sortByTwoColumns vmnr host role ROLES) :role05 role09 role06 role10 role07 role03 role08 role04
#(sortByTwoColumns host vmnr role ROLES) :role05 role06 role07 role08 role03 role04 role09 role10

# vOptions
echo "(vOptions start)   :"$(vOptions start)
echo "(vOptions host)    :"$(vOptions host)
# OUTPUT:
#(vOptions start)   :no yes
#(vOptions host)    :vmhost151 vmhost201 vmhost92 vmhost93 vmhost94 vmhost95

#!/bin/sh 
function checkCSVTableExists() {
 if [[ -z $CSV_FILE ]] ; then
  echo "$0 Error: export CSV_FILE=./config/config.template.csv $CSV_FILE"
  exit 5
 fi
 if [[ ! -f $CSV_FILE ]] ; then
  echo "$0 Error:CSV_FILE does not exist with name: $CSV_FILE "
  exit 10
 fi
}
# Controleer of de CSV_FILE is gezet, anders stoppen
checkCSVTableExists

# hLookupHeaderIndex1(name)
#
# lookup the index value, starting with 1 for the first column.
# Arg1 String Name of the column
# return int index of column
# Example :$(hLookupHeaderIndex1 'testsoort') 
function hLookupHeaderIndex1() {
 NAME=$1
 # tolowercase
 HEADER=$(head -n1 ${CSV_FILE})
 HEADER=$(tr [A-Z] [a-z] <<< "$HEADER")
 NAME=$(tr [A-Z] [a-z] <<< "$NAME")
 # Check if name in Header
 if [[ $HEADER != *$NAME* ]] ; then
  echo "$0 Error 1001 hLookupHeaderIndex1 not found:  NAME= $NAME HEADER= $HEADER"
  exit 1001
 fi
 PINDEX=$(echo ";"$HEADER";"|sed "s/;$NAME;.*//g"|tr ';' '\n' |wc -l )
 echo $PINDEX;
 return 0
}

# vLookup(column match column)
# lookup matching rows and return values.
# arg1 String headername
# arg2 String match
# arg3 String return column
# return String values in matching column
# Example :$(vLookup  'role' 'role11' 'host')
function vLookup() {
 ColumnIdx=$(hLookupHeaderIndex1 $1 )
 MATCH=$2
 PINDEX=$(hLookupHeaderIndex1 $3 )

 while read LINE ; do
  VALUE=$(echo $LINE | cut -d';' -f$ColumnIdx )
  if [[ $MATCH == $VALUE ]] ; then 
    echo $LINE | cut -d';' -f$PINDEX
  fi
 done < $CSV_FILE
}

# sortByTwoColumns(column1 column2 column_match match_values ..)
#
# arg1 String sort Column name one
# arg2 String sort Column name two
# arg3 String Column name match value
# arg4-n String match values in sorted order
#
function sortByTwoColumns() {
  Column1=$(hLookupHeaderIndex1 $1);shift
  Column2=$(hLookupHeaderIndex1 $1);shift
  Column3=$(hLookupHeaderIndex1 $1);shift
  AWK='{ print $'$Column1'";"$'$Column2'";"$'$Column3'}' # build AWK statement
  GREP=';'$(echo ""$@""|sed 's/ /$|;/g')'$'              # build GREP filter
  tail -n+2 $CSV_FILE|awk -F';' "$AWK"|grep -E $GREP|sort|cut -d';' -f3
}

# vOptions(column)
# arg1 String column name
# return choicelist from the specified column
function vOptions() {
 PINDEX=$(hLookupHeaderIndex1 "$1")
 # skip first line tail -n +2 (start linenumber)
 LIST=$(tail -n +2 $CSV_FILE| cut -d';' -f${PINDEX}|sort|uniq)
 echo $LIST
}

Bash cheatsheet

#tar the directory mydir (and its files/subdirectories) into a tar file named foo.tgz.
tar cvzf foo.tgz mydir 
#see a tar file s table of contents
tar tzf foo.tgz
#extract the contents of a tar file
tar xvzf foo.tgz
#find biggest dir
du -k | egrep -v "\./.+/"|sort -n

Bash make a choice

Script example of asking the user for input.

Ask for a string value:

echo "Make a choice"
read -p "Whats your name?" -a NAME

Read a string value, wait for 10 seconds.

echo "Is your name $NAME?"
read -t 10 -p "Choice 1 YES, 2 NO:" -a YESNO

check if the YESNO value is set.

if [[ -z $YESNO ]]; then
 echo "YESNO not set exit"
 exit 0
fi

Check the of YESNO

if [ $YESNO -eq 1 ]; then
 echo "Yes: $YESNO"
elif  [ $YESNO -eq 2 ] ; then
 echo "No: $YESNO"
else
 echo "Unkown choice: $YESNO" 
fi

Whole script:

#!/bin/bash
#
#
echo "Make a choice"
read -p "Whats your name?" -a NAME

echo "Is your name $NAME?"
read -t 10 -p "Choice 1 YES, 2 NO:" -a YESNO

if [[ -z $YESNO ]]; then
 echo "YESNO not set exit"
 exit 0
fi

if [ $YESNO -eq 1 ]; then
 echo "Yes: $YESNO"
elif  [ $YESNO -eq 2 ] ; then
 echo "No: $YESNO"
else
 echo "Unkown choice: $YESNO" 
fi

Bash search logfiles

LogFile example

2012-09-16 19:06:33,450 ERROR [-59] JDBCException ORA-00001: unique constraint (X_UK1) violated
2012-09-16 19:31:39,319 ERROR [-60] JDBCException ORA-00060: deadlock detected (X_UK3) violated
2012-09-16 19:31:39,998 ERROR [-61] JDBCException ORA-00001: unique constraint (X_UK1) violated
2012-09-16 19:41:31,331 ERROR [-62] JDBCException ORA-00001: unique constraint (X_UK2) violated

First option is to grep the lines with ORA-00060:

$ grep ORA-00060 l.log 
2012-09-16 19:31:39,319 ERROR [-60] JDBCException ORA-00060: deadlock detected (X_UK3) violated

Find ORA error strip first part of the line.

$ grep ORA l.log | sed 's/.*ORA/ORA/g' 
ORA-00001: unique constraint (X_UK1) violated
ORA-00060: deadlock detected (X_UK3) violated
ORA-00001: unique constraint (X_UK1) violated
ORA-00001: unique constraint (X_UK2) violated

Search for ORA and count the time they occur.

$ grep ORA l.log | sed 's/.*ORA/ORA/g' | sort | uniq -c
   2 ORA-00001: unique constraint (X_UK1) violated
   1 ORA-00001: unique constraint (X_UK2) violated
   1 ORA-00060: deadlock detected (X_UK3) violated

Search with sed between two dates.

$ sed -n '/^2012-09-16 19:31/p; /^2012-09-16 19:32/q' l.log 
2012-09-16 19:31:39,319 ERROR [-60] JDBCException ORA-00060: deadlock detected (X_UK3) violated
2012-09-16 19:31:39,998 ERROR [-61] JDBCException ORA-00001: unique constraint (X_UK1) violated

String manipulation with sed

First example

$ echo "Hello world" | sed 's/o/i/g'
Helli wirld

Example with other seperator

$ echo "Hello world"  |sed "s@o@i@g"
Helli wirld

Example with slash forward

$ echo "Hello/ world"  |sed "s@/@.@g"
Hello. world

Example with slash backwards

$ echo "Hello\ world"  |sed "s@\@.@g"
sed: 1: "s@\@.@g": unterminated substitute in regular expression
$ echo "Hello\ world"  |sed "s@\\@.@g"
sed: 1: "s@\@.@g": unterminated substitute in regular expression
$ echo "Hello\ world"  |sed "s@m@.@g"
Hello\ world
$ echo "Hello\ world"  |sed "s@\\\@.@g"
Hello. world

Bash oneline command loops

For loop for osx:

For loop form 1 down to 4.

$ for xx in {1..4}; do echo "Hello world $xx"; done
Hello world 1
Hello world 2
Hello world 3
Hello world 4

For loop form 101 down to 95.

$ for xx in {101..95}; do echo "Hello world $xx"; done
Hello world 101
Hello world 100
Hello world 99
Hello world 98
Hello world 97
Hello world 96
Hello world 95

For with steps

$ for((i=1;i<=10;i+=2)); do echo "Welcome $i times"; done
Welcome 1 times
Welcome 3 times
Welcome 5 times
Welcome 7 times
Welcome 9 times

Work with files

$ for i in *; do echo $i; done
Applications
Desktop
Documents
Downloads

with filter

$ for i in ./*.xls; do echo $i; done
./2012.02.sysgarden.xls
./2012.03.sysgarden.xls
./2012.04.sysgarden.xls

$ DO
DONE