maandag 21 april 2014

Reading CSV files in bash script

idrolestartstophostvmnr
1role01nonovmhost2011
2role02nonovmhost2012
3role03yesnovmhost2013
4role04yesnovmhost2014
5role05yesnovmhost1511
6role06yesnovmhost1512
7role07yesnovmhost1513
8role08yesnovmhost1514
9role09yesnovmhost921
10role10yesnovmhost932
11role11noyesvmhost943
12role12noyesvmhost954
# 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
}