Korn Shell Scripting
1) Parsing strings, i.e to
pick out "2003" from "2003/12/25",
NDATE="2003/12/25"
year=${ NDATE%%/* }
If we have a variable defined with certain fixed separators, we can pluck
blocks of text off them like so: GAME_DETAIL=12440_INSTOCK_ARCADE_TETRIS
echo ${GAME_DETAIL#*_}
echo ${GAME_DETAIL##*_}
echo ${GAME_DETAIL%_*}
echo ${GAME_DETAIL%%_*}
This will give:
INSTOCK_ARCADE_TETRIS
TETRIS
12440_INSTOCK_ARCADE
12400
2) Finding text in a file
Simply use 'grep' to find the text pattern in a file
grep pattern
filename
To search all files underneath a directory:
find /opt/archives -print | xargs grep '[Nn]eedle'
This searches for the occurrence of "needle" or "Needle" in all files in /opt/haystack and all its subdirectories.
2) Formatting time and date using 'date':
time1='date
+%H:%M:%S'
echo $time1
This will print out something like
For
time1='date
+%d/%m/%y'
echo $time1
This will print out '14/02/03'.
3) Processing input parameters
Shell scripts can be called with a number of parameters.
case $# in
0) print "No
parameter specified"
;;
1) NDATE=$1
;;
2) print
"Error, only one input parameter expected!"
;;
esac
print "Your
input: $NDATE \n"
This simple script expects only 1 input parameter, $# represents the number of
input parameters the script was called with.
4) Using sed to process files
To delete blank lines from a file,
cat
unprocessed.csv | sed -e '/^$/d' > processed.csv
To delete large white spaces in a file,
cat
unprocessed.csv | sed -e 's/[ ]*//g' > processed.csv
To perform two consecutive operations in a sed command, separate them with a
semi-colon:
cat
unprocessed.csv | sed -e 's/[ ]*//g; /^-/d' > processed.csv
5) Using sed to change the value of shell
variables
VAL_DATE='"2003-06-01"'
We want to remove the double-quotes and dashes from the date to end up with
merely 20030601. Here, we can use backticks:
VAL_DATE=`echo $VAL_DATE | sed -e 's/"//g;
s/-//g' `
So now we will get VAL_DATE = 20030601
6) Using cut to extract blocks of text in a
variable
This script converts British date format to American date format when called
with a British formatted date as an input parameter, like
"2004/03/01" i.e the 1st of March 2004.
#!/bin/ksh
year=$(echo $1 |
cut -d/ -f1)
month=$(echo $1
| cut -d/ -f2)
day=$(echo $1 |
cut -d/ -f3)
print
"${month}/${day}/${year}"
This will print out "
7) Sending email from a script
To mail users from a shell:
echo "Hello, how are you?" | mailx -s "Greeting"
janedoe@doughnut.com
To include attachments in the mail:
(uuencode /out/reports/ImportantDates.csv
ImportantDates.csv) | mailx -s "Important Dates" janedoe@doughnut.com
The recipient Jane Doe will receive an email with the subject "Important Dates" and will be able to open the csv file using Excel.
To mail a block of text, it is possible to also embed commands in the text, like including an external log file:
mailx
–s “Error Log Warning” operations@thefirm.com
<< EOF
This
is an automatically generated warning email. An error has occurred,
so please take a look at the error log below.
`cat /log/today/batchrun.log`
EOF
8) Printing coloured text
To print coloured text on the screen, refer to the ASCII codes for the complete colour codes. Use the Emacs text editor in order to type out these control characters.
This will clear the screen and print the greeting text:
print "\033[1;1H\033[JHello world!"
This will print the menu title in cyan with a blue background, and the menu
items in cyan (against the default background).
print "^[[1;36;44mDINNER
MENU^[[0m\n"
print "^[[1;36m(1) Bacon and eggs\n
print "(2) Croissant and orange juice\n
print "(3) Toast and coffee^[[0m\n"
9) Extracting text from files
This script extract bits of text from files in a shell script.
Say we have a file called top_books.csv that contains the following text:
favourite book=A Farewell To Arms
To extract the book name in a shell script, we can do something like this:
read
-r favebook < top_books.csvSo when we run the script, we will get the following output:
The top best seller is A Farewell To Arms
10) Concatenating files underneath a set of sub-directories
Say we have a series of sub directories underneath a directory /lists_{date}
where date is the last date that a list generator script was run. So the
directories (and files) are:
/lists_20051031/fruits/inventory_fruits.csv
/lists_20051031/meat/inventory_meat.csv
/lists_20051031/veg/inventory_veg.csv
We want to concatenate all three csv files into one big file called total_list.csv
Contents of inventory_fruits.csv:
Bananas
Kiwi fruit
Contents of inventory_meat.csv:
Chicken
drumsticks
Leg of lamb
Contents of inventory_veg.csv:
Cucumber
Chard
Spinach
First our script will need to pick out the latest lists_{date} directory to
inspect before recursively visiting each sub directory to compile our total_list.csv.
datetag=$(date +"%Y%m")
cd /
pwd | grep lists
if [$? -eq 0]; then
dirName=lists_${datetag}*
fi
$listdir=$(
ls -d $dirName | tail -1)
if [ -n $listdir]; then
echo "Checking $listdir..."
find $listdir -type f -name "inventory*.csv" -exec cat {}
>> /total_list.csv
fi
This will append all the contents of each file into total_list.csv, giving a
result of:
Kiwi fruit
Brussels sprouts
Cucumber
Chard
Spinach
Chicken drumsticks
Leg of lamb
11.
Reading a file line by line
This is a simple ksh script to read a file line-by-line.
Say we have a file pipe.txt that contains the following
text:
This is not
a pipe
silly man!
#!/bin/ksh
while read lineOfFile; do
echo “$lineOfFile”
done < pipe.txt
The output will be exactly the contents of the file.
12.
Printing all possible values of a particular column of a CSV file
Say we have a very large csv file called Models.csv containing numerous
lines of data such as “0,14343,”mauve”,”14/06/04”,”365 Acacia Road”,
“112-343-1456” and we need to list all the possible instances of column 3, i.e
in this case, the colour code. This is a comma separated file.
awk –F, ‘{ print $3 }’ Models.csv | sort –u
The –F flag specifies the separator, in this case a comma. ‘3’ specifies the column number we are interested in. Piping the results through a “sort” command just sorts the results alphabetically.
12.
Finding and deleting files older than X days
We often want to recurse through directories and get rid of old files, say
log files older than 14 days. In a shell script, we can write these commands in
succession so that it performs these different operations in succession.
`find /home/users/chgreen –type f –mtime +14 –exec
rm –r {} \’;
`find /home/users/fblackett –type f –mtime +14 –exec rm –rf {} \’;
If we want to match a directory pattern, i.e the directories are:
/home/users/chgreen/2006_03_01
/home/users/chgreen/2006_03_02
/home/users/chgreen/2006_03_03
We can:
`find /home/users/chgreen/????_??_?? –mtime +2 –exec
rm –r {} \’
If we want to list all CSV files in a certain directory structure:
`find /home/users/chgreen/data –type f –name “*.csv”
–exec ls –l {} \’
13. Using
xargs
If we want to find all the files of a certain pattern i.e its name is something
like “GuildTreasury*.log”, and they are all located under a complicated
directory structure under a base directory of /guild/finances, and we want to
zip them all up into a single zip file, we can use the xargs command to pass
the results of one query to another:
find /guild/finances/ -name “GuildTreasury*.log” | xargs zip -9 GuildTreasuryFiles.zip
14. Checking for existence of a file
If we have a file that we know will be named “post” and appended with the current date, say something like “post150306.csv” then we can easily search for it like so:
postFile=`ls
/home/post/post*.csv`
if
[ ! –s $postFile ]; then
echo “Post file does not exist today”
else echo “Post file is here : $postFile”
fi
15. Turning off sound
If the beeps on the computer are getting annoying, just:
xset
b 0
or
xset
b off
This will turn beeps off (tested on a Solaris box).
16. Extracting script name
If we have a program called barney.ksh and we want to generate a log file called barney.log automatically, we can just extract the program name in the script and then utilise it like so:
BASENAME=`basename
$0 .ksh`
echo $BASENAME
LOGFILE=$BASENAME.log
This will print “barney” on the screen and the logfile will be automatically set to barney.log.
17. Await user input
If we want to wait for a user to choose either to Exit the script or to Continue to the next stage, we can use a while loop as shown below. We assume that C is the default answer, if the user hits return, then we assume he/she wants to continue.
while
:
do
echo “\nDo you wish to Exit or Continue (E/C)? >\c”
read ans
if
[ -z “$ans” ]; then
ans=C
fi
case
$ans in
c|C) echo “Fine you want to continue…” ;;
e|E) echo “Exiting the script! Bye!”
exit 0 ;;
*) echo “That is not a valid choice! Try again.”
continue ;;
esac
break
done
Visual Basic
1) Expression Editor (MS Access Reports)
To display negative numbers in this style, i.e (500,000.25) instead of
-500,000.25, use the following expression in the Expression Editor.
Assuming the number is from the column "modifiednpv" in an Access
table.
=iff([modifiednpv]
< 0, format(abs([modifiednpv]), "(#,##0.00)"), [modifiednpv] )
SQL
1) Maximums and minimums
If there are chunks of data, and we want process them chunk by chunk, like so,
in the table BookTbl:
|
BookID |
Name |
Retailer |
Price |
|
00001 |
The Catcher In The Rye |
Amazon.com |
£4.65 |
|
00001 |
The Catcher In The Rye |
Foyles |
£4.99 |
|
00005 |
To Kill A Mockingbird |
Waterstones |
£4.99 |
|
00005 |
To Kill A Mockingbird |
ABC Books |
£4.50 |
|
00005 |
To Kill A Mockingbird |
Borders |
£6.00 |
If we want to select an additional column that will list the cheapest price for
each book listed:
select BookID,
Name, Retailer, min(Price)
from BookTbl
group by BookID
This will give the results:
00001 The Catcher
In The Rye Amazon.com £4.65
00005 To Kill A
Mockingbird ABC Books £4.50
2) Using Case
While displaying some data from a table, we may want to alter its output
depending on the value of a column. Here, the case statement comes in handy:
select candy_id,
case candy_name
when "Snickers" then "Sweet chocolate
bar"
else "Chocolate bar"
end 'candy_name',
candy_retailer
from candy_table
Here, we only want an output of "Sweet chocolate bar" when it
is a Snickers bar, and otherwise it displays "Chocolate bar".
3) Displaying stored procedures in a database
select * from sysobjects where type = "P"
go
This will give a list of all stored procedures in a database
List the stored procedure this way:
sp_helptext <stored_proc_name>
go
For listing views, replace the P with V.
4) Checking free space in a database
To check for the remaining MB of free space available in the data section of a
database:
select
sum(curunreservedpgs(u.dbid,u.lstart,0)/512This will display the size in MB.
Perl
1) Text processing an output file.
For a raw output file that is a large SQL query, like this:
BookID
BookName Author Inventory Price
------
-------- ------ ---------------
'D01014','To The
Lighthouse', 'Virginia Woolf','In Stock','6.99'
'D56058 ','The
Bell Jar' 'Sylvia Plath' 'In Stock','9.99'
'RY788','Possession',
'AS Byatt','Out of Stock','9.99'
And we want to process it so it'll load nicely into a spreadsheet as a csv:
#!usr/local/bin/perl
-w
$index=0
while ($line =
<> )
{
chomp($line);
$index+=1;
if($index=2) { $line=""; } ##
erase the ------ line
if($index=1) {
$line=~ s/^\s+//; ##
remove leading spaces
$line=~ s/\s+/ /g ## substitute many spaces with one space
@fields = split(/ /, $line); ##
split the first line by spaces into a "fields" array (headers)
$line="";
for $i (0 .. $#fields) {
$i =~ s/^\s+//;
## remove all leading spaces
$i = ~s/\s+$//;
## remove all trailing spaces
$line .=
"$fields[$i], ";
}
$line =~ s/, //g;
## remove last comma in header line
}
## end if
processing headers
elsif($index=2) { next; } ## skip second line, we've removed the --- earlier!
else
{
## now for the rest of the file
$line =~ s/\s*,\s*/,/g; ## remove spaces between commas
$line =~ s/^\s//;
## remove leading spaces
$line =~ s/\s+$//;
## remove trailing spaces
$line =~ s/,$//;
## remove the last comma from every line
$line =~ s/\s*'\s/"/g; ## replace ' with "
$line =~ s/\s*','\s*/","/g; ## replace all ',' with ","
}
print
"$line\n";
## print out formatted line
}
We'll just pipe the raw file through this format_file.pl file like so:
cat raw_output.txt
| ./format_file.pl > processed_output.csv
2) Finding today's date, what day it is, etc.
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)
= localtime(time);
# using hashes
%weekdays = (
"0" => "Sunday",
"1" => "Monday,
"2" => "Tuesday",
"3" => "Wednesday",
"4" => "Thursday",
"5" => "Friday",
"6" => "Saturday"
);
print "Today
is $weekdays{$wday}\n";
# using arrays
@weekday_array =
qw(Sunday Monday Tuesday Wednesday Thursday Friday Saturday);
print "Today
is $weekday_array[$wday]\n";
3) Making a front end in Perl/Tk
This Tk front end was made to kick off a process which takes a parameter
"book name". It takes a list from a config file bookList.txt in your
home directory and populates the list box with the contents of the file.
#!/opt/perl5/bin/perl5.00553
use Tk;
my(@backSlateGrey) = (-background => "slategray3");
my(@backDarkGrey) = (-background => "gray33");
my(@backLightYellow) = (-background => "light yellow");
my(@backLightBlue) = (-background => "lightsteelblue2");
my(@foreWhite) = (-foreground => "white");
my(@selectforeRed) = (-selectforeground => "red");
my(@ridgeRelief) = (-relief => "ridge");
my(@listBoxParams) = (-selectmode =>
"single",@ridgeRelief,-width=>28,@backDarkGrey,@foreWhite,@selectforeRed);
my(@textParams) = (-width=>28, -height=>1);
$bookFile = "$ENV{HOME}/bookList.txt";
open BOOKFILE, $bookFile or die "Cannot find bookList.txt in home
directory! Exiting...\n";
$i=0;
while(<BOOKFILE>) {
chomp;
$bookList[$i]=$_;
$i++;
}
close BOOKFILE;
my $mw = MainWindow->new;
$mw->title("Swap Risk/PDH Report Generator");
$mw->configure(@backSlateGrey);
$mw->geometry("+250+150");
$mw->Label(-text=>"Select a book", @backSlateGrey, -pady=>5,
-padx=>88)->pack();
$bookLb = $mw->Listbox(@listBoxParams)->pack();
$bookLb->insert('end',@bookList);
$bookLb->bind('<Button-1>', sub {
@listItem=$bookLb->curselection();
$messageText->delete("1.0",'end');
$messageText->insert("end","Book $bookList[$listItem[0]] selected.");
});
$messageText=$mw->Text(@textParams,@backLightYellow,@ridgeRelief)->pack();
$messageText->tagConfigure('errorMsg', -foreground =>red);
$mw->Button(-text=>"Run",@backLightBlue,@ridgeRelief, -command
=> sub {
if(scalar @listItem eq 0) {
$messageText->delete("1.0",'end');
$messageText->insert("end","Error! No book
selected.",'errorMsg');
} else {
$messageText->delete("1.0",'end');
$messageText->insert("end","Running for book
$bookList[$listItem[0]].");
print "Running script for book $bookList[$listItem[0]]\n";
}
})->pack(-pady=>20,-side=>'left',-expand=>1,-fill=>'x');
$mw->Button(-text=>"Exit", @backLightBlue,@ridgeRelief, -command
=> sub { exit
})->pack(-pady=>20,-side=>'right',-expand=>1,-fill=>'x');
MainLoop;
© Copyright 2002, 2003 JR
Ong/ArtemisWorks