My first SED-AWK script

I have heard a lot about SED-AWK scripts but never had a chance to work on it. Recently I was assigned a mundane task of importing excel sheet to Oracle database table. Of course there are plenty of tools available in market but my case is little complex as the data I had was denormalized. So I must find ID value for some column and also some formatting/calculation was required for other columns. There are many ways to solve this problem but this time I took an approach of solving it using SED-AWK.

AWK Script to create insert statements.

/a*/ {
print "insert into <TABLE> (<COLUMNLIST>) values (";
for (i=1; i< NF; ++i)
{
    if ($i=="SYSDATE")
    {
         print $i",";
    }
    else
    {
        if (i >= 2 && i <= 7)
        {
            print $i"*3600,";
        }
   else
      {
           print "'"$i"',";
   }
 }
}
print "(select item_id from ic_item_mst where item_no = "$i"))";
print "/";}

Shell script to converts CSV to Insert SQL.

#
# CSV to INSERT SQL
# param 1 TABLE NAME
# param 2 COMMA SEPERATED COLUMN LIST
# param 3 awk script file
# parma 4 CSV values
#

USAGE="Usage: 0-ScriptName 1-Table Name 2- Comma seperated column list 3- awk script 4- values csv file";

if test $# != 4
then
  echo "$0: Error: Incorrect Arguments.."
  echo $USAGE
  exit 1
fi

cp $3 temp.awk

#
# Encode spaces with @@ token
#
sed 's/ /@@/g' $4 > temp1

#
# Replace , with spaces so that awk can read it
#
sed 's/,/ /g' temp1 > tempvalues.txt

#
# Replace table name in AWK script
#
sed 's/<TABLE>/'$1'/g' temp.awk > test.awk
rm temp.awk

#
# Replace column list in AWK script
#
sed 's/<COLUMNLIST>/'$2'/g' test.awk > final.awk
rm test.awk

#
# AWK and generate the insert stmt
#
awk -f final.awk tempvalues.txt > temp.sql
rm final.awk
rm tempvalues.txt
rm temp1

#
# Decode spaces back
#
sed 's/@@/ /g' temp.sql > insertstmt.sql
rm temp.sql

In the AWK script I have written code to multiply columns 2 to 7 with 3600 and I am getting ID value for the last column.

Example:

./ci.sh xxtmg_sl_std_maintenance "RUN_SPEED,CT_6ORLESS,CT_6TO15,
CT_15ORMORE,CCT_6ORLESS,CCT_6TO15,CCT_15ORMORE,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,ORGN_CODE,RESOURCES,ITEM_ID" 
xsm.awk
final.csv

Sample insert statement from the output:

insert into xxtmg_sl_std_maintenance (RUN_SPEED,CT_6ORLESS,CT_6TO15,
CT_15ORMORE,CCT_6ORLESS,CCT_6TO15,CCT_15ORMORE,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,ORGN_CODE,RESOURCES,ITEM_ID)
values (
'800',
0.5*3600,
0.75*3600,
1*3600,
0.2*3600,
0.25*3600,
0.33*3600,
'1913',
SYSDATE,
'1913',
SYSDATE,
'-1',
'LIB',
'SLITTER',
(select item_id
from ic_item_mst
where item_no = '0315MS600 000'))