SET DEFINE OFF

In the last post I mentioned about shell script for converting excel to insert statements. I came across one more problem with that task. The values in insert statement had "&" (ampersand) and the sqlplus was prompting to enter a value. There are slick ways to encode ampersand using sed, but the simplest one is to turn that behavior off in sqlplus. The following command will do the trick.

SET DEFINE OFF.

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'))